Results 1 to 12 of 12
  1. #1
    JennyL is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    51

    How to update one field in master table to multiple child tables


    Hi all,

    I have been researching for many hours for a way to copy one field "data" to multiple tables with same field name. For example, I have a field called "archive date" in the master table, and 7 child tables with the same field. Once user enter the "archive date" on the main table, once user click on save command on the form, I want access to update all 7 child tables with the same archive date.

    any idea how I can do this? I tried update query but it didn't work.

    Greatly appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes, update queries.
    the criteria is the date in the master form, and it updates each table in the sub form.

  3. #3
    JennyL is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    51
    any idea how I can do that? I tried but without any success.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I tried update query but it didn't work.
    What does "didn't work" mean? Did you get an error message? The field wasn't updated?

    Need to know table names, field names, field types.

    Would you post a pic of your relationship window?
    Or post your DB with just a few demo records. No sensitive data... Do a "Compact & Repair", then Zip it.......



    Hmmmmmm..... 7 child tables????? Are they identical structures????

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You use the key in the master form (use the builder to get syntax correct)
    usu, forms!myForm!txtKey

    put this as criteria, and update the sub table.

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Curious why you need to do this in the first place if it is always the same date as the Master table? If you are linking the child tables to Master, you will always have access to that date anyway.

  7. #7
    JennyL is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    51
    the reason I am trying to add the "archive date" to the master table and then update all sub-tables is because I will be using that date to archive each table separately in the future. there might be another way to archive all tables via vba but I have a feeling that it will involve a lot of VBA coding. Therefore I am creating an Append Query for each table, which will prompt to enter the "archive date" range. In order for me to run the archive query in the future, i need each table to have the archive date, the primary key between all tables is account number, and the archive date should always be copied from the master table.

    Users will need to open up the customer account profile form, once account is closed, they will enter the archived date field in the master table form. What i want to achieve is once user entered that information in the master form, save and close, it should automatically update the records in the sub forms Archive Date records to match what is on the master form.

    Is update query the right solution for this process?


    Tbl_CustomerAccount
    AccountNum = primary key
    ArchiveDate


    Tbl_CAD
    AutoID = primary key
    AccountNum
    ArchiveDate

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Therefore I am creating an Append Query for each table, which will prompt to enter the "archive date" range.
    You still don't need the archive date in the secondary tables. An append query can have more than one table in it as the source - but it can only append to one table.

    So, in your append queries, just link the master table and one of the sub-tables using the AccountNum field. The criteria for the append query is then the archive data in the master table. If the ArchiveDate is to be included in the archived data (i.e. it is a field in the archive table), then just use the archive date in the master table as the source.

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Again not trying to complicate things but on the step you have an Append query for each table, link in the master table to that child table by AccountNum and then you can use the ArchiveDate from Master along with the child table fields to populate your Archive. That way you only have to add Archive date to the Master. Sorry if I am missing something with this, just throwing out alternatives.

    If you still need to update the child tables, maybe use this in the AfterUpdate on the Form (change tblChild1 with your table name, etc.).

    DoCmd.RunSQL "Update tblChild1 set ArchiveDate= '# & Me.ArchiveDate & #' where AccountNum=" & Me.AccountNum
    DoCmd.RunSQL "Update tblChild2 set ArchiveDate= '# & Me.ArchiveDate & #' where AccountNum=" & Me.AccountNum
    DoCmd.RunSQL "Update tblChild3 set ArchiveDate= '# & Me.ArchiveDate & #' where AccountNum=" & Me.AccountNum
    ...

  10. #10
    JennyL is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    51
    Quote Originally Posted by Bulzie View Post
    Again not trying to complicate things but on the step you have an Append query for each table, link in the master table to that child table by AccountNum and then you can use the ArchiveDate from Master along with the child table fields to populate your Archive. That way you only have to add Archive date to the Master. Sorry if I am missing something with this, just throwing out alternatives..
    oh my goodness, this works perfect! thank you so much for the suggestion!!

    Can I use the same method for Delete Queries? After I ran all of the append queries, I want to run delete queries to delete all the records from child tables and master table.

  11. #11
    JennyL is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    51
    Quote Originally Posted by John_G View Post
    You still don't need the archive date in the secondary tables. An append query can have more than one table in it as the source - but it can only append to one table.

    So, in your append queries, just link the master table and one of the sub-tables using the AccountNum field. The criteria for the append query is then the archive data in the master table. If the ArchiveDate is to be included in the archived data (i.e. it is a field in the archive table), then just use the archive date in the master table as the source.
    Thank you so much! It works perfectly!! Should i use the table source or query source? if i use the table, will it slow down the access speed? I am planning to split the database when everything is finished.

  12. #12
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Should i use the table source or query source?
    Not sure quite you mean there, but generally it doesn't really matter. Queries don't contain data; they only display (for select queries) or modify data in their component table(s).

    So for a split database (always a good idea), all your queries are going to be using the linked tables anyway. Besides which, unless your tables are large (many thousands of records), access times are generally not an issue.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-02-2014, 02:07 PM
  2. Replies: 3
    Last Post: 11-24-2013, 06:59 PM
  3. Replies: 4
    Last Post: 02-07-2013, 04:11 PM
  4. Replies: 5
    Last Post: 03-23-2012, 11:40 AM
  5. Replies: 1
    Last Post: 02-20-2012, 01:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums