Solved

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

Posted on 2014-01-02
2
1,240 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 65

Accepted Solution

by:
Jim Horn earned 200 total points
Comment Utility
> 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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
SELECT Item_ID, TransAction_Type, MAX(Transaction_Date) as MaxDate
FROM youTable
GROUP BY ItemID, Transaction_Type
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now