Append Query - Add a new record or update an existing record

[Abacus1234]

I have 2 tables. A company table and a company personnel table.

The company personnel table contains a foreign key to the company table.

I want to replicate the records entered in these 2 tables, into 2 other tables for a different company.

As each record is entered, I can trigger an append query to update the second set of tables.

In the append for Company, I would leave out the ID which is an autonumber field.

Question 1. Could I check that the company I am appending is not already in the tables I am appending to and if it is update it rather than create a new record.



I would expect the Company Name to be unique.

[June7]

Why do you need two more tables? Why not just use the existing tables?

Yes, code can look for existing data and take an action depending on what is found. This could be an If Then Else construct in macro or VBA.

[Abacus1234]

Why do I need two more tables is a good question. I designed a part of the system and now I'm not quite sure how I got there. I need two different entities, but the data feeding them could be the same. I have a bound form and have introduced 8 subforms. I choose from the 8 different table with combo boxes. So at the moment if I try to pull from the same table for 2 of them, I get the same value populating both subforms. And so far I haven't found a way round that.

[June7]

I don't understand the data structure nor the form behavior.

If you want to provide db for analysis, follow instructions at bottom of my post.