Hey all,
Please help! I’m new to Access and Sql. I have a table that looks something like the one below. I want the resulting query to only show the records with the most recent date for each item. If I simply use max() on the ModifiedDate field it returns all records with the most resent date for each item, but doesn’t remove the other records and i still end up with 9 records.
UniqueID ITEM PRICE ModifiedDate
232323 item-1 $1.23 1/1/2017
111344 item-1 $3.12 12/11/2016
11111567 item-1 $2.17 12/13/2016
232300 item-2 $7.23 1/12/2017
119989 item-2 $8.43 11/1/2016
12202211 item-2 $6.23 12/13/2016
232212 item-3 $14.22 2/13/2017
122002 item-3 $13.14 10/11/2016
11887556 item-3 $12.12 12/23/2016
Looking for the result to be this (just 3 records):
UniqueID ITEM PRICE ModifiedDate
232323 item-1 $1.23 1/1/2017
232300 item-2 $7.23 1/12/2017
232212 item-3 $14.22 2/13/2017
Code:
SELECT TempTable.UniqueID, TempTable.ITEM, TempTable.PRICE, Max(TempTable.ModifiedDate) AS MaxOfModifiedDate
FROM TempTable
GROUP BY TempTable.UniqueID, TempTable.ITEM, TempTable.PRICE;
Ideas?
Thanks in advance