• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1563
  • Last Modified:

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
0
filtrationproducts
Asked:
filtrationproducts
1 Solution
 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now