Combining Row Pair Values

  • Thread starter Thread starter Tim Boyden
  • Start date Start date
T

Tim Boyden

Hi All,

I have a table "tblTravelStats" with three fields: Passengers(double),
Origination(text), Destination(text)

I built a query that SUMs the number of Passengers and groups the
results by the Origination and Destination fields. However the results
show row pairs due to a separate result for the outbound and return
trips.For example I get results for New York to Chicago and Chicago to
New York. The Passenger total is not always the same for the two
pairs.

Is there a way to combine the 2 rows so I get a total of all trips
between New York and Chicago?

Thanks,

Tim Boyden
 
Try this --
SELECT IIf([Destination]>[Origination],[Origination],[Destination]) AS City,
IIf([Destination]>[Origination],[Destination],[Origination]) AS [End],
Sum(tblTravelStats.Passengers) AS [Total Travelers]
FROM tblTravelStats
GROUP BY IIf([Destination]>[Origination],[Origination],[Destination]),
IIf([Destination]>[Origination],[Destination],[Origination]);
 
Karl that worked great! Thank you so much for your help! I'll have to
learn more about using conditional logic in SQL. Any recommendations
on sources I can check out?
 
Back
Top