Avatar of Mark Drelinger
Mark Drelinger
Flag for United States of America asked on

Access query, selecting only max record

I have a table with three columns (ID, ProductIDNo, DateReceived).
 ProductIDNo field has duplicates with different DateReceived values.
 I want to filter it to include all records only for Max DateReceived for that record.
 Is this possible?

I posted a similar, but opposite, question yesterday (thank you for the help, Rey)
Below selects all of the records that are less than the Max DateReceived :
SELECT T.*
 FROM CPEQUP T
 Left Join
 (SELECT CPEQUP.ProductIDNo, Max(CPEQUP.DateReceived) AS MaxOfDateReceived
 FROM CPEQUP
 GROUP BY CPEQUP.ProductIDNo) As T1
 ON T.DateReceived=T1.MaxOfDateReceived
 Where T1.MaxOfDateReceived Is Null
Microsoft Access

Avatar of undefined
Last Comment
Mark Drelinger

8/22/2022 - Mon
Rey Obrero (Capricorn1)

try if this will give the result you want

SELECT CPEQUP.ProductIDNo, Max(CPEQUP.DateReceived) AS MaxOfDateReceived
 FROM CPEQUP
 GROUP BY CPEQUP.ProductIDNo
Mark Drelinger

ASKER
Thank you, Rey.
If I want to include other columns that are in that table in the result (for example CustomerNo, Make), how could I include them ?
When I try to just add them to the select, I get an error that they are not part of the aggregate function?
Rey Obrero (Capricorn1)

try this

SELECT T.*
 FROM CPEQUP T
Inner Join
 (SELECT CPEQUP.ProductIDNo, Max(CPEQUP.DateReceived) AS MaxOfDateReceived
 FROM CPEQUP
 GROUP BY CPEQUP.ProductIDNo) As T1
 ON T.DateReceived=T1.MaxOfDateReceived And T.ProductIDNo=T1.ProductIDNo
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Mark Drelinger

ASKER
Is there any way to include the records where either ProductIDNo or the DateReceived is null. This version excludes the records where either of those fields are null.
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mark Drelinger

ASKER
very astute question :)
We've merged two systems, and the old system had customer inventory loaded that we didn't actually sell to the customer. So, there was no delivery date.  But the data still has value.
Mark Drelinger

ASKER
Thanks for all the help.
Mark
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.