Solved

Remove Duplicates with Access Query

Posted on 2013-06-13
3
18,570 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

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…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

776 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