Solved

Remove Duplicates with Access Query

Posted on 2013-06-13
3
20,612 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

623 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