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
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.
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.
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.
SELECT CPEQUP.ProductIDNo, Max(CPEQUP.DateReceived) AS MaxOfDateReceived
FROM CPEQUP
GROUP BY CPEQUP.ProductIDNo