hi
I have an access table that I want to find duplicate records based on three particular fields, one of them being a yes/no field. I then want code to compare the two matching records using a date field that already exist in the table and change the yes/no field of the record with the oldest date. are there any suggestions to be able to do this. your help would be appreciated.

thanks

Welcomet to Utter Access!

Use the Query Wizard to find the duplicate records. Then use that query as the source for an Update Query that is sorted by decending dates and the Top Values is set to 1.

My question for you is why do you have duplicate records?

I have NOT tried what I have suggested so there is no guarantee that this suggestion will do what you want, but maybe it will give you an idea...

Jack

These records do not match completely, what happens is I have records that are entered into the database and there is a yes/no field titled current, that defaults to Yes. e.g I have a table that describes how many kilometres a car travels in a given month. so I have a table with fields; car, month, distance, current (as described above) and a field with a timestamp in it that says when the data was actually entered.
the problem is that later on, the data can be amended so another record is put in, with the same car, in the same month, but the distance is different, this record will also show "yes" in the current field. What I want to do is find the two records in a table with the same car and month, and change the current field to "no" based on the oldest timestamp.

any ideas how this can be done?

thanks

It sounds to me like your data is not normalized. You should have two tables, one for the car info and one for Kilometers in a specific month, etc. Without knowing your specific fields your tables might look something like this:

tblAutomobile
AutomobileID (PK and auto)
Make
Manufacturer
Year
...other necessary fields...

tblTravel
TravelID (PK and auto)
AutomobileID (FK)
DistanceThisMonth
MonthName
...other fields that you track..

hth,
Jack