Multiple Joins in Access (1 Viewer)

[f9073341]

Hi,
Is it possible to access a tables data over multiple joins? For instance, for each row of Table A, I want the associated data from Table D. Is this possible in access? If it's not, could you suggest an alternative? Please see the example. Many Thanks.

SELECT Table A.Person_Name, Table D.Favorite_Sport
FROM (Table A
LEFT JOIN Table B ON Table A.ID=Table B.ID)
LEFT JOIN Table C ON Table B.ID=Table C.ID
LEFT JOIN Table D ON Table C.ID=Table D.ID

 

[vbaInet]

As long as there is some sort of ID that relates to both tables then it is possible. Simply use the Query wizard and select the fields you want from both tables. Access will sort out the joins for you.

For you own knowledge, once the query has been created, right click it in design view and select SQL View to view how it was formed.

 

[f9073341]

I have tried with the query wizard... but I think it's too complex for access to generate.

For each row in Table A, there's a lookup table (Table B) which finds the associated value. Table C, which is referenced through the lookup table (Table B) has the associated identifier. Table D then has specific items related to Table A (e.g. Total number of 'problems'). I want to sum up the problems for each association.

This is why I need to access information in Table D from Table A. Please help! My mind is blown!

Many Thanks.

 

[gemma-the-husky]

can you clarify why you need so many intermediate tables?

instead of algebraic references, can you explain the actual data involved. it's much easier to understand.

 

[f9073341]

OK, sure.


SELECT Requirement.Name, Test_Status.Number_Passed

FROM (tblRequirement
LEFT JOIN tblRequirement_to_Test_Lookup ON tblRequirement.ID=tblRequirement_to_Test_Lookup.ID)
LEFT JOIN tblTest ON tblRequirement_to_Test_Lookup.TestID=tblTest.ID
LEFT JOIN tblTest_Status ON tblTest.TestName=tblTest_Script_Status.TestName

The intention is that there can be a number of tblTest_Status entries for a given tblTest. So the query sums up those Test_Status'. So, if it finds two, which match the tblTest, one with the value 3 and one with the value 4, it will return 7. I'm not so concerned about that though at the moment. I just want to be able to see that data.

Many Thanks.

 

[d_profesor]

As Access said, have you tried to divide this query into two separate query ?

 

[gemma-the-husky]

the number and names of these tables is very confusing

lets say you have a "test management " table
and a "test status" table

where the test management table carries a value equivalent to an entry from the test status table.

2 tables

So why do you need two more intermediate tables. what data is in these tables?