Append Query Duplicates (1 Viewer)

kitty77

Registered User.
Local time
Today, 11:54
Joined
May 27, 2019
Messages
693
How can I make my append query not append records that are all ready there? Based on a id number. Each record has a unique id number that gets enter. Its kind of a tracking number. but I don't want it duplicated by my append query. I have a form that has the append query button and you can press the button more (user operator) than once creating multiple records...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:54
Joined
Oct 29, 2018
Messages
21,360
Hi. You can either set a unique index for the ID field or modify your append query to exclude existing IDs.
 

Micron

AWF VIP
Local time
Today, 11:54
Joined
Oct 20, 2018
Messages
3,476
I suspect you won't know which are already there, otherwise you would be able to apply some sort of filter. So I agree the approach is to make a field or fields an index that doesn't allow duplicates. However, you will find that when you run a query against this, you will get 1 or 2 messages about a) inserting or updating records, and b) not being able to insert/update all of them due to 'this that and the other'. When you get to that point, you have options: turn off warnings, run query, turn warnings back on (which I don't do) or use the .Execute method of the database object, which I do use.
 

isladogs

MVP / VIP
Local time
Today, 15:54
Joined
Jan 14, 2017
Messages
18,186
Another way to achieve that result is to use the unmatched query wizard to create a select query of all records not already in the destination table.
Then change that from a select query to an append query and only new records will be appended - no duplicates
 

kitty77

Registered User.
Local time
Today, 11:54
Joined
May 27, 2019
Messages
693
Can you provide me an example of the unmatched query with an append?
 

isladogs

MVP / VIP
Local time
Today, 15:54
Joined
Jan 14, 2017
Messages
18,186
Certainly. This appends new records into tblData from tblImport where the ID field values don't already exist.

PHP:
 INSERT INTO tblData ( StartDate, EndDate, NCheck ) 
 SELECT tblImport.StartDate, tblImport.EndDate, tblImport.NCheck 
 FROM tblImport LEFT JOIN tblData ON tblImport.ID = tblData.ID 
 WHERE (((tblData.ID) Is Null));

For a detailed explanation, see my extended article about Synchronising Data which includes unmatched append queries such as this one and much more.
 

Users who are viewing this thread

Top Bottom