how do delete duplicate account

[ges]

Can any one help me with SQL syntax how to delete duplicate query?
I just want to delete one of the duplicate not both duplicate accounts.

SELECT tblCollectorNotes.AcctIDAuto, tblCollectorNotes.CN_Account
FROM tblCollectorNotes
WHERE (((tblCollectorNotes.AcctIDAuto) In (SELECT [AcctIDAuto] FROM
[tblCollectorNotes] As Tmp GROUP BY [AcctIDAuto] HAVING Count(*)>1 )))
ORDER BY tblCollectorNotes.AcctIDAuto;

But the above syntax will delete all duplicate query instead of delete just
one duplicate account.

Thanks in advance for any help.

Ges

 

[Allen Browne]

See:
http://allenbrowne.com/subquery-01.html#DeDuplicate

The example keeps the lowest primary key value when there are duplicates.

 

[Dale Fye]

The problem with trying to "automate" the deletion of "duplicate" records is
determining which record to keep. In your case, you have only selected a
limited number of fields (3) from tblCollectorNotes, so at best you will only
see those three fields.

Allen has solved this problem by assuming that the record with the lowest ID
value is the one to keep, but if you have entered data into both of the
records, automatically deleting one of them, without scrutinizing all of the
data fields, may result in lost data.

Although it is more time consuming, I prefer to do this kind of effort in a
select query, display all of the fields, and then delete the "duplicates" one
record at a time, ensuring that I look at all of the fields, and make sure
that the record I'm going to keep has all of the appropriate data (from all
of the matching records).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[ges via AccessMonster.com]

Thanks Allen!

Ges

Can any one help me with SQL syntax how to delete duplicate query?
I just want to delete one of the duplicate not both duplicate accounts.

SELECT tblCollectorNotes.AcctIDAuto, tblCollectorNotes.CN_Account
FROM tblCollectorNotes
WHERE (((tblCollectorNotes.AcctIDAuto) In (SELECT [AcctIDAuto] FROM
[tblCollectorNotes] As Tmp GROUP BY [AcctIDAuto] HAVING Count(*)>1 )))
ORDER BY tblCollectorNotes.AcctIDAuto;

But the above syntax will delete all duplicate query instead of delete just
one duplicate account.

Thanks in advance for any help.

Ges