Need to get rid of records with duplicate info

[Ken Stupka]

I have a table that contains over 300,000 records. The records contain a
Longitude and Latitude field to define a precise and unique location.

The problem is that apparently there are a few record that have identical
field values for these two fields, (i.e. they share exactly the same
latitude AND longitude values) It is important that I get rid of the
offending records so that each record will define an unique global
position. To farther clarify the point, it is quite permissible for more
than one record to share a longitude value OR a latitude value but not share
both values.

Is there a way that I can delete these records?

Thanks,

 

[Marshall Barton]

I have a table that contains over 300,000 records. The records contain a
Longitude and Latitude field to define a precise and unique location.

The problem is that apparently there are a few record that have identical
field values for these two fields, (i.e. they share exactly the same
latitude AND longitude values) It is important that I get rid of the
offending records so that each record will define an unique global
position. To farther clarify the point, it is quite permissible for more
than one record to share a longitude value OR a latitude value but not share
both values.

Is there a way that I can delete these records?

Well, it's easy enough to identify the records with
duplicate values. Just create a new query using the Find
Duplicates wizard.

However, deleting all but one of the duplicates can be a
tricky proposition. Generally, I would not want to even try
to automate this kind of thing without knowing exactly how
the duplication occurred. If there's a chance they were
generated during user data entry, how do you know that it
wasn't a typo and the record should represent a different
location?

I recommend that you just run the find duplicates query and
manually delete any undesireable duplicates or correct any
missenterd records.

 

[John Spencer (MVP)]

In addition to Marshall's excellent advice, I would recommend that once you have
gotten rid of the duplicates, you add a compound index to the table using the
two fields and setting the index to not allow duplicates. This will stop all
duplicates from being entered into your table.

If the combination is your primary key, you can add the index easily by
selecting the two fields (in table design view) and then selecting Edit: Primary
Key from the menu.

If these aren't your primary key, then
-- Open the table in design view
-- Select View: Indexes from the menu
-- In the first empty row, enter LatLongIndex in the Index name
-- Choose the Latitude field under field Name
-- In the bottom section, Change Unique to YES
-- In the next row down, leave Index Name blank
-- Select the Longitude field
-- Close the index dialog
Save the table. If you have NOT eliminated all the duplicates, you will get an
error message.