Linking between tables

[Sarah]

I want to create a link / relationship that associates a People Table record
with a Company Table record. I.e., if a record in the People Table says that
Joe Smith works for Company ABC, I want that record to be linked to the
Company ABC record in the Company Table (and vice versa).

Ultimately, I want to be able to see the Company Table details of the
corresponding employer when I click on a People Table record.

Can you help?

 

[Beetle]

If Joe Smith leaves Company ABC and goes to work for the
Acme Slinky Shoe Corp., do you want to store the fact that he
used to work for Company ABC for historical purposes? Or do
you only care about the business they currently work for?

 

[Sarah]

Ideally, yes, I would like to store the historical employment information in
the Company table, as long as it can be designated as historical and not
current.

 

[Beetle]

Then you have a many-to-many relationship between Companies and
People. A Company can have many People working for it, and any given
person in the People table can work for many Companies (over a period of
time).
This type of relationship needs a third (junction) table to define it. The
junction table would hold the Primary Key values from the other two tables,
as well as a field (like a Yes/No field) to indicate which Company is the
current employer. If you need to know the dates a person started and
ended working for a Company, then you would add a couple of date
fields to the junction table also. Actually, if you do store the dates, then
you don't need the Yes/No field for current employer. You can just query the
junction table for the CompanyID with a start date but no end date.

An example structure;

tblCompanies
**********
CompanyID (Primary Key)
CompanyName
Address
City
State
Phone
other attributes of each Company

tblPeople
*******
PersonID (PK)
FirstName
LastName
Phone
other attributes of each person

tblEmployment (the junction table)
***********
EmploymentID (optional PK)
CompanyID (Foreign Key to tblCompanies)
PersonID (FK to tblPeople)
StartDate
EndDate

In the junction table you could use CompanyID, PersonID and StartDate
as a combined PK, or you could add a surrogate PK like EmploymentID
(as in the example).

You would then, for example, create a form/sub form for data entry where
the main form would be based on tblCompanies and the sub form would be
based on the junction table. The sub form would display (typically via
combo boxes) all the People that work for a Company (again, you could
use a query of the junction table if you only want to show the current
employees).

You could also have another similar form with the main form based on
tblPeople and the sub form based on the junction table which would show
all the Companies a person has worked for.