Solved

Remove Duplicates with Access Query

Posted on 2013-06-13
3
19,865 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 4

Expert Comment

by:BAKADY
ID: 39246096
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 25

Accepted Solution

by:
chaau earned 400 total points
ID: 39246229
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
ID: 39246241
@chaau: this is a tutorial.... great!!!
0

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

751 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