no duplication in Append query

[AccessWater]

Hello, I am suing an append query to append new tables to an existing table. However, I only want to append the records that are not in the existing table. I have three variables: Category, Group, Project. None of them are unique. How to wirte this query.


For example: In existing Table:

Category Group Project
A 1 Pro1
A 1 Pro2
A 2 Pro1
B 1 Pro1
B 2 Pro1

Now I have two records:

A 1 Pro3
A 1 Pro1

I only want to append A 1 Pro3 to the existing table.

Thank you for your help.

 

[Wminnette]

What if you included another field in your target file and your data to append file. Call that field Index. That field would be the concatenated data from the 3 fields you mention.

A1Pro1
A1Pro2
etc.

Set this Index for only unique entries.

Build the same field in the data to append. When you appended, the duplicate Index fields would not be added.

A1Pro3 would append
A1Pro1 would not append, it appears in the target file.

 

[AccessWater]

Thank you Wminnette. It is a good idea. But the problem is that the project name is very long. And many project names have same words at the begning.

 

[annemu]

Hi Wminnette,
I used that idea for an update query for much the same purpose as described here - works fantastically well - thanks for a good idea!
Anne

 

[DCrake]

In theory you first need to create an unmatched query between old and new. Then use the results of this query as the feeder into the destination table.