 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008RE: Deleting Duplicate Records (Allen Browne post to 3/20/08)
I also have duplicates in my Database and have entered the following for: - PrimaryID = ID1; - Table Name= TEST_MBS2; - Duplicate is defined as the same ID, LAST NAME, FIRST NAME, AMOUNT, and USED; - I wish to keep the lowest ID1 value. DELETE FROM TEST_MBS2 WHERE ID1 <> (SELECT Min(ID1) AS MinOfID1 FROM TEST_MBS2 AS Dupe WHERE Dupe.ID = TEST_MBS2.ID AND Dupe.LAST NAME = TEST_MBS2.LAST NAME AND Dupe.FIRST NAME = TEST_MBS2.FIRST NAME AND Dupe.AMOUNT = TEST_MBS2.AMOUNT AND Dupe.USED = TEST_MBS2.USED); What am I doing wrong (doesn't work) ? - Thanks for your help  SignatureFirstVette52 STEP 1: BACKUP your data before attempting the following. STEP 2: BACKUP your data before attempting the following. Without a backup you cannot restore the data if this does not work the way you expect. DELETE FROM TEST_MBS2 WHERE ID1 Not In (SELECT Min(ID1) AS MinOfID1 FROM TEST_MBS2 AS Dupe GROUP BY ID, [Last Name], [First Name], Amount, Used) John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > I also have duplicates in my Database and have entered the following for: > - PrimaryID = ID1; [quoted text clipped - 16 lines] > > - Thanks for your help I get an error 'Query must have at least one destination field' and then it opens the query with the cursor blinking after the last statement, '...Used)' Thanks for your help : ) SignatureFirstVette52 > STEP 1: BACKUP your data before attempting the following. > STEP 2: BACKUP your data before attempting the following. [quoted text clipped - 33 lines] > > > > - Thanks for your help I have numerous records with duplicate 'ID' numbers and different 'ID1' numbers that need to be kept ('AMOUNT' will be different or 'USED' will be different and, so, not identical). I want to ONLY delete those records with different ID1's where ALL OTHER FIELDS are identical. SignatureFirstVette52 > STEP 1: BACKUP your data before attempting the following. > STEP 2: BACKUP your data before attempting the following. [quoted text clipped - 33 lines] > > > > - Thanks for your help I realy don't understand why that query statement did not work. Did you open a query in SQL view and paste the statement in there. The posted SQL was not criteria for a field. DELETE FROM TEST_MBS2 WHERE ID1 Not In (SELECT Min(ID1) AS MinOfID1 FROM TEST_MBS2 AS Dupe GROUP BY ID, [Last Name], [First Name], Amount, Used) That query should do what you asked. '==================================================== John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County '==================================================== > I have numerous records with duplicate 'ID' numbers and different 'ID1' > numbers that need to be kept ('AMOUNT' will be different or 'USED' will be > different and, so, not identical). I want to ONLY delete those records with > different ID1's where ALL OTHER FIELDS are identical. Thank You John! It worked! I'm not sure what went wrong last night, but wien I came in this AM and did it again, it worked beautifully. Nice piece of code. ACCESS changed your code upon execution as follows: DELETE * FROM TEST_MBS WHERE ID1 Not In (SELECT Min(ID1) AS MinOfID1 FROM TEST_MBS AS Dupe GROUP BY ID, [Last Name], [First Name], Amount, Used); The only thing I did different was to add the ';' at the end. Thanks again! SignatureFirstVette52 > I realy don't understand why that query statement did not work. > [quoted text clipped - 20 lines] > > different and, so, not identical). I want to ONLY delete those records with > > different ID1's where ALL OTHER FIELDS are identical.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.