Access Count IF

[SDowse]

Hi,

I have a table that has an "Added" field (which is a date), and "Account Number" field. I want to create a query that shows the last date in the "Added" field and counts the "Account Number" only where they were added on the last "Added" date.

I hope that makes sense, I have tried so much and can't get my head around it. Currently the query shows the last date but counts up all the account numbers.

 

[Joe4]

Are you trying to get a count by Account Number (so evey account may have a different last added by date)?
Or just a single count of all the accounts on the last added date?

It may be helpful to post a small example and your expected results.

 

[SDowse]

Hi,

Thanks for the reply. Every account number will have an added date, and the added date won't change...but there will be a varying number of accounts added on each date.

An attempt at sample data:

Date Added Account Number
10/06/2018 Acc1
10/06/2018 Acc2
10/06/2018 Acc3
10/06/2018 Acc4
10/06/2018 Acc5
11/06/2018 Acc6
11/06/2018 Acc7
11/06/2018 Acc8
11/06/2018 Acc9
11/06/2018 Acc10
12/06/2018 Acc11
12/06/2018 Acc12
12/06/2018 Acc13
12/06/2018 Acc14
12/06/2018 Acc15
12/06/2018 Acc16
12/06/2018 Acc17


I want to show:
Last date = 12th June 2018
Count of added = 7 accounts

Then, when I add the data for the 13th June, the information will automatically update to show 13th June 2018 as the last date and count the number of accounts added on that day.

HTH?

 

[Joe4]

You could use this query code (I assumed table name of "Table1" in this example; change to suit your table name):

SELECT Table1.[Date Added], Count(Table1.[Account Number]) AS [CountOfAccount Number]
FROM Table1
GROUP BY Table1.[Date Added]
HAVING Table1.[Date Added] = 
(SELECT Max(Table1.[Date Added]) AS [MaxOfDate Added]
FROM Table1);

 

[SDowse]

Awesome! Peeeerfect...works a treat!