Easy question...just brain dead! Delete query... 1

[CMooreJr]

Hey all....I cannot for the life of me figure out something.....here is the scenerio....

I have a table called "tbl_project_nbr_store_list". The users select store numbers from a list box and click OK to add store numbers to this list, however, sometimes they do it 2-3 times and it causes duplicate entries. The table simply has

Project number store number
51122 0213
51122 0213

I need to search this table, find the dups, and delete them, while keeping 1 record. I have set up a duplicate query that finds them, but cant seem to get it to delete the records from the table.....can you guys help please!!!

 

[richardii]

Remove duplicates from a table TEST.

Step1:

SELECT DISTINCT Tests.* INTO Tests_1
FROM Tests;

Step2:

DELETE Tests.*
FROM Tests;

Step3:

INSERT INTO Tests
SELECT Tests_1.*
FROM Tests_1;

Then get rid of the temp table tests_1:

DoCmd.DeleteObject acTable, "tests_1"