Query last 10 unique records

[rickyfong]

I got a large transaction file with deplicated records!! For unit price checking, I need to copy the last 10 unique records to another file with the same criteria but price is not the same as the current checking record.

For instance, I got 5000 records with around 400 records unit price is not the same as history.
For each checking, I needed to copy the same criteria historical records to a tempory file and then delete corresponding duplicated records first, following by another deleting action only to keep the last 10 records according to date field.

After doing this, the DB size grew a lot. Is there any other genious way to do this kind of filtering!! THanks a lot!!

 

[JHB]

...
After doing this, the DB size grew a lot.

Then do a Compact and Repair and the size will shrink.

 

[Old Man Devin]

You might be able to use the SELECT DISTINCT SQL command here, which returns only unique values from a recordset.

Check out: http://www.techonthenet.com/sql/distinct.php

As for getting the last 10, you could either take them manually from the query results, or have some clever criteria in the query where the ID has to be greater than Max(ID)-10, assuming your records have some sequential auto-number ID.