table linking and updating

[Guest]

i have 3 tables. one for supervisors, one for employees, and one overall
record table.

in the supervisors table, it has their name, and a supervisor ID # (1-4)

in the employees table, it has thier name, their supervisor name, and the
supervisor's ID # (1-4)

in the records table it has the employee's name, their supervisor,
supervisor's ID #, record number (which is unique) and various other aspects
of the record.

is there a way to link all these together, so say, if i changed the
supervisor's ID # from 1 to a 5, that all the other tables would update
accordingly? also the same for if the worker switched supervisors? the thing
is, each employee would have maybe... 30 records, to their name, and theres
quite a few employees, so manual data entry is not feasible say, 6 months
down the road, so i am trying to make these tables linked/updating themselves
now, while the database is relatively small.

thank you in advance!
-Justin

 

[Guest]

in the employees table, it has thier name, their supervisor name, and the
supervisor's ID # (1-4)
There is no need to have their supervisor name in the employee table - just
supervisor's ID.

in the records table it has the employee's name, their supervisor,

supervisor's ID #
Again there is no need to have their supervisor name or supervisor's ID in
the records table.

each employee would have maybe... 30 records, to their name

Why would the employee have more than one record?

 

[Guest]

is there a way to link all these together, so say, if i changed the
supervisor's ID # from 1 to a 5, that all the other tables would update
accordingly?
Make the supervisor ID a key field in the supervisors table.
In the employees table it is preverable to use an id instead of their name.
Make the ID a key field.
Use the supervisor ID in the employee table. Set a one-to-many relation from
supervisor to employee table.
Use the employee ID in the records table. Set a one-to-many relation from
the employee table to the records table.

 

[Guest]

each employee has multiple records to their names as each record is a case
they are working on, so each employee could have many...

i did all that you said, but i cannot figure out how to make it
automatically update itself still... the relationships seem fine, but in
terms of it updating, it doesnt seem to be working