471,122 Members | 2,314 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,122 software developers and data experts.

Multiple Joins in Access

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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Table A.Person_Name, Table D.Favorite_Sport
  2. FROM (Table A 
  3. LEFT JOIN Table B ON Table A.ID=Table B.ID)
  4. LEFT JOIN Table C ON Table B.ID=Table C.ID
  5. LEFT JOIN Table D ON Table C.ID=Table D.ID
Nov 25 '10 #1
3 1762
orangeCat
83 64KB
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.
Nov 25 '10 #2
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.
Nov 26 '10 #3
NeoPa
32,349 Expert Mod 16PB
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.
Nov 28 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Prem | last post: by
1 post views Thread by Pieter Breed | last post: by
2 posts views Thread by narendra vuradi | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.