locked
Access update 4 tables with one query RRS feed

  • 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



    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



    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