Using Additional Criteria when Deleting Duplicates

[Guest]

How would you add additional criteria to specify which of the duplicate
records gets deleted in the append query?

Below I have two records, with the spaces acting as field delimiters:

Fld1 Fld2 Fld3
=== === ===
AAA 123 456
AAA 123 123

I only want one AAA record, but I also want the record where Fld2 is equal
to Fld3, (where 123=123) to be the record that is deleted.

 

[Douglas J Steele]

Any reason for using the condition "WHERE
(((IIf([field3]=[field2],True,False))=True))"?

"WHERE [field3]=[field2]" should be sufficient.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here is the sample you can use:
table name is mytable
field names are field1, field2 and field3

field2 and 3 contain numbers and by using "iif" you can check if the value
in both are equal and if so, it returns "True" and in query you show the
fields with Count result of more than 1 and "iif" result = true.

SELECT Field1, Count(Field1) AS FiledCounter
FROM mytable
WHERE (((IIf([field3]=[field2],True,False))=True))
GROUP BY Field1
HAVING (((Count(mytable.Field1))>1));

How would you add additional criteria to specify which of the duplicate
records gets deleted in the append query?

Below I have two records, with the spaces acting as field delimiters:

Fld1 Fld2 Fld3
=== === ===
AAA 123 456
AAA 123 123

I only want one AAA record, but I also want the record where Fld2 is equal
to Fld3, (where 123=123) to be the record that is deleted.