How to update more tham one table on a Access Query?

[Joe Au]

I would like to create a query which update 2 tables as coded below, but I
get error.

Parameters paccno text(10), pamt currency;
update table1 set amt=[pamt] where accno=[paccno]
update table2 set balamt=balamt+[pamt] where table2.accno in (select
table3.accno from table3 where table3.accno=[paccno]);

How do I fix it?
Thanks.
Joe.

 

[Douglas J. Steele]

You can't do it in a single query.

To be honest, though, it's not a good idea to store calculated values like
that. You're much better off creating a query that will return the balance,
and use that query rather than storing the balance in a table. In that way,
you don't have to worry that your two tables could get out of synch.

 

[david epsom dot com dot au]

You can update joined tables (table1 inner join table2).

update table1 inner join table2 on table1.accno = table2.accno
set amt=[pant], balamt=balamt+[pamt].

You need to index the join fields, and if you need table3 it
should be joined also (your use of table3 in the example is
faulty)

Also, you will probably find that the balance gets mixed up
whenever there is a problem, which always seems to happen.

(david)


(david)