Solved

Remove Duplicates with Access Query

Posted on 2013-06-13
3
17,225 Views
Last Modified: 2013-06-26
Hi,

I have an access table that consits of 21 fields.  Of those 21 fields one field is an account number and another the state code, IL, NY etc.  Due to a data glitch, some records are being duplicated except the state field.  In essence there are two rows where 20 of the 21 fields are identical, and the state field is different for the rows.

I want to remove the duplicates based on a heirarchy of states that I have.  So, since AZ is higher on my list than IL, I want to remove the IL entry and leave the AZ record.

How do I do this in the 'Design Query' interface rather than SQL?

Thanks,

Artie
0
Comment
Question by:Rick_Preiss
  • 2
3 Comments
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
i can said you how to do that in sql, after you paste the sql you can change to the designer interface, but some example of your data and desired output will be helpfully to do that.
0
 
LVL 24

Accepted Solution

by:
chaau earned 400 total points
Comment Utility
As long as you mentioned Design Query I recommend that you start with the "Query Wizard". On the "Create" toolbar click on "Query Wizard". Select "Find Duplicates Query Wizard". On the first step of the wizard select your table. On the next step Move all the fields except the "State" field to the "Duplicate-Value fields" panel. In the next step move your "State" field to the "Additional Query Fields" panel.
On the last step select "Modify the design" option and click "Finish".
In the design view you will notice that all the fields have "Ascending" as a sort option, except for your "State" filed. This will have to be set to "Ascending" as well for your requirements.
Now, you can click on "View"->"Datasheet View" toolbar button.
At this stage you will see the duplicate entries with both States. See below
Duplicate Query Result - OriginalThe next step will be to restrict the result to the State that comes first in alphabetic order. You do this with a simple statement in the design mode. Switch back to Design Mode. You first field's criteria most likely contains a statement, similar to the image below:
Condition for DuplicateCopy everything to the clipboard after the keyword IN. In my case the statement looks like this:
(SELECT [Field1] FROM [Table1] As Tmp GROUP BY [Field1],[Field2] HAVING Count(*)>1  And [Field2] = [Table1].[Field2])

Open in new window

(Note, that I have not copied IN keyword)
Paste this condition to the "State" field's condition.
Modify the filed that goes after SELECT to MAX([State]) (I assume the column is called State in your table). So, the condition should be:
(SELECT MAX([State]) FROM [Table1] As Tmp GROUP BY [Field1],[Field2] HAVING Count(*)>1  And [Field2] = [Table1].[Field2])

Open in new window

Switch back to the "Datasheet View". Please note that what you see now on screen WILL BE DELETED on the next step. Validate it thoroughly, as there will be no rollback option. Note how many records in the current view, as you will have to cross check it in the next step. Use <Ctrl>+<End> key to scroll to the very end of the results to allow Microsoft Access to count the number of records. If in doubt, create a backup copy of your database before proceeding .
These records will be deletedIf you are happy with the result. Then switch back to Design view.
In the "Query Tools" toolbar switch the Query Type to "Delete". Click on "Run" toolbar button. Microsoft Access will give you a warning.
WarningPlease make sure that the number of records in the Warning message matches exactly the number of records you have checked before. Click on "Yes". You are done. Easy, huh?
0
 
LVL 4

Expert Comment

by:BAKADY
Comment Utility
@chaau: this is a tutorial.... great!!!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now