Create query to show only most recent date for part numbers/transaction types

I have a table with 3 fields;

ITEM_ID, TRANSACTION_DATE, TRANSACTION_TYPE

The table has about a half a million records and I want to filter it out to show me only the most recent date (1 record) for each TRANSACTION_TYPE (There is 4 different transaction types) for each ITEM_ID.

Is there a way to do this easily in an Access Query?

Thanks in advance!
Dan
LVL 1
filtrationproductsAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> show me only the most recent date (1 record) for each TRANSACTION_TYPE  for each ITEM_ID.
Create a new query, then go into SQL View, then copy-paste the below SQL into that SQL view, then rename YOUR_TABLE.
SELECT ITEM_ID, TRANSACTION_TYPE, Max(TRANSACTION_DATE) as most_recent_date
FROM YOUR_TABLE
GROUP BY ITEM_ID, TRANSACTION_TYPE
ORDER BY ITEM_ID, TRANSACTION_TYPE

Open in new window

0
 
Dale FyeCommented:
SELECT Item_ID, TransAction_Type, MAX(Transaction_Date) as MaxDate
FROM youTable
GROUP BY ItemID, Transaction_Type
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.