Results 1 to 2 of 2
  1. #1
    MTSPEER is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    283

    Append Query Help

    Hello,

    I am having trouble finding a way to append records from table1 to table2 where the criteria in table1 isnt in table2. The problem is there are multiple records with the same field(ID) but two other different fields that I want to check to see if is in the other table. They are Group and Number. I have changed the join where to show all records that are equal between table1 and table2 and all records from table1. For example the data will loook like this:

    Table1:


    ID Group Number
    111 0 000
    111 1 100
    111 2 100
    111 3 100

    I can get it to append the records to table2 where fields Group and Number are different but can't figure out a way to append it where it looks for the ID also. I mean it will work the first time because that ID isnt't in table2, but when I append it again with a different Group and Number it wont work because that ID is already in there. I'm trying to make work so it will append that record if all 3 ID, Group and Number aren't in table 2

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You can do one of serveral things

    1. Create a multiple field index on table 2 that includes all 3 tables. This would prevent any duplicates from being posted to the table. This is kind of heavy handed though and may not be the best solution especially if any of these three 'key' fields can change
    2. When you want to add new data to your table 2 do it in two phases. Phase 1 would be to update existing records that have matching ID/GROUP/NUMBER (by the way don't use reserved words in field names, number being one of them). Phase 2 would be to append any new records.
    3. Purge your table 2 of any relevant data (delete it) before doing a new import of data so for instance if you know you're importing new data from ID 111 you delete all data related to ID 111 before performing the import.

    I would question why you need to copy this data to a new table at all though. Temp tables are rarely necessary in my experience and, if you intend to use the data in them over time, tend to lead to more problems than they solve.

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: 1
    Last Post: 10-06-2011, 08:37 AM
  3. Replies: 7
    Last Post: 07-21-2011, 01:01 PM
  4. append query
    By kroenc17 in forum Queries
    Replies: 8
    Last Post: 11-30-2010, 10:09 AM
  5. Append query won't append
    By yelkenli in forum Queries
    Replies: 5
    Last Post: 02-12-2010, 11:19 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