Clever Geek Handbook

Access 2003 VBA / Sql Update TableA on account status also in table A

I am very new to Sql and am having trouble wiring my brain around the syntax. I have a PM table (with Acct, Amt, Action, Reason fields) and I need to update multiple fields in the table where count (Acct)> 1 and 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));

This code returns records that I know change anyway. Once someone explains it, I'm sure it'll be obvious, but I'm stuck.

+3


source to share


1 answer


You can do it this way, then you still use it GROUP BY

for yours Amt

. This was tested in MS Access 2003 and only updates the records in the subquery:



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
)
+2


source






More articles:


All Articles