Access update 4 tables with one query

Question

Hello all,

I have 4 tables, different names, but the same structure. Now, I would like to update one column (in all tables) based on IIF statement.

This is my statement, but it doesn't work:

=IIf(([monthly]![Type]="FFF" Or [monthly]![Type]="SSS" Or [monthly]![Type]="CCC"),"BBB","NNN")

Can someone assist me?

Thanks in advance.

Answer 1

I've corrected the formula above:

UPDATE monthly

SET monthly.[Group] = IIf(([monthly]![Type]="FFF" Or [monthly]![Type]="SSS" Or [monthly]![Type]="CCC"),"BBB","NNN")

and it works but is it possible to update 4 tables with one query?

Answer 2

No.

 

Either

You create relations between the tables (so proper normalization of the data) and enforce cascading updates, so when you update the master table the changes are automatically propagated into all the related tables.

Or

You create 4 update queries.

Or

You create a VBA procedure to update each table 1 by 1.

---

Daniel Pineault, 2010-2016 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 3

Ok Thanks for the rep.

Definitely, I'm gonna create 4 queries as I am not able to make Relations between tables. I have default ID and have gotten the error "No unique index found for the referenced field on the primary table".

Btw, thanks once more!