Queries :: Access 2007 / Query Count Of Unique IDs

I have a basic query off a currency table :

Quote:

SELECT tblCurrencies.CcyID, tblCurrencies.Ccy
FROM tblCurrencies
ORDER BY tblCurrencies.Ccy;

Now - I have a separate table of balances, which is linked to the currency table by the same CcyID, and which also has identifiers to link it to other tables (e.g. AccountID)

As part of the above query, I want to return the count of unique AccountID's in the balance table for each currency. So in other words, I want to know, for each currency, how many unique accounts exist?

Each AccountID could appear one or more times in the balance table (one-to-many relationship), so I only want to count the number of unique ID's.

So I started with the following :

Quote:

SELECT tblCurrencies.CcyID, tblCurrencies.Ccy, Count(tblBalances.AccountID) AS NoOfAccounts
FROM tblCurrencies INNER JOIN tblBalances ON tblCurrencies.CcyID = tblBalances.CcyID
GROUP BY tblCurrencies.CcyID, tblCurrencies.Ccy
ORDER BY tblCurrencies.Ccy;

But this just gives the number of AccountID's per currency (regardless of duplication within them)

I found this article which informs me that a Count(Distinct) query won't work in Access and to use subqueries instead.