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!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try if this will give the result you want
SELECT CPEQUP.ProductIDNo, Max(CPEQUP.DateReceived) AS MaxOfDateReceived
FROM CPEQUP
GROUP BY CPEQUP.ProductIDNo
SELECT CPEQUP.ProductIDNo, Max(CPEQUP.DateReceived) AS MaxOfDateReceived
FROM CPEQUP
GROUP BY CPEQUP.ProductIDNo
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
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.
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial