Complicated relationship between two tables

[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?

 

[John W. Vinson]

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?

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.

 

[hikaru]

Thanks, it worked ^_^

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.