Query count of unique ID's (AC2007)

[AOB]

Thanks namliam


I couldn't survive without a naming convention - I learned the hard way many years ago! But thanks for noticing. I'm still finding my way in Access and SQL so it's rare for me to get a thumbs up on any of my coding!


Thanks a million for taking the time guys


AOB

 

[AOB]

Hey namliam / plog / everybody

Sorry to resurrect an old thread but I have a follow-up question from the previous solution...

Taking the qryBanksPerCurrency example :

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

I've noticed a deficiency : this query doesn't return any currencies for which there are no banks associated (i.e. it doesn't return '0's for NoOfBanks)

This wasn't an issue before as it wasn't possible to have a currency without it being associated with at least one bank (or, for the qryAccountsPerCurrency example, a currency without it being associated with at least one account)

However, I'm trying to use the same logic to do a similar query for counts which could potentially return zeroes (and this time I need to see the zeroes)

I've tried using Nz but it has no effect (I'm still missing the currencies which have no banks associated with them in the returned data)

SELECT tblCurrencies.CcyID, Nz(Count(tmp.BankID),0) 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

Any suggestions as to how to tweak the query to get these zero counts back along with everything else?

Many thanks!

AOB

 

[plog]

It's a big tweak. You need to change your FROM clause to tblCurrencies, change your INNER JOIN to a LEFT JOIN and that is where your sub query needs to go.

 

[AOB]

Thanks plog

That does stretch the definition of 'tweak' a bit alright

I think, in the long run, a restructure of the table relationships will allow me to use a simple DCount calculation as the control source of the textbox rather than convoluted querying. Probably more work but it's the only way I'll learn!

I'll experiment...

Thanks for the response!

AOB