By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Left Join in a subquery not working in access 2010

P: 5
I have a table called people which has a row for each person. Their address is represented by aacode and their number in the house is PERSNO there are then fields which represent the relationships between people. e.g. PERSNO=1 relationship to PERSNO=2

I want to produce a query which return a single row for each house which includes columns for each relationship between PERSNO=1 and the other residents, something like this.

aacode Relationship2, Relationship3, Realtionship4, etc

This is the code I produced so far:

Expand|Select|Wrap|Line Numbers
  1. SELECT People.ID, People.aacode, People.PERSNO, People.HRP, People.DVHsize,
  2. (SELECT Fam2.R01  FROM People  AS Fam2  WHERE Fam2.aacode = People.aacode AND Fam2.PERSNO = 2) AS Rel2,
  3. (SELECT Fam3.R01  FROM People  AS Fam3  WHERE Fam3.aacode = People.aacode AND Fam3.PERSNO = 3) AS Rel3,
  4. (SELECT Fam4.R01  FROM People  AS Fam4  WHERE Fam3.aacode = People.aacode AND Fam4.PERSNO = 4) AS Rel4
  5. WHERE (((People.HRP)=[People.PERSNO]))
  6.  ORDER BY People.aacode;
This code works, but returns a number which represents the relationship, and I want the text value taken from a table called Relatives.

I attempted to modify my subquery to

Expand|Select|Wrap|Line Numbers
  1. (SELECT Relatives.Relationship FROM Relatives 
  2. LEFT JOIN (SELECT People.R01 FROM People)  AS Fam2 ON Relatives.ID = Fam2.R01 
  3. WHERE (Fam2.aacode = People.aacode AND Fam2.PERSNO = 2)) AS Rel2,
but this does not work, how do I fix this?
Nov 14 '11 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,709
In a rush for now, but see the FROM clause in your inner subqueries. You need that in the main query too. It's where the LEFT JOIN is specified.
Nov 14 '11 #2

P: 5
Could you give an example please?
Nov 15 '11 #3

Expert Mod 15k+
P: 31,709

Expand|Select|Wrap|Line Numbers
  1. SELECT tA.*
  2.      , tB.*
  3. FROM   [tA]
  4.        LEFT JOIN
  5.        [tB]
  6.   ON   tA.AID = tB.AID
Nov 16 '11 #4

Post your reply

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