Access query, selecting only max record

Mark Drelinger
Mark Drelinger used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
try if this will give the result you want

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

Author

Commented:
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?
Top Expert 2016

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.
Top Expert 2016
Commented:
how can you have a DateReceived data for null ProductID?


try this


SELECT T.*
FROM CPEQUP AS T INNER JOIN (SELECT CPEQUP.ProductIDNo, Max(CPEQUP.DateReceived) AS MaxOfDateReceived
  FROM CPEQUP
  GROUP BY CPEQUP.ProductIDNo)  AS T1 ON (T.ProductIDNo = T1.ProductIDNo) AND (T.DateReceived = T1.MaxOfDateReceived)

UNION ALL

SELECT CPEQUP.*
FROM CPEQUP
WHERE (CPEQUP.ProductIDNo) Is Null OR (CPEQUP.DateReceived) Is Null

Author

Commented:
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.

Author

Commented:
Thanks for all the help.
Mark

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial