Unable to use more than two tables in query

[Ryker7]

I have a query that needs to pull info from three tables. The three tables are:

tblEmp - primary key of social security number
tblEmpChg - no primary key
tblLocalTravel - no primary key

tblEmp is linked to tblEmpChg by SSNO
tblEmp is linked to tblLocalTravel by EmpSocial

My test queries of tblEmp and tblEmpChg run ok.
My test queries of tblEmp and tblLocalTravel run ok.

When I design a query using all three tables, the query will not work. What am I doing wrong?

 

[lespaul]

This should work:


SELECT * FROM tblEmp INNER JOIN tblEmpChg on tblEmp.SSN = tblEmpChg.SSNO INNER JOIN tblLocalTravel on tblEmp.SSN = tblLocalTravel.EmpSocial

Leslie

 

[dhookom]

Since tblEmpChg and tblLocalTravel are not related to each other directly, you may get a "cartesian" join from this SQL. For instance, if an employee has 3 related records in tblEmpChg and 4 related records in tblLocalTravel then the resulting datasheet will have 12 records for the employee. I doubt this is very usefull (sometimes it might be).

If you wish to display the results in a form or report then you may need to use subforms or subreports.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

but adding DISTINCT will eliminate the duplicates.

Leslie

 

[dhookom]

Also, if fields from tblEmpChg and tblLocalTravel are included in the results, then you will still get multiple records. Referring to my earlier post, this will return 12 records:
[blue]

SELECT DISTINCT tblEmp.*, tblEmpChg.*, tblLocalTravel.*
FROM (tblEmp INNER JOIN tblEmpChg ON tblEmp.SSN = tblEmpChg.SSNo) INNER JOIN tblLocalTravel ON tblEmp.SSN = tblLocalTravel.EmpSocial;

[/blue]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

Will it still return 12 results if you only pulled specific information from each table instead of all information?

SELECT DISTINCT tblEmp.EmpSSN, tblEmpChg.Field1, tblLocalTravel.Field2
FROM (tblEmp INNER JOIN tblEmpChg ON tblEmp.SSN = tblEmpChg.SSNo) INNER JOIN tblLocalTravel ON tblEmp.SSN = tblLocalTravel.EmpSocial;

(Just trying to figure this out, even if the tables don't have a primary key, shouldn't the data still be ok since we are joining on a single field on the two different tables?)

Leslie

 

[dhookom]

I tried this as I specified in my previous posts and got 12 records. Each of the tblEmpChg records is included for each of the tblLocalTravel records (with the same SSN value). I don't know what you would expect the results to look like considering the following test I created using my previous sql:
[tt][green]
tblEmp
SSN FirstName
4 Duane
[/green]
[/tt]
[tt][blue]
tblEmpChange
SSNo TheChange
4 a
4 b
4 c[/blue]
[/tt]
[tt][red]
tblLocalTravel
EmpSocial TravelTo
4 Augusta
4 Fall Creek
4 Eau Claire
4 Marshfield
[/red][/tt]
[tt]
Query Results
SSN FirstName SSNo TheChange EmpSocial TravelTo
4 Duane 4 a 4 Augusta
4 Duane 4 a 4 Eau Claire
4 Duane 4 a 4 Fall Creek
4 Duane 4 a 4 Marshfield
4 Duane 4 b 4 Augusta
4 Duane 4 b 4 Eau Claire
4 Duane 4 b 4 Fall Creek
4 Duane 4 b 4 Marshfield
4 Duane 4 c 4 Augusta
4 Duane 4 c 4 Eau Claire
4 Duane 4 c 4 Fall Creek
4 Duane 4 c 4 Marshfield
[/tt]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

I see what you're saying. Thanks for the explanation.

les