Update query based on count

[Guest]

Hi,

I have a table that is similar to the one below

FieldA FieldB

AAA
AAB
AAA
AAC
AAA
AAB
BCA

I want to update FieldB with a certain value and the criteria is that the
value in FieldA is coming up more than once in the table. For example if I
want to update the FieldB with "HH", then this table will look like

FieldA FieldB

AAA HH
AAB HH
AAA HH
AAC
AAA HH
AAB HH
BCA

because AAA and AAB are coming up more than once. When I try to run the
query it gives me an error that EXISTS is missing. Can anyone help me. Thanks.

 

[Duane Hookom]

You could try
UPDATE tblSimilarToTheOneBelow
SET FieldB = "HH"
WHERE DCount("*","tblSimilarToTheOneBelow","FieldA='" & [FieldA] & "'")>1;