Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-01-02
2
Medium Priority
?
1,529 Views
Last Modified: 2014-01-02
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
Comment
Question by:filtrationproducts
2 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 800 total points
ID: 39751303
> 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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39751353
SELECT Item_ID, TransAction_Type, MAX(Transaction_Date) as MaxDate
FROM youTable
GROUP BY ItemID, Transaction_Type
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question