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 derfloh 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
Joined
Feb 20, 2003
Messages
217
Location
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