Return unique records from a table with duplicate records.

[Zwoker]

Greetings everyone,

I have a problem that I hope has a simple solution. I am using MS Access 2003.

I have a table that is a list of financial transactions. I am using a make table query over that table, and want to return a single record where multiple exist for the five fields that are keys on the table. The rule as to which record I want returned where many exist is to use the record with the most recent transaction date (a field on the transaction table).

I named the five key fields first in the query, and sorted by them. I have used Max(transaction_date) field in the query (and set that to ascending in the sort too), with every other field being set to Group_By, and set the Unique Fields property to Yes in the query properties.

But, I still get duplicate records created in my new table.

One obvious cause that I don't seem to be able to (easily) avoid is that I want more fields extracted in my make table query than just the ones I have named. These fields are things like Payment Amount, so each will likely be different, leading to the Unique Fields property not doing what Iike would like it to do.

One final complication: I only want transactions of a certain type, so I have a criteria set in one of the extra (non-key) fields.

Is there an option in MS Access that I haven't set that I should? Is my expectation that Max will cause MS Access to only retrieve record with the latest transaction date wrong?


Thanks for any suggestions.

[Rabbit]

Create a query that returns the unduplicated records with only the key fields. And create a second query that will join that query back to the original.

[NeoPa]

I can probably help with this but I'm sure I will understand better what you have and what you require if you post a copy of the SQL you are currently using.
That will include the names of all the fields (which I don't know from your post) as well as how you see the data fitting together.