Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

CMooreJr

Technical User
Feb 20, 2003
217
US
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!!!
 
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"
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top