Updating two tables from one form

[radicalrik]

I have a table called Primary Employees and another table called Secondary Employees. I also have a form where you can add new employees, which updates the Primary Employees table. What I would like to do is, after I add a new employee have the new data update both the Primary and Secondary table at the same time. These tables have the same information within them. These two tables are currently relational to one another, but I still have to go in and manual update the Secondary Employee table after I add a new employee. I have to have two databases because of other form requirements. Any help you could provide would be greatly appreciated.

 

[RV]

These tables have the same information within them.

I have to have two databases because of other form requirements.

Your database structure shouldn't be form driven.
Use one table, not two.
If you want to register an Employee as being Primary or Secondary, add a column to your table to register as such.

RV2

 

[radicalrik]

Believe me I've tried everything, but having the two tables is the only way I have gotten the results I need. I am using combo boxes in the form for both the Primary and Secondary fields. When entered the data into the primary field it would change the Secondary field as well. Not what I wanted. Anyway, is there a way I can update two tables using one form, or when I update a table have it update another table?

 

[neileg]

The usual way to model this is to have one table for employees and one for jobs. The jobs table will have an entry for every job an emplyee has, and the primary employment will be flagged as such. Most payroll/HR systems work this way.

This also has an advantage that you can maintain the employee data separate from the job data. Changing, say, the home address means only altering the employee record. If one job ceases and another starts, you only alter the job table. Where I work, people can have more than two jobs and this staructure allows you to maintain any number of jobs.