CODEWITHSUNDEEP
sqlvbacountms-access-2003
graidan
graidan

Reputation: 151

Access 2003 VBA / Sql Update TableA on count condition also in TableA

I'm very new to Sql, and having trouble wrapping my brain around the syntax. I have a table PM (with Fields Acct, Amt, Action, Reason), and I need to update a few fields in the table wherever count(Acct)>1, and the Amt is the same. Specifically, based on this select statement:

SELECT PM.Acct, PM.Amt
FROM PM
GROUP BY PM.Amt
HAVING (((Count(PM.Acct))>1));

That code returns the records I know need to be changed, anyway. Once someone explains it, I'm sure it will be obvious, but I'm stuck.

Upvotes: 3

Views: 556

Answers (1)

Taryn
Taryn

Reputation: 247880

You can do it this way, then you are still using your GROUP BY for your Amt. This was tested in MS Access 2003 and only updates the records in the sub-query:

UPDATE PM
SET PM.Amt = newValue
    , (other fields and values)
WHERE EXISTS
(
    SELECT t.Acct, t.Amt
    FROM PM t
    WHERE PM.Acct = t.Acct
    GROUP BY t.Acct, t.Amt
    HAVING Count(t.Acct)>1
)

Upvotes: 2

Related Questions