Joining Tables using more than 1 field

[MikeDutcher]

I have 3 tables:

Table 1
Employer Code
System Code
Data

Table 2
Employer Code
Employer Name

Table 3
Employer Code
System Code
County Code

For all records on Table 1, I want the employer name from table 2 (so I Join Employer Codes) and the county code from table 3 (where I want to join on Employer Code and System Code).

How can I join table 1 to table 3 using 2 fields?

 

[Telsa]

Create a UNION query. Mary

Rule 1: Don't sweat the small stuff.
Rule 2: EVERYTHING is small stuff!! X-)

 

[woyler]

SELECT Table1.*, Table2.EmployerName, Table3.CountyCode
FROM Table3
INNER JOIN(Table2 INNER JOIN Table1 ON
Table2.EmployerCode = Table1.EmployerCode) ON
(Table3.SystemCode = Table1.SystemCode)
AND(Table3.EmployerCode = Table2.EmployerCode)

PS. Try to avoid using spaces in table names:
ie: Employee Name
try: EmployeeName or EMPLOYEE_NAME.

 

[jflachman]

I'm confused about your table structure.
Is your employer code unique? (I'm assuming it is from table 2)
In other words, in your table 1, is there exactly 1 row with each employer code? If so, then I assume that there is exactly 1 system code that goes with each employer code?

If so, your tables contain duplicate data. You should remove the system code from either table 1 or table 3.
If I'm out in left field...here's the SQL statement to get what you want...
[tt]
SELECT Table2.[employer name], Table3.[county code]
FROM (Table1 INNER JOIN Table2 ON Table1.[employer code] = Table2.[employer code]) INNER JOIN Table3 ON (Table1.[system code] = Table3.[system code]) AND (Table1.[employer code] = Table3.[employer code]);
[/tt]...I think Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt