Need to set criteria based on latest date for group.

[rbertram]

I am working with a table that records historical transaction data for an ID. The table uses an auto number as the PK. A record is created every time a new action takes place for a particular ID.

I need to create a query to report all records or last record <= to a certain date, but not if there are any transactions after the date. If I create a query that limits the selection just to <= a date, it will output all entries eliminating the future entries.

The table layout is something like this:

Autonumber, ID, Action, Action Date

Sample data:

1, ID1, Action1, 05/12/2004
2, ID2, Action4, 03/28/2004
3, ID1, Action2, 04/18/2004
4, ID2, Action5, 06/30/2004

So if I wanted data based on a date of <= 05/20/2004, it would only return record #1 (or record #1 and #3) for ID1. It would not return any records for ID2, since there was a transaction with a date past the requested date.

Is this possible? Do I need to sort through the entire table storing a single record for the latest date in a temporary table, overwriting it if a later date is found? I think there must be a better and more efficient way to do this.

 

[rbertram]

Nevermind - I figured it out.

I just created another query that grouped by ID and I set my critera to the MaxOfDate, then created and inner join between to the other query.

Sometimes I think too much into a problem and miss the easiest and should be most obvious solution.

 

[PHV]

Something like this ?
SELECT A.* FROM theTable A INNER JOIN
(SELECT ID FROM theTable GROUP BY ID
HAVING Max([Action Date])<=#05/20/2004#
) B ON A.ID=B.ID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244