Results 1 to 7 of 7
  1. #1
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108

    Append query to add unique value only

    Hello,


    I need to append a table w/ only unique records. I have my master table and my table im using to add the new records from, the layouts are the same in both tables. i get the data for the new table by importing data from an excel sheet that comes from our work system and i hope to append this data to the master table to add any new records. The table names are:

    Master Table - tbl_All_WIGIs
    Table im using to update Master Table - tbl_WIGI_TEST
    Unique Value - Emp_ID

    I want to import all columns from the tbl_WIGI_Test table into the tbl_All_WIGIs and this is what i have come up with but i keep getting compiler errors.



    this is the example query i found:

    INSERT INTO [Perm_Table] (theField1, theField2, theField3)
    SELECT [Import_Table].field1, [Import_Table].field2, [Import_Table].field3
    FROM [Import_Table] WHERE [Import_Table].[Uniqe_V #] NOT IN
    (SELECT [Perm_Table].[Uniqe_V #] FROM [Perm_Table]);


    and this is what i have.

    INSERT INTO tbl_All_WIGIs (Emp_ID, Emp_Name, Posn_desc, WGI_Due_Dt, Original_WGI_Due_Dt, Date_LEI, WGI_Status, Pay_Plan, Grade, Step)
    SELECT tbl_WIGI_TEST.Emp_ID, tbl_WIGI_TEST.Emp_Name, tbl_WIGI_TEST.Posn_desc, tbl_WIGI_TEST.WGI_Due_Dt, tbl_WIGI_TEST.Original_WGI_Due_Dt, tbl_WIGI_TEST.Date_LEI, tbl_WIGI_TEST.WGI_Status, tbl_WIGI_TEST.Pay_Plan, tbl_WIGI_TEST.Grade, tbl_WIGI_TEST.Step)
    FROM tbl_WIGI_TEST where tbl_WIGI_TEST.Emp_ID not in
    (Select tbl_All_WIGIs.[Emp_ID] from tbl_all_WIGIs);


    since i am appending all the columns can i just do it like this:

    INSERT INTO tbl_All_WIGIs.*
    SELECT tbl_WIGI_TEST.*
    FROM tbl_WIGI_TEST where tbl_WIGI_TEST.Emp_ID not in
    (Select tbl_All_WIGIs.[Emp_ID] from tbl_all_WIGIs);


    any help would be appriciated.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make Q1 to join the existing table & the incoming new data table ,
    make it an OUTER join to show all items in new table NOT in target table.
    make Q2 an append query to add items in Q1 to target table. These will be unique to the new table.

  3. #3
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Ranman,
    thanks for the reply.. but none of that makes any sense.. sorry i dont really know SQL..

    I am only using the below qurrey, i am not sure what Q1, Q2 mean and i am somewhat familiar w/ Outer joins but not sure how to set it up in SQL

    INSERT INTO tbl_All_WIGIs (Emp_ID, Emp_Name, Posn_desc, WGI_Due_Dt, Original_WGI_Due_Dt, Date_LEI, WGI_Status, Pay_Plan, Grade, Step)
    SELECT tbl_WIGI_TEST.Emp_ID, tbl_WIGI_TEST.Emp_Name, tbl_WIGI_TEST.Posn_desc, tbl_WIGI_TEST.WGI_Due_Dt, tbl_WIGI_TEST.Original_WGI_Due_Dt, tbl_WIGI_TEST.Date_LEI, tbl_WIGI_TEST.WGI_Status, tbl_WIGI_TEST.Pay_Plan, tbl_WIGI_TEST.Grade, tbl_WIGI_TEST.Step)
    FROM tbl_WIGI_TEST where tbl_WIGI_TEST.Emp_ID not in
    (Select tbl_All_WIGIs.[Emp_ID] from tbl_all_WIGIs);

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Build a query (name it Q1 or whatever you want) as described by Ranman. Do same for Q2.

    Use the query Designer, not try to manually type in SQLView
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    ok, apparently i need it broken down barney style.. cuz i dont really have any idea what your telilng me to do. I dont mind reading if you have links to what your telling me.

  6. #6
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Is anyone able to look at my code and at least tell me if its a syntax error or the whole thing is just wrong?

    INSERT INTO tbl_All_WIGIs (Emp_ID, Emp_Name, Posn_desc, WGI_Due_Dt, Original_WGI_Due_Dt, Date_LEI, WGI_Status, Pay_Plan, Grade, Step)
    SELECT tbl_WIGI_TEST.Emp_ID, tbl_WIGI_TEST.Emp_Name, tbl_WIGI_TEST.Posn_desc, tbl_WIGI_TEST.WGI_Due_Dt, tbl_WIGI_TEST.Original_WGI_Due_Dt, tbl_WIGI_TEST.Date_LEI, tbl_WIGI_TEST.WGI_Status, tbl_WIGI_TEST.Pay_Plan, tbl_WIGI_TEST.Grade, tbl_WIGI_TEST.Step)
    FROM tbl_WIGI_TEST where tbl_WIGI_TEST.Emp_ID not in
    (Select tbl_All_WIGIs.[Emp_ID] from tbl_all_WIGIs);

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    There is an extra parentheses. They must be in pairs - an even number, count them.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-13-2014, 05:30 PM
  2. Replies: 9
    Last Post: 01-20-2014, 12:06 PM
  3. Replies: 6
    Last Post: 10-23-2013, 08:06 AM
  4. Replies: 2
    Last Post: 05-21-2012, 08:46 PM
  5. append xml data and rename unique id
    By DanChirila in forum Import/Export Data
    Replies: 1
    Last Post: 04-01-2012, 11:39 AM

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