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.MaxOfDat
Where T1.MaxOfDateReceived Is Null
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.MaxOfDat
Where T1.MaxOfDateReceived Is Null
Microsoft Access
Last Comment
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?
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?
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.MaxOfDat
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.MaxOfDat
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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
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
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 questionASKER
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.
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.
ASKER
Thanks for all the help.
Mark
Mark
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SELECT CPEQUP.ProductIDNo, Max(CPEQUP.DateReceived) AS MaxOfDateReceived
FROM CPEQUP
GROUP BY CPEQUP.ProductIDNo