Solved Query to Return Most Recent Date (1 Viewer)

woodsy74

Registered User.
Local time
Today, 11:01
Joined
Jul 25, 2012
Messages
26
Hello All, I seem to be stuck on this query. A Customer can have multiple AccountTypes which can have multiple entries by Date. I am trying to query the most recent Date for each AccountType per Customer.

So, looking at the below sample I would want to return rows 3,4,&5 (row 1 is the field names) for Customer ABC and rows 6&8 for Customer DEF. These rows contain the most recent Date per AccountType.

Any thoughts on how I can accomplish this? Thanks.

CustomerAccountTypeDateAmount
ABC1114/2/2021210
ABC1115/15/202170
ABC33311/1/20211500
ABC5559/15/2021150
DEF22211/1/202125
DEF3339/15/2021420
DEF33310/20/2021390
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:01
Joined
Sep 21, 2011
Messages
9,589
Look at the Max() function

Why not 6 and 8 ?
 

plog

Banishment Pending
Local time
Today, 10:01
Joined
May 11, 2011
Messages
10,714
I am trying to query the most recent Date for each AccountType per Customer.

So, looking at the below sample I would want to return rows

Those are 2 different things. Your first sentence wants a Date, the second wants an entire row. I'll show you how to get the former and tell you how to use it to get the latter.

Build a totals query and get the MAX [Date] for each Customer/AccountType combination. This is that query:

Code:
SELECT Customer, AccountType, MAX([Date]) AS MostRecent
FROM YourTableNameHere
GROUP BY Customer, AccountType

Then, if you want the entire row, you save the above query and use it in another query along with YourTableNameHere. You JOIN them via Customer-Customer, AccountType-AccountType and [Date]-MostRecent. Bring in all the fields from YourTableNameHere and you have your results.
 

woodsy74

Registered User.
Local time
Today, 11:01
Joined
Jul 25, 2012
Messages
26
That looks like a winner. Thank you very much! Appreciate it.
 

plog

Banishment Pending
Local time
Today, 10:01
Joined
May 11, 2011
Messages
10,714
Everything is 'code'. Open a new query in your database, switch to SQL view, paste in what I posted , run it to verify it works, then you can go into design view which is what you are probably used to
 

Users who are viewing this thread

Top Bottom