Solved

Remove Duplicates with Access Query

Posted on 2013-06-13
3
17,919 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
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 24

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

929 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

12 Experts available now in Live!

Get 1:1 Help Now