Append Query Help

[MTSPEER]

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

[rpeare]

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.