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

SQL-fu failing as I attempt to Update using multiple joins

P: 11
G'day all,
I'm trying to update a single table (containing plans for flights) with information from the same and other tables (containing info for organisations and locations).

tblFlightPlans contains the txt-column 'TripSummary', which is what I'm trying to update, as well as ID numbers for the origin, destination, and plane carrier (eg, Qantas). The SQL code below successfully joins tblLocations and tblFlightPlans and updates TripSummary with a text name for the flight's origin.
Expand|Select|Wrap|Line Numbers
  1. UPDATE [SELECT PlaceID, PlaceName FROM tblLocations]. AS test INNER JOIN tblFlightPlans ON test.PlaceID=tblFlightPlans.Leg1Origin SET TripSummary = test.PlaceName;
  2.  
What I need is something very similar, but involving the above TWICE (two lookups), for an origin and a destination, as well as a carrier. I've tried this, which fails:
Expand|Select|Wrap|Line Numbers
  1. UPDATE 
  2. [SELECT PlaceID, PlaceName FROM tblLocations]. AS Origins INNER JOIN tblFlightPlans ON Origins.PlaceID=tblFlightPlans.Leg1Origin
  3. [SELECT PlaceID, PlaceName FROM tblLocations]. AS Destinations INNER JOIN tblFlightPlans ON Destinations.PlaceID=tblFlightPlans.Leg1Dest
  4. [SELECT OrgID, Name FROM tblOrganisations]. AS Carriars INNER JOIN tblFlightPlans ON Carriars.OrgID=tblFlightPlans.Leg1OrgID
  5.  
  6. SET TripSummary = Origins.PlaceName & " to " & Destinations.PlaceName & " by " Carriars.Name; 
  7.  
...and also this, which also fails (though again, the singular form works, if I'm only trying to update TripSummary with one PlaceName):
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblFlightPlans 
  2. INNER JOIN [SELECT PlaceID, PlaceName FROM tblLocations]. AS Origins ON Origins.PlaceID=tblFlightPlans.Leg1Origin 
  3. INNER JOIN [SELECT PlaceID, PlaceName FROM tblLocations]. AS Destinations ON Destinations.PlaceID=tblFlightPlans.Leg1Dest
  4. INNER JOIN [SELECT OrgID, Name FROM tblOrganisations]. AS Carriars ON Carriars.OrgID=tblFlightPlans.Leg1OrgID
  5.  
  6. SET TripSummary = Origins.PlaceName & " to " & Destinations.PlaceName & " by " Carriars.Name; 
  7.  
What am I doing wrong? How do I approach using multiple joins in Access?

If all else fails, is there some sort of "append" function that will allow me to make 3 separate queries and tack it on to the same textbox?

Notes:
Interchanging "(xxx) AS x" and "[xxx]. AS x" gives the same errors, so I don't think it's a bracketing problem. Also, "Carriars" is deliberate, the name "Carriers" is already taken somewhere else =P
Mar 10 '09 #1
Share this Question
Share on Google+
3 Replies


P: 11
UPDATE: Apparrently there is such a thing as 'nested INNER JOINs'. Looking into these now, but they're pretty obtuse beasts.
Mar 10 '09 #2

P: 11
UPDATE: Nevermind, all, got it sorted! =)
The following does what I want, ie, gives a text output of "Location1 to Location2 by Carrier1".

Expand|Select|Wrap|Line Numbers
  1. UPDATE 
  2. [SELECT PlaceID, PlaceName FROM tblLocations]. AS Origins INNER JOIN 
  3. (
  4. [SELECT PlaceID, PlaceName FROM tblLocations]. AS Destinations INNER JOIN 
  5. (
  6. tblFlightPlans INNER JOIN [SELECT OrgID, Name FROM tblOrganisations]. AS Carriars ON Carriars.OrgID=tblFlightPlans.Leg1OrgID
  7. ON Destinations.PlaceID=tblFlightPlans.Leg1Dest
  8. ON Origins.PlaceID=tblFlightPlans.Leg1Origin 
  9.  
  10. SET TripSummary = Origins.PlaceName & " to " & Destinations.PlaceName & " by " & Carriars.Name;
  11.  
Mar 10 '09 #3

NeoPa
Expert Mod 15k+
P: 31,561
Just to assuage your curiosity about the subquery notation, see Access QueryDefs Mis-save Subquery SQL.

Note, in most cases (except those where it seems Access recognises that it was created by itself) only the version with parentheses will be recognised.
Mar 11 '09 #4

Post your reply

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