Summing Records from Two Queries

[dancoe2004]

I have what I'm pretty sure is a simple issue but it currently has me stumped and any help would be greatly appreciated.

I have two queries which pull fuel uplift figures by airport from a master table, each query for a separate airline.

Each record is made up of Airport, Fuel Uplift, Month and Airline. I want to create a query that will show each Airport per month with the total fuel from both airlines. To do this I've created a new field titled AirportMonth and then applied a JOIN command. This works to a point but initially only returns records where the same AirportMonth value appears in both tables. I can alter the join properties to show all records from one of the tables but I need all records from both tables. Below is the SQL I have so far.

SELECT JAFyr.Airport, JAFyr.Month, [JAFyr]![Fuel Uplift USG]+[TFLyr]![Fuel Uplift USG] AS Expr2
FROM JAFyr LEFT JOIN TFLyr ON JAFyr.AirportMonth = TFLyr.AirportMonth;

Thanks in advance for any help!

 

[krowe]

im no expert but wouldn't you need an outer join of some kind

edit - or maybe a unionall, then a seperate query to do the sum

 

[plog]

I have two queries which pull fuel uplift figures by airport from a master table, each query for a separate airline.

I think this is cause of your problems. If you want all the data together in the end, don't build it seperately in the beginning. You should make this final query directly from the master table, or make it off a query that doesn't seperate airports.

If you need help with that, post sample data from your master table (including actual field and table names) and then what the result should be in your final query based on that sample data. Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
David, 3/2/2010, 98,
Sally, 4/2/2011, 107,
Tom, 5/7/2013, 14,

 

[dancoe2004]

Hi Plog,

Thanks for the post, I've taken your advice and have now managed to get to the same point using just one query. I still however need to find a way to sum records from this query.

I currently have

12 Months By Airline
Airport, Month, Fuel Uplift USG, Airline, AirlineMonth
ACE, 1, 12012, JAF, ACE1
ACE, 1, 15032, TFL, ACE1
ADB, 3, 1000, JAF, ADB3

From this I want to create a query that with the above data would simply produce

12 Months Totals
Airport, Month, Fuel Uplift USG, AirlineMonth
ACE, 1, 27044, ACE1
ADB, 3, 1000, ADB3

Seems so simple when written like that but still struggling, if you could help me crack this I'd be very grateful!