Update and append table with selected records from another table

[hcg]

I imported an Exchange table containing all employees of our company worldwide into my database (tblEnterprise). The database also has a table with "local" employees called tblCompany. Whenever the tblEnterprise is updated (e.g. employee title change) or when new (local) employees are added, I want my tblCompany to be automatically updated and/or appended for the "local" employees only. tblCompany was initially built through a make-table query. However when a records are removed from tblEnterprise, the corresponding records in my tblCompany may not be deleted. In this case ex-employees therefore need to be "flagged" inactive. The tblCompany also contains other "flag" fields which are not present in the tblEnterprise and need default values upon entry of a new record. These flags may not be affected by any updates and may only be changed manually. I tried combining Update- and appendqueries but got stuck with some kind of key violation error. Both tables were linked on unique fields which never change. Could you please help me out here? [sig][/sig]

 

[Robertd]

Hi

When you update the tables you are only changing the data in the records ie you don't include the Keyfield in the fields to be appended.

When you add records you need to limit the source records to those that are not already in the destination table ie you need to add in the where clause

And Not Exists DestinationTable.KeyID = SourceTable.KeyID

here you want only the records from the SourceTable that do not exist in the DestinationTable hence not add duplicates

HTH
[sig]<p>Robert Dwyer<br><a href=mailto:rdwyer@orion-online.com.au>rdwyer@orion-online.com.au</a><br>[/sig]