Append new records but don't create duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an an append query that copies entries from one field in one table to a field of the same name in another table. I have a macro that opens the append query every time the first table is updated. However, I don't want duplicates of existing entries to be copied to the 2nd table. How can I modify my query, or work with multiple queries to either:
- avoid creating duplicate records to begin with or
- delete duplicate records so that only one of the duplicates exists

I don't want the database user's to receive any of MS Access's built-in warnings when any query runs.


Details:
I have a 1:1 relationship between two tables, Contracts and Customers. Contracts includes the field Customer_Name. Customer has the fields Customer_Name and Customer_Type.
e.g. 1 if the the Customer_Name is 'Microsoft', then the Customer_Type may be 'Industry'.
e.g. 2 if the Customer_Name is 'US Dept. of Agriculture' then the Customer_Type is 'Government'.
I don't want duplicates of 'Microsoft' or 'US Dept. of Agriculture' appearing in the Customer table.
 
If you have the primary keys set correctly you will be able to run an append
query without a problem. The query will only enter records that don't
produce duplicates.
In order to turn off the MS warning message do the following:

docmd.setwarnings false
docmd.openquery "appendqueryname"
docmd.setwarnings true

HTH
Compu Geek said:
I have an an append query that copies entries from one field in one table
Click to expand...
to a field of the same name in another table. I have a macro that opens the
append query every time the first table is updated. However, I don't want
duplicates of existing entries to be copied to the 2nd table. How can I
modify my query, or work with multiple queries to either:
- avoid creating duplicate records to begin with or
- delete duplicate records so that only one of the duplicates exists

I don't want the database user's to receive any of MS Access's built-in warnings when any query runs.


Details:
I have a 1:1 relationship between two tables, Contracts and Customers.
Click to expand...
Contracts includes the field Customer_Name. Customer has the fields
Customer_Name and Customer_Type.
e.g. 1 if the the Customer_Name is 'Microsoft', then the Customer_Type may be 'Industry'.
e.g. 2 if the Customer_Name is 'US Dept. of Agriculture' then the Customer_Type is 'Government'.
I don't want duplicates of 'Microsoft' or 'US Dept. of Agriculture'
Click to expand...
appearing in the Customer table.
 
Where do I paste this code? I've used a macro to automate the OpenQuery command, not a module.
 
In your macro before the entry to open the query add another line
SetWarnings ---- change this to Yes or No (not sure which as I haven't used
it)
After the entry to open the query add another line
SetWarnings ----- change this to the opposite of whatever works in the
previous line

HTH
 
It worked!

Thanks

Newbie said:
In your macro before the entry to open the query add another line
SetWarnings ---- change this to Yes or No (not sure which as I haven't used
it)
After the entry to open the query add another line
SetWarnings ----- change this to the opposite of whatever works in the
previous line

HTH
Click to expand...
 
Back
Top