Results 1 to 3 of 3
  1. #1
    Huddle is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2010
    Posts
    318

    Syncing two tables. Would a Union Query work?

    I have two tables



    First is a linked table from an excel sheet (Table 1)

    The second table has the data from the first table with a few added fields. (Table 2)

    I want to be able to sync them. When a record is added or deleted from table 1 I want table 2 to update so the same records are in both.

    Should I use an union query? If so, can you walk me through it?

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Best solution IMO is to link the excel as table 1, and in table2 just take the primary key field of table 1 + the extra fields you need. You can then combine the two tables in a query that shows all data.
    In this way when changing info in the excel table will automatically change the existing fields. After adding records to the excel sheet you can write in access a query with an outer link that shows all records in Excel and if there is a linked record in table2, show that info.
    You can then write an append query that only show the records that exist in Excel but don't have a counterpart in the second table and add a new record to that table so you can fill in the extra fields.
    In the same way you can write a delete query that looks for records that have no longer a matching record in the excel table.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    To be clear, you want to add or delete records in table 2, you don’t want to change them if the data in table 1 changes?

    So to identify new records in table1 left join it to table 2 and set criteria to table2.pk is null

    You can modify this to become an insert query

    To identify records in table2 that are no longer in table1 reverse the process and change to a delete query

    Note that deleting records is generally considered to be poor practice, better to have a flag (perhaps a date field) to indicate the record should be ignored

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

Similar Threads

  1. Union Query from 3 tables?
    By Freddie81 in forum Queries
    Replies: 2
    Last Post: 05-30-2018, 08:03 AM
  2. Replies: 4
    Last Post: 08-09-2017, 12:06 PM
  3. Replies: 4
    Last Post: 12-20-2015, 02:35 PM
  4. Replies: 1
    Last Post: 07-03-2012, 02:47 PM
  5. Replies: 0
    Last Post: 12-28-2008, 01:56 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