Combining Row Pair Values

[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

 

[KARL DEWEY]

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]);

 

[Tim Boyden]

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?

 

[KARL DEWEY]

Sorry, I am self taught with a little help from the newsgroup.