Creating a record in multiple tables

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

Guest

I am using Access 2000
I have 4 tables and I want the primary key to be the same in each table.
I want to use one form to set up the records.
What I am trying to do is input the reference once and automatically assign
that reference to the other three table records.
Is there any way I can do this ? (I am aware that the simple answer is to
type the number in 4 times)

Thank you in advance for your help.
 
You probably want to reconsider your data dictionary - table structure and
relationships. Normally, each table has a distinct primary key and is
related to other tables (if at all) by inclusion of a foreign key - that is
the primary key of another table.

Why would you want the same information recorded 4 times? As an example, for
employee info system you would have an Employee table with their name,
address, etc. For employee payroll for the last four years, you would NOT
have four tables - 1 for each year. You WOULD have a single salary/payment
table that records the employeeID, salary level, year, etc. This would be
related, many to one, to the employee table by the employeeID (primary key
of the employee table, and a foreign key of the salary table.

Perhaps if you explain more about what you are trying to accomplish, we
could help you more.
-Ed
 
Dear Ed,

Thank you for your reply.

I have four tables, the first has employees startdate, promotions, location
etc. The other three relate to share option schemes. Although the share
option files are set up exactly the same, legislation dictates that they are
kept in separate files as they are set up under different acts and
regulations. A further complication is that some staff have options under
each of the three schemes. The reason I want to see everthing on one form is
that each scheme has a maximum number of options per employee but each
employee has a maximum number of options which is lower than the sum of the
maximums of the three schemes (e.g. each scheme has a maximum of 100,000 but
the employees have a maximum of 250,000).
The maximums for the scheme and the employee depends on the status of the
employee at the date the options were granted.
The reason I want to open a record in each table at the same time is because
it is as important to show there are no options as to show options granted.

Many thanks for your help
Sean
 
Well, you could record all employee stock options in one table, and list the
controlling reg, strike price, vesting period, etc. to keep them all a
discrete record, but linked to a single employee record.

However, if you want to stick with your structure, set the employee to
option table relationships to: (1 - many) for all option tables. Make a
main form that show the employee info. Add 3 sub forms; one for each option
table. Use the subform wizard and it will walk you thru the correct
parent-child linking. No need to copy the employeeID across 3+ tables, the
relationship and form structure will handle everything necessary. Where
there are no related records for an employee, that particular subform will
remain empty, but changing the employee record the main form will show the
related records (if any) for that employee.

Use an unbound control on the main form and the Dsum function to show the
total of all options.
-Ed
 
Dear Ed,

Thank you for your last reply.

That answers my problem and gets me round the legal constraints.

Again, thank you and a Happy New Year.

Sean
 
Back
Top