A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Linking between tables



 
 
Thread Tools Display Modes
  #1  
Old December 12th, 2008, 03:52 PM posted to microsoft.public.access.tablesdbdesign
sarah
external usenet poster
 
Posts: 633
Default 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  
Old December 12th, 2008, 04:37 PM posted to microsoft.public.access.tablesdbdesign
Beetle
external usenet poster
 
Posts: 1,254
Default 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  
Old December 12th, 2008, 04:57 PM posted to microsoft.public.access.tablesdbdesign
sarah
external usenet poster
 
Posts: 633
Default 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  
Old December 12th, 2008, 05:25 PM posted to microsoft.public.access.tablesdbdesign
Beetle
external usenet poster
 
Posts: 1,254
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:50 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.