Counting customers in different ways - do I need SubQuery?

Question

Thanks for taking the time to read my question.

Imagine a situation when a customer is given a free gift if they open an account. Some clients open an account to receive a free gift, but never add money to their account. Other clients open accounts, receive a free gift, and also fund their accounts by adding money to it.

I need to compare funded accounts with the total number of all clients.

Things at first looked pretty easy ...

SELECT Audits.AuditDate, Count(Audits.NickName) AS AllAccounts FROM Audits Group By Audits.AuditDate 

This obviously gives me an account of all accounts on a daily basis.

To get a "Financed Account", I ...

  SELECT Audits.AuditDate, Count(Audits.NickName) AS Funded FROM Audits WHERE Audits.CurrGBP > 0 GROUP BY Audits.AuditDate; 

This time I get a "Funded Bills" account.

Now this is where I am stuck ... I want both values ​​to match the same query, so my results will be like this ...

 AuditDate (DD/MM/YYYY) AllAccounts Funded 01/01/2012 50 45 02/01/2012 60 50 03/01/2012 70 55 

Something tells me that I need to use Sub Query, but after searching for multiple pages. Sub Queries puzzles me.

May I ask you for help? Can you show me how to write Sub Query to give me the results I need.

Regards, John.

PS - My Audits table has the following fields: Audit_ID, Audit_Date, NickName, CurrGBP, and I use MS Access 2010.

Answer 1

I can’t remember if access supports case; or if IIF is the way ... but something like ...

The subquery is not really needed, you can get the results in one query, just limit what you think when using case or IIF.

 Select A.AuditDate, count (A.NickName) as AllAccounts, sum(CASE when A.CurrGBP > 0 then 1 else 0 end) as Funded FROM Audits A GROUP BY A.AuditDate 

if IIF

 Select A.AuditDate, count (A.NickName) as AllAccounts, sum(IIF(A.CurrGBP >0,1,0)) as Funded FROM Audits A GROUP BY A.AuditDate 

EDIT, some commas in selects were missing.

Answer 2

try the following:

 SELECT AuditA.AuditDate, Count(AuditA.AllAcounts) as AllAccount, iTable.iCount FROM Audits as AuditA INNER JOIN (SELECT AuditB.Audit_ID, COUNT(AuditB.Audit_ID) as iCount FROM Audit as AuditB WHERE AuditB.CurrGBP > 0) as iTable ON AuditA.Audit_ID = iTable.Audit_ID