Link to home
Start Free TrialLog in
Avatar of Mark Drelinger
Mark DrelingerFlag 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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try if this will give the result you want

SELECT CPEQUP.ProductIDNo, Max(CPEQUP.DateReceived) AS MaxOfDateReceived
 FROM CPEQUP
 GROUP BY CPEQUP.ProductIDNo
Avatar of 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?
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
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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Thanks for all the help.
Mark