Solved: Query Help: Listing Unique Records

[Jacob Hilderbrand]

Hi

I am having trouble with a query and hopefully someone can help me out.

Given the following setup:

Table: "Table 3"
Fields: "ID", "A", "B", "C", "TransactionDate"

There can be several of the same ID listed.

What I want to do is this. For each ID, list only one record (and show all 5 fields). If there are multiple, then list the record from the latest TransactionDate.

Now, the tricky part. There can be multiple TransactionDates, normally I just want the latest, but if there are several records for the latest date, I want to list them all.

So if ID "ID1" has the following records for dates:
7/4/06
7/5/06
7/6/06

I want the last one, but if these are the records:

7/4/06
7/6/06
7/6/06
7/6/06

I want the last 3 records.

Thanks

Jake

[OBP]

Jake, that is a pretty tricky one, I don't think my SQL is up to it. It is easy enough to do one or the other, list them all by a date or list them by the last date only. But to be able to mix and match I don't know.
It is easy enough to show them on a Form/Subform though.
Sorry, I couldn't do it on a Form either LOL.

[Jacob Hilderbrand]

Is there a way to check each record and build a record set to use for the query? Basically I can use VBA to loop through each record and then could I add it to a new record set or something else and then have the query or table or whatever list only what I populated?

[asingh]

Hello DRJ...
Am attaching a sample Access Data base...It seems to work.
Following is the logic...
1. Group by dates.
2. Get the MAX transaction date from (1) -- This is that transaction Date which is the largest in the whole set.
3. Group by ID and get their MAX transaction dates. [Shows all unique ID's with their greatest transaction dates]
4. Get all ID's which have a transaction date that = (2). [i.e pick those ID's that have a transaction date that equals the maximum transaction date of the whole set].
5. Query (4) and (2) --- and find the Mismatches. [shows those ID's which dont have a maximium transaction date that equals the maximum transaction date of the whole set].
6. Append result set of (4) into "tbl_final_Sorted".
7. Append result set of (5) into "tbl_final_Sorted".

Have included a macro --- "mcr_sort_data" that does it all.

The raw data is stored in table "tbl_table3".

regards,

asingh

[Jacob Hilderbrand]

That seems to do the trick. :)

Thanks

Jake

[OBP]

Jake, I did get the table version to work. It only needs the one table, 2 queries and 2 forms.
Like Asingh the query is set to max date and filters the dates for the Main form, the Subform uses a normal query but the query is filtered by the Main form's ID and Date.
So now you have 2 ways to do it. :rotlaugh:

[Jacob Hilderbrand]

Well 2 is a lot better than 0. :)

Thanks