469,913 Members | 2,230 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,913 developers. It's quick & easy.

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

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;
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
  6. SET TripSummary = Origins.PlaceName & " to " & Destinations.PlaceName & " by " Carriars.Name; 
...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
  6. SET TripSummary = Origins.PlaceName & " to " & Destinations.PlaceName & " by " Carriars.Name; 
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?

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
3 1957
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
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 
  10. SET TripSummary = Origins.PlaceName & " to " & Destinations.PlaceName & " by " & Carriars.Name;
Mar 10 '09 #3
32,231 Expert Mod 16PB
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.

Similar topics

2 posts views Thread by Peter | last post: by
6 posts views Thread by Fuzzydave | last post: by
14 posts views Thread by Developer | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.