Two primary keys to the same field?

[Access_Novice]

I have the following:

tblEmployees: Employee ID = Primary Key
tblSubcontractors: Employee ID = Primary Key
Table3: Employee ID = Foreign Key


The first two tables have a unique set of employeeID numbers. The third table, contains employeeIDs that appear in the first two tables. I have the primary keys from both tables joined to the foreign key in the third table. The results come up blank.

However, if I join only table #1 or #2 to the third table, then I do get results.

It seems like you cannot have two primary keys joined to the same foreign key. Is this correct? If so, is there a way around this?

[RuralGuy]

Not quite sure what you have there. Is the tblEmployees: Employee ID = Primary Key the SAME VALUE as the tblSubcontractors: Employee ID = Primary Key?

[June7]

Are you using INNER JOIN in the query?

Why do you have two tables for employees?

Are both of those primary keys autonumber type or are they custom ID?

[ssanfu]

I would have:

tblEmployees: EmployeeID = Primary Key

tblSubcontractors: SubcontractorID = Primary Key

Table3:       Table3ID = Primary Key (autonumbr
            EmployeeID = Foreign Key
       SubcontractorID = Foreign Key

[Access_Novice]

Not quite sure what you have there. Is the tblEmployees: Employee ID = Primary Key the SAME VALUE as the tblSubcontractors: Employee ID = Primary Key?

The employee ID values in table 1 and 2 are not the same.

[Access_Novice]

Are you using INNER JOIN in the query?

Why do you have two tables for employees?

Are both of those primary keys autonumber type or are they custom ID?

One table is for employees. The other table is for subcontractors. Each employee has a unique ID number (alpha numeric). Each subcontractor has a unique ID number (also alphanumeric).

The subcontractor IDs do not duplicate the employee IDs. I just thought it would be better organization split out the employees from the subcontractors.

[RuralGuy]

I'll bow out for now.

[ssanfu]

Text type fields are not very good as PK fields. You might want to read these:

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

[June7]

To reduce confusion, change to ContractorID.

What is purpose of Table3?

[orange]

It might be helpful to users if you would tell us a little about your database and its purpose.
What do each of the tables represent in your "business"?