Multiple Joins in Access

[John Roberts]

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

[orangeCat]

What exactly are you trying to do? What have you tried so far?
What is the structure of your tables?

Any name you define that includes a space must be enclosed in [] for Access.

Table A becomes [Table A]

However, you should NOT use names with spaces. It is a very bad habit.... avoid it.

[John Roberts]

Hey, thanks for your reply - my tables don't actually have spaces in, they are just for example. Here's what I'm trying to do...

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.

[NeoPa]

Yes John. This can be done. Access only handles JOINs against a single entity at a time though (EG. (TableA JOIN TableB) JOIN TableC).

There are various other rules, including no INNER JOINS to the right of any LEFT or RIGHT JOINs.

The simplest way to proceed though, is either to provide the requested information - the layout of the tables you want to work with, or to use the query design view and link the tables there. View / SQL then shows you the resultant SQL.

PS. Be very careful when using examples. Sometimes (yours is a case in point), the failure to put the example together properly ends up with quite a misleading example. Unless you're going to pay proper attention to it, it is much better to use real life examples.