Delete query

[JOM]

I have a table that has 20 fields and might contain duplicate information
based on 3 fields(ID, LoanNum, date). How do I create a delete query that
would look at each record and based on those 3 fields delete 1 or more of the
duplicates and leave the other?
e.g.,
ID: 123
LoanNum: 44444
Date:01/01/2008

ID: 123
LoanNum: 44444
Date:01/01/2008

ID: 123
LoanNum: 44444
Date:03/01/2008

I would like to delete one of the duplicates and leave the other one there
so end up with
ID: 123
LoanNum: 44444
Date:01/01/2008

ID: 123
LoanNum: 44444
Date:03/01/2008

 

[Bob Barrows [MVP]]

I have a table that has 20 fields and might contain duplicate
information based on 3 fields(ID, LoanNum, date). How do I create a
delete query that would look at each record and based on those 3
fields delete 1 or more of the duplicates and leave the other?
e.g.,
ID: 123
LoanNum: 44444
Date:01/01/2008

ID: 123
LoanNum: 44444
Date:01/01/2008

ID: 123
LoanNum: 44444
Date:03/01/2008

I would like to delete one of the duplicates and leave the other one
there so end up with
ID: 123
LoanNum: 44444
Date:01/01/2008

ID: 123
LoanNum: 44444
Date:03/01/2008

Backup your database.
Run a maketable query that inserts unique records into a new table.
Delete all the records from your original table
Run an append query to append the unique records into your original table

Like this:
Select ID,LoanNum,[Date],First(field1) As FirstField1, ...
First(FieldN) As FirstFieldN
Into NewTable
From OriginalTable
Group By ID,LoanNum,[Date]

Delete * from OriginalTable

Insert into originaltable (ID,LoanNum,[Date],field1,...fieldn)
select ID,LoanNum,[Date],FirstField1, ... FirstFieldN
From newtable

 

[Allen Browne]

You cannot do that if the table has no primary key, because there is no way
to indicate which one to keep and which one(s) to delete.

Either add a primary key, and use a subquery to indicate which one to keep.
More about subqueries:
http://allenbrowne.com/subquery-01.html

Or it might be easier to depress the total button in query design view.
Group By the fields that define "duplicate", and choose First for the other
fields. Copy'n'paste the table in the database window to make a duplicate,
choosing "structure only" when it asks if you want the data too. Then change
this query to an Append query, and append to this new table. The new table
contains the de-duplicated data.