Results 1 to 4 of 4
  1. #1
    whilburn is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    26

    Select Query, Append Query to update Main Table from Staging Table


    Views: 4 Size: 42.9 KB">Example.zip

    In real life application...
    1. I have created a table linked to an excel file. (The reason for this is because i will have to pull data in to my access db on a weekly basis and its easier to link to a file that is structured and only the data will change.)
    2. Identify records that exist in a staging table but do not exist in the Main Table.
    3. Append the records from step 2 into the Main table.

    I have attached the example db with my attempt to do the append but am getting errors and cannot resolve.

    Also, Can step 2 and 3 be performed at the same time?

    Thanks,
    Wayne

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    I saw the issue. Your main table was adding new data with an autonumber which it already had in the list.

    I pressed compact and repair database and your append query worked fine

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Also, Can step 2 and 3 be performed at the same time?
    I'm unclear what you mean here.

    You have a query which appends only data from another query which finds the data which you don't have....

    So you're doing it all in one go anyway?

  4. #4
    whilburn is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2017
    Posts
    26
    For anyone interested, i made a word doc to illustrate the process...

    Append Only Unique Records.zip

    Andy,
    I compacted/repaired and it worked as advertised.... I wonder what issue needed to be resolved by this action? I built/rebuilt the query in my example db and the main db several times and by the end, i was questioning not only Access' ability to do it, but my ability to get it done... My skill set lies in vba vs sql and mostly for excel..

    You also mentioned I was trying to add data with an autonumber.. that was just the FK to another table (lookup) and it updated it as well...

    The last thing you were asking about...
    I had to create a query to get the single record to append.. then create another query to call the first query and then append the record...
    I was wondering if it could all be written into a single query. technically i have two queries in my list... Not complaining but i am going to have alot of queries in my main database where i have close to 55 tables.... Of course not all of them will be appended on a weekly basis.. most of the look up tables will never change. Just wondered

    either way, Thanks again for your help.
    Wayne

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

Similar Threads

  1. Replies: 6
    Last Post: 12-03-2014, 10:28 PM
  2. Replies: 4
    Last Post: 03-10-2014, 11:33 AM
  3. Replies: 3
    Last Post: 01-24-2013, 02:38 PM
  4. Select Query > Update Query > Table Field
    By tuggleport in forum Queries
    Replies: 2
    Last Post: 08-09-2012, 07:04 AM
  5. Replies: 2
    Last Post: 01-31-2011, 08:31 AM

Tags for this Thread

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