If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Linking between tables
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? |
#2
|
|||
|
|||
Linking between tables
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? -- _________ Sean Bailey "Sarah" wrote: 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? |
#3
|
|||
|
|||
Linking between tables
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" wrote: 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? -- _________ Sean Bailey "Sarah" wrote: 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? |
#4
|
|||
|
|||
Linking between tables
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. -- _________ Sean Bailey "Sarah" wrote: 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" wrote: 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? -- _________ Sean Bailey "Sarah" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|