Complicated relationship between two tables

  • Thread starter Thread starter hikaru
  • Start date Start date
H

hikaru

hi,

I have a problem in viewing related data between two tables. my two tables
are:

Tbl_Company:
CompanyID
Company Name
CompanyType (Consultant or Contractor)

Tbl_Projects:
ProjectID
ProjectName
ConsultantID (CompanyID FK)
ContractorID (CompanyID FK)

so, I have two relationship between the two tables. both are One-to-Many and
from Tbl_Company to Tbl_Projects.

How can I solve the issue? Are such relationships possible?
 
hi,

I have a problem in viewing related data between two tables. my two tables
are:

Tbl_Company:
CompanyID
Company Name
CompanyType (Consultant or Contractor)

Tbl_Projects:
ProjectID
ProjectName
ConsultantID (CompanyID FK)
ContractorID (CompanyID FK)

so, I have two relationship between the two tables. both are One-to-Many and
from Tbl_Company to Tbl_Projects.

How can I solve the issue? Are such relationships possible?
Click to expand...

Yes, this is a perfectly reasonable structure: you simply have two independent
foreign keys to the same table. You can display the data by adding
tbl_Projects to the query window, and adding tbl_Company *twice* - once
joining to ConsultantID and once to ContractorID. If a given project might
have only a consultant and no contractor, or vice versa, you may need to use a
Left Outer Join on both relationships.

You'ld do the same thing to establish RI - add tbl_Company twice. Access will
alias the second instance as tbl_Company1.
 
Thanks, it worked ^_^

John W. Vinson said:
Yes, this is a perfectly reasonable structure: you simply have two independent
foreign keys to the same table. You can display the data by adding
tbl_Projects to the query window, and adding tbl_Company *twice* - once
joining to ConsultantID and once to ContractorID. If a given project might
have only a consultant and no contractor, or vice versa, you may need to use a
Left Outer Join on both relationships.

You'ld do the same thing to establish RI - add tbl_Company twice. Access will
alias the second instance as tbl_Company1.
Click to expand...
 
Back
Top