[erpa]

I am working with a large data set containing vehicle positions. The data was collected with a very high sample rate so for each vehicle I have positional data every 5 milliseconds. This makes the data set huge, so I want to strip down the set so that it only contains one data record per second.

The table looks like this:

ID Date Time Long Lat
1 2011-06-30 21:26:35 59.529139 -1.628551
2 2011-06-30 21:26:35 59.529138 -1.628541
3 2011-06-30 21:26:35 59.529140 -1.628546
....
39 2011-06-30 21:26:36 59.529242 -1.628534
40 2011-06-30 21:26:36 59.529247 -1.628533
....

I want to only save one record with exactly the same Date and Time (in the above data set two records would have been saved, one from 21:26:35 and one from 21:26:36). I have used the "Find Duplicates Query Wizard", selecting the fields "Date" and "Time" and it gives me the duplicates so that I can delete all but one of them manually.

Problem: the date set contain over 2 million records so manual deletion is impossible.

Question: How can I do this with a query that lets me keep only one record for each identical Time and Date?

[OBP]

I would create a duplicate blank table by copy and pasting the "Structure Only" and then add a field that adds the date & time together and then set it to Indexed - No Duplicates.
Then create an Append Query based on the big table appending from the big table to the new one, you will have to add a Column Heading with the Concatenation of the date & time which is added to the new field in the table.
When you run that query it will add the fist date & time and then the rest will be excluded due to the "No Duplicates".
You can then create a "Delete" Query to delete all the records in the big table.

You may not need to append the ID field if you don't want to, you can use an autonumber in the new table

[erpa]

Works perfectly! Thanks for your help.