Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / April 2008

Tip: Looking for answers? Try searching our database.

Deleting Duplicate Records in a single table

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mike P - 23 Apr 2008 11:11 GMT
I have a table with duplicate records in it.  This happens during an External
Data file import from an Excel Worksheet, sometimes the user imports the same
worksheet twice.  The table does NOT have an Autonumber ID as a field.  The
table is indexed with duplicate fields allowed.   How do I write a Query to
delete multiple records?  

The two  Fields which identify if the data is duplicate are:
Symbol, EventDate
Jeanette Cunningham - 23 Apr 2008 11:23 GMT
Mike,
You can create a new blank table which is a copy of the table structure of
the original table.
Set up a unique index for the combination of Symbol and EventDate on this
table.
Append all the data from the original table to the copy.
Access will give you the message that it can't append all the data. (the
records it can't append will be the duplicates).
Accept this and click yes.
You should end up with the records minus duplicates in the copy.

Now rename the original table in a way so you know it is out of date.
Rename the copy to the original table name.
Recreate any relationships if necessary.
You should have a working table that will not allow duplicate records.

Jeanette Cunningham

>I have a table with duplicate records in it.  This happens during an
>External
[quoted text clipped - 8 lines]
> The two  Fields which identify if the data is duplicate are:
> Symbol, EventDate
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.