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
- UPDATE [SELECT PlaceID, PlaceName FROM tblLocations]. AS test INNER JOIN tblFlightPlans ON test.PlaceID=tblFlightPlans.Leg1Origin SET TripSummary = test.PlaceName;
Expand|Select|Wrap|Line Numbers
- UPDATE
- [SELECT PlaceID, PlaceName FROM tblLocations]. AS Origins INNER JOIN tblFlightPlans ON Origins.PlaceID=tblFlightPlans.Leg1Origin
- [SELECT PlaceID, PlaceName FROM tblLocations]. AS Destinations INNER JOIN tblFlightPlans ON Destinations.PlaceID=tblFlightPlans.Leg1Dest
- [SELECT OrgID, Name FROM tblOrganisations]. AS Carriars INNER JOIN tblFlightPlans ON Carriars.OrgID=tblFlightPlans.Leg1OrgID
- SET TripSummary = Origins.PlaceName & " to " & Destinations.PlaceName & " by " Carriars.Name;
Expand|Select|Wrap|Line Numbers
- UPDATE tblFlightPlans
- INNER JOIN [SELECT PlaceID, PlaceName FROM tblLocations]. AS Origins ON Origins.PlaceID=tblFlightPlans.Leg1Origin
- INNER JOIN [SELECT PlaceID, PlaceName FROM tblLocations]. AS Destinations ON Destinations.PlaceID=tblFlightPlans.Leg1Dest
- INNER JOIN [SELECT OrgID, Name FROM tblOrganisations]. AS Carriars ON Carriars.OrgID=tblFlightPlans.Leg1OrgID
- SET TripSummary = Origins.PlaceName & " to " & Destinations.PlaceName & " by " Carriars.Name;
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