473,327 Members | 1,920 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Left Join in a subquery not working in access 2010

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,
  4.  
but this does not work, how do I fix this?
Nov 14 '11 #1
3 2428
NeoPa
32,556 Expert Mod 16PB
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
falcs
5
Could you give an example please?
Nov 15 '11 #3
NeoPa
32,556 Expert Mod 16PB
Sure.

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

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

Similar topics

3
by: Dave | last post by:
I have a list of towns in tblTownData, and a list of people's names in tblNames. During a year, there are multiple events where more names are added in tblNames, for a particular town. ie:...
14
by: Ryan | last post by:
I want to do the following SQL statement in Access. However, it won't allow me to have the secondary part of my join statement and tells me that this is not supported. OK, so Access doesn't support...
2
by: Darryl Kerkeslager | last post by:
The following SQL does as I intend, but ... SELECT offender_id, off_name, inv_ppo_id, add_note, add_zip_id, it_name AS Purpose FROM (offender INNER JOIN (investigation INNER JOIN...
2
by: lelandhuang | last post by:
I am developing reporting service and using lots of 'LEFT OUTER JOIN', I am worried about the performance and want to use some subquery to improve the performance. Could I do that like below, ...
2
by: David F | last post by:
Hello, I have what seems like a simple left join query, but Access returns results like it's an inner join. I've tried numerous combinations without success. I have a table (ProjectList)...
1
by: henryrhenryr | last post by:
Hello! I'm having a bit of trouble with a query. I am trying to paginate a set of results using LIMIT. Each result has a bit of 'basic' data and potentially multiple 'additional' data. I have...
3
by: rrstudio2 | last post by:
If I have two tables and need to do a left outer join and include a where statement on the second table, it seems like the left outer join becomes an inner join. For example: Table: Names id...
4
by: polycom | last post by:
Assistance needed to optimize this query SELECT SD.content_id AS Id, SD.title AS Title, CT.name AS Contenttype, PV.content_id AS SponsorId, ...
2
by: knkk | last post by:
Please see this: WARNING Error while executing query: select event_type, EventId, VenueId, Price, Schedule, ev.startDate, ev.endDate, events.name eventname, movies.name moviename from...
1
by: Mark Bordelon | last post by:
Hey forum. Not a SQL newbie here but new to the forum. A somewhat advanced query has my stumped. I have a table of session information that provides log-ons and log-off times like this (column names...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.