Query count of unique ID's (AC2007)

[AOB]

Hi there,

I have a basic query off a currency table :

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?

Bear in mind that 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 :

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. But really struggling to adapt it for my needs as I can't get my head around all the subqueries required.

Can anybody help me out?

Thanks

AOB (SQL novice but trying to get there!)

 

[plog]

Only 1 subquery required, one that will list the unique CcyID/AccountID permuations in tblBalances:

SELECT CcyID, AccountID FROM tblBalances GROUP BY CcyID, AccountID

Use that in the query you posted instead of tblBalances.

 

[AOB]

Thanks plog,

That makes sense and have been trying to put something like that together all day but keep failing with the syntax. Do I need to alias the subquery and refer to a field within it?

Sorry to be a pain but can you help me with how to use what you've provided in my main query?

Thanks again

AOB

 

[plog]

You need to save the query I posted with a name like sub_Accounts. Then edit that second query you posted; removing tblBalances and replacing it with sub_Accounts

 

[AOB]

Thanks plob

Is it possible to do it all as one query? I'm using this as a record source for a subform, not saving them as individual queries.

(I know I could save it as a query - or rather a pair of queries - and then refer to the main one as the record source but I prefer to hard-code the queries directly into the RecordSource property of the subform as I have a lot of subforms and it's just easier for me to manage them this way rather than have a long list of queries in the navigation pane)

Along the lines of :

SELECT tblCurrencies.CcyID, tblCurrencies.Ccy, Count(tmp.AccountID) AS NoOfAccounts
FROM

(SELECT CcyID, AccountID FROM tblBalances GROUP BY CcyID, AccountID) AS tmp,
tblCurrencies INNER JOIN tblBalances ON tblCurrencies.CcyID = tblBalances.CcyID
​

GROUP BY tblCurrencies.CcyID, tblCurrencies.Ccy
ORDER BY tblCurrencies.Ccy

(But this is wrong as the numbers it spits back are way off the mark; getting tens of thousands in the returned 'NoOfAccounts' field when there are only a couple of hundred accounts in there!)

Is this feasible?

Thanks

AOB

 

[plog]

What's the love affair with tblBalances? It's only appearance should be in the subquery I posted. Remove the reference to it in the INNER JOIN. You need to INNER JOIN the subquery not tblBalances.

 

[AOB]

Haha, sorry (it's kind of the pivotal table so forgive my infatuation!)

If I do that :

SELECT tblCurrencies.CcyID, tblCurrencies.Ccy, Count(tmp.AccountID) AS NoOfAccounts
FROM

(SELECT CcyID, AccountID FROM tblBalances GROUP BY CcyID, AccountID) AS tmp,
tblCurrencies INNER JOIN tmp ON tblCurrencies.CcyID = tmp.CcyID
​

GROUP BY tblCurrencies.CcyID, tblCurrencies.Ccy
ORDER BY tblCurrencies.Ccy

I get an error :

The Microsoft Office Access database engine cannot find the input table or query 'tmp'.
Make sure it exists and that its name is spelled correctly

 

[namliam]

Without reading the full thread.... the query should look more like:

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

 

[AOB]

Bingo!!

That's exactly what I wanted, thanks namliam

Thanks to yourself and plog for your invaluable help, will file this away for the next subquery problem I run into. All part of the learning curve!

Much appreciated

AOB

 

[AOB]

Guys,

One more question, just out of curiosity, for the learning file...

What if I wanted to add another count to this query? So let's say my balances table also had, say, a BankID field, and I wanted to count the number of unique AccountID's and the number of unique BankID's linked to each currency?

Is that possible?

Thanks again

AOB

 

[namliam]

Depends on how complex you want to make it

In oracle and some other databases you can do:

Select ccyid, count(distinct AccountID) 
from tblCurrencies  
Group by ccyid

If you really want to count unique banks thru the BankID, you will need to add more (and more) subqueries, which you then join back using the same logic as above.

 

[AOB]

Thanks namliam

I don't *want* to make it more complex than it has to be, obviously

But I do want to know how to do it if I have no alternative

I guess where I'm struggling is how to do the INNER JOIN on the second subquery, am I going in the right direction here or way off the mark?

SELECT tblCurrencies.Ccy, Count(tmpAccounts.AccountID) AS NoOfAccounts, Count(tmpBanks.BankID) AS NoOfBanks
FROM

(SELECT CcyID, AccountID
FROM tblBalances
GROUP BY CcyID, AccountID) AS tmpAccounts,

(SELECT CcyID, BankID
FROM tblBalances
GROUP BY CcyID, BankID) AS tmpBanks
​

INNER JOIN tblCurrencies ON tmpAccounts.CcyID = tblCurrencies.CcyID
INNER JOIN tblCurrencies ON tmpBanks.CcyID = tblCurrencies.CcyID
GROUP BY tblCurrencies.Ccy
ORDER BY tblCurrencies.Ccy

 

[namliam]

You make an inner/outer join always per query/table, something along the lines of....

Select...
From Table1 
Join  Table2 on Table1.Key = Table2.key
Join Table3 on table1.Key = Table3.Key
Join Table4 on Table3.key = Table4.Key
Join Table5 as Namliam on table1.Key = Namliam.key

Table 1 is always you "primary" table, just to keep things a little readable and understandable.

If you are substituting in query's Instead of Table, you simply do as per above

Join (Select... from ... etc...) as Table6 on Table1.Key = Table6.Key

Any join can be (inner) join, Left (outer) join, Right (outer) join

Suppose you can read some links from here: https://www.google.nl/#q=join+sql

 

[AOB]

Thanks (again) namliam

Both for the info and the links

Still can't get it to work though :banghead:

(returning same values for NoOfAccounts as for NoOfBanks)

SELECT tblCurrencies.Ccy, Count(tmpAccounts.AccountID) AS NoOfAccounts, Count(tmpBanks.BankID) AS NoOfBanks

FROM ((tblCurrencies

INNER JOIN

(SELECT CcyID, AccountID
FROM tblBalances
GROUP BY CcyID, AccountID) AS tmpAccounts
​

ON tblCurrencies.CcyID = tmpAccounts.CcyID)
​

INNER JOIN

(SELECT CcyID, BankID
FROM tblBalances
GROUP BY CcyID, BankID) AS tmpBanks
​

ON tblCurrencies.CcyID = tmpBanks.CcyID)
​

GROUP BY tblCurrencies.Ccy

ORDER BY tblCurrencies.Ccy

 

[namliam]

That is because of the way that joins multiply eachother, you will need to subnest the queries for it to work... I wasnt thinking you would actually want the discribed result but were looking for general information....

Something along the lines of:

SELECT tblCurrencies.Ccy, Count(tmpAccounts.AccountID) AS NoOfAccounts, NoOfBanks

FROM tblCurrencies 
INNER JOIN (SELECT CcyID, AccountID , count(BankID) noofbanks
            FROM tblBalances 
            Inner Join (SELECT CcyID, BankID 
                        FROM tblBalances 
                        GROUP BY CcyID, BankID) AS tmpBanks ON tblCurrencies.CcyID = tmpBanks.CcyID
            GROUP BY CcyID, AccountID) AS tmpAccounts 

ON tblCurrencies.CcyID = tmpAccounts.CcyID)

GROUP BY tblCurrencies.Ccy

ORDER BY tblCurrencies.Ccy

You really need to go read up on joins and how they work though, undestanding this is KEY to working with databases.

 

[AOB]

Thanks (again) namliam

Agreed, some further reading is definitely required on my part. I 'get' JOINS but I do accept I need to refine my understanding of the precise mechanics of them. Part of my problem (I suppose every newbie's problem) is that the easiest way to understand them is to mess around with them in the query editor until you either get back what you expected or understand exactly why you didn't.

Apologies also, my original post did suggest it was only general information I was after but, as per the above, I'm playing around with it in the editor to try and understand it, so thanks very much for providing the full SQL command. I really do appreciate the effort.

It still doesn't work for me though. Could be wrong but I suspect the final closing bracket shouldn't be there? But even removing that, the editor gives me an error : "Syntax error in JOIN operation", but, frustratingly, doesn't show me where the error occurs?

SELECT tblCurrencies.Ccy, Count(tmpAccounts.AccountID) AS NoOfAccounts, NoOfBanks

FROM tblCurrencies

INNER JOIN

(SELECT CcyID, AccountID, Count(BankID) AS NoOfBanks

FROM tblBalances

INNER JOIN

(SELECT CcyID, BankID

FROM tblBalances

GROUP BY CcyID, BankID) AS tmpBanks

ON tblCurrencies.CcyID = tmpBanks.CcyID
​

GROUP BY CcyID, AccountID) AS tmpAccounts

ON tblCurrencies.CcyID = tmpAccounts.CcyID
​

GROUP BY tblCurrencies.Ccy

ORDER BY tblCurrencies.Ccy

 

[namliam]

The trick to this type of thing, is to take it step by step...
you know the original query works, now make it for the banks

I didnt even notice you had tblBalances and a tblCurrencies, so that is where it may be going wrong...
I.e. here:

SELECT CcyID, AccountID, Count(BankID) AS NoOfBanks

FROM tblBalances
INNER JOIN (SELECT CcyID, BankID
	    FROM tblBalances
            GROUP BY CcyID, BankID) AS tmpBanks ON [B][I][U]tblCurrencies[/U][/I][/B].CcyID = tmpBanks.CcyID

GROUP BY CcyID, AccountID

That should obviously be tblBalances or reverse have tblCurrencies in the from.

Another trick is to KISS, Keep it simple stupid/sunshine.
Just make the one query

SELECT CcyID, BankID
	    FROM tblBalances
            GROUP BY CcyID, BankID

Save it as qryUniqueBank
and just use the query as input for your next query, just like you would a table.... and save that query and build on it again, etc etc.
If later you want to look smart, replace the individual SQL's into the final query and presto a 10 page SQL build with 500 subqueries to impress the boss man

 

[AOB]

Good advice!

I've bitten the bullet and saved the two 'Count' queries :

qryBanksPerCurrency
SELECT tblCurrencies.CcyID, Count(tmp.BankID) AS NoOfBanks
FROM

(SELECT CcyID, BankID
FROM tblBalances
GROUP BY CcyID, BankID) AS tmp
​

INNER JOIN tblCurrencies ON tblCurrencies.CcyID = tmp.CcyID
GROUP BY tblCurrencies.CcyID

qryAccountsPerCurrency
SELECT tblCurrencies.CcyID, Count(tmp.AccountID) AS NoOfAccounts
FROM

(SELECT CcyID, AccountID
FROM tblBalances
GROUP BY CcyID, AccountID) AS tmp
​

INNER JOIN tblCurrencies ON tblCurrencies.CcyID = tmp.CcyID
GROUP BY tblCurrencies.CcyID

And referred to them in the main query, used as the RecordSource for the subform

SELECT tblCurrencies.Ccy, qryAccountsPerCurrency.NoOfAccounts, qryBanksPerCurrency.NoOfBanks

FROM ((tblCurrencies

INNER JOIN qryAccountsPerCurrency ON tblCurrencies.CcyID = qryAccountsPerCurrency.CcyID)

INNER JOIN qryBanksPerCurrency ON tblCurrencies.CcyID = qryBanksPerCurrency.CcyID)
​

GROUP BY tblCurrencies.Ccy, qryAccountsPerCurrency.NoOfAccounts, qryBanksPerCurrency.NoOfBanks

ORDER BY tblCurrencies.Ccy

I'd prefer to have a single query hard-coded straight into the subform (and not rely on saved queries) but I just don't have the knowledge (yet...) to make that switch.

Thanks again for all your patience namliam!

(Plog, if you're still reading, I'm so sorry, I know this was your original suggestion...!)

 

[David R]

I'd prefer to have a single query hard-coded straight into the subform (and not rely on saved queries) but I just don't have the knowledge (yet...) to make that switch.

Why? Saved queries are more efficient and generally easier to debug.

 

[namliam]

Just to show you how to merge that up.... Yes single queries are slower than stored queries, but the general difference caused by that is measured in 2 decimals behind the comma in seconds... not only are they easier to debug, also generaly much more easy to build

SELECT tblCurrencies.Ccy, qryAccountsPerCurrency.NoOfAccounts, qryBanksPerCurrency.NoOfBanks

FROM ((tblCurrencies
INNER JOIN [COLOR="DarkOrange"]( SELECT tblCurrencies.CcyID, Count(tmp.AccountID) AS NoOfAccounts
             FROM
                 (SELECT CcyID, AccountID
                  FROM tblBalances
                  GROUP BY CcyID, AccountID) AS tmp
             INNER JOIN tblCurrencies ON tblCurrencies.CcyID = tmp.CcyID
             GROUP BY tblCurrencies.CcyID ) as[/COLOR] qryAccountsPerCurrency ON tblCurrencies.CcyID = qryAccountsPerCurrency.CcyID)

INNER JOIN [COLOR="Magenta"]( SELECT tblCurrencies.CcyID, Count(tmp.BankID) AS NoOfBanks
             FROM
                 (SELECT CcyID, BankID
                  FROM tblBalances
                  GROUP BY CcyID, BankID) AS tmp 
             INNER JOIN tblCurrencies ON tblCurrencies.CcyID = tmp.CcyID
             GROUP BY tblCurrencies.CcyID ) as [/COLOR]qryBanksPerCurrency ON tblCurrencies.CcyID = qryBanksPerCurrency.CcyID)
GROUP BY tblCurrencies.Ccy, qryAccountsPerCurrency.NoOfAccounts, qryBanksPerCurrency.NoOfBanks

ORDER BY tblCurrencies.Ccy

Good for you for having a naming convention and using the code tags