Deleting duplicate records

[Guest]

Hi - Is there a way to delete one of the duplicate rows using a delete query?

I used the query wizard and came up with the query showing duplicate records
but if I use that in a delete query, it deletes both duplicate records. I
want to delete one of the duplicatye records.

Is there a way to do this? any help will be much appreciated.

 

[Nikos Yannacopoulos]

Make a select query on the existing table, go View > Totals and leave the
Totals fiunction on all fields to the default Group By. This query will
return unique records only. Change it to a Make Table query, and it will
create a copy of the original table without duplicates.

HTH,
Nikos

 

[Guest]

The grp FN is the way to go, but I think you have to change one of the fields
(preferably one that's got unique data in each record) to Min or Max.

If unsure go to access assistance online and type how to delete duplicate
records – select Find, eliminate, or hide duplicate records in Access &
scroll down & click on I want to delete records that are duplicates of
others. You will see full explanation of how to eliminate duplicate records
using this suggested technique however instead of make a new table it
suggests that you use a delete query to eliminate duplicates within the
original table.

Issue I have is this

My table has no relationships and I have followed MS quidelines for deleting
duplicate records where have created Totals query to find Distinct Records
and then a Delete query where fields are equal, but time I run query get the
same msg "cannot delete specified records". I am running it locally, table is
not locked or read only and no relationships. In my mind I have done
everything that MS advise. What's the trick?

KM
NZ

 

[Guest]

I just wanted to let anyonw know who gives a >>>> that there is a better and
more efficient way of doing this than suggested by MS in

Try this

DELETE *
FROM Vessel as A
WHERE A.VesselID >
(SELECT Min(X.VesselID) FROM Vessel X WHERE A.Vessel=X.Vessel AND
A.Origin=X.Origin AND A.Flag=X.Flag

It is suggested by Roger Carlson http://www.rogersaccesslibrary.com who is
worth his weight in gold + supporting his website. I recall writing similar
years ago, but could not remember until roger reminded me. Besides that,
instead of a 2 step process like MS recommends it is one however you need to
know how to make alias of table.

Hope this helps you. Cheers from down under.

Lqdgold
NZ