Update query based on count

S

sharman

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.
 
D

Duane Hookom

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top