Answered by:
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.
Friday, January 20, 2017 1:19 PM
Answers
-
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
- Edited by Daniel Pineault (MVP)MVP Friday, January 20, 2017 1:40 PM
- Marked as answer by tocy Friday, January 20, 2017 2:19 PM
Friday, January 20, 2017 1:37 PM
All replies
-
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?
Friday, January 20, 2017 1:29 PM -
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
- Edited by Daniel Pineault (MVP)MVP Friday, January 20, 2017 1:40 PM
- Marked as answer by tocy Friday, January 20, 2017 2:19 PM
Friday, January 20, 2017 1:37 PM -
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!
Friday, January 20, 2017 2:27 PM