<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.apple-style-span
{mso-style-name:apple-style-span;}
span.apple-converted-space
{mso-style-name:apple-converted-space;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple style='word-wrap: break-word;
-webkit-nbsp-mode: space;-webkit-line-break: after-white-space'>
<div class=Section1>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Thanks Josh,<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>I did get an answer from the Microsoft Access newsgroup, but I’ll
post my original query and the query from the response that I got back that
worked. Maybe someone can identify if the working result is database neutral
because I would like to implement the final solution in MySQL:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>My original query:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>SELECT SUM(PASSENGERS) AS [TotalPAX], ORIGIN_CITY_NAME,
DEST_CITY_NAME<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>FROM [2007 Passenger Statistics - Orig and Dest]<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>GROUP BY ORIGIN_CITY_NAME, DEST_CITY_NAME<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>ORDER BY SUM(PASSENGERS) DESC;<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>The query I got in response to my posting on microsoft.public.access:<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>SELECT IIf([DEST_CITY_NAME]>[ORIGIN_CITY_NAME],[ORIGIN_CITY_NAME],[DEST_CITY_NAME])
AS City1, <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>IIf([DEST_CITY_NAME]>[ORIGIN_CITY_NAME],[DEST_CITY_NAME],[ORIGIN_CITY_NAME])
AS City2, <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Sum([2007 Passenger Statistics - Orig and Dest].PASSENGERS) AS
[Total Passengers]<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>FROM [2007 Passenger Statistics - Orig and Dest]<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>GROUP BY
IIf([DEST_CITY_NAME]>[ORIGIN_CITY_NAME],[ORIGIN_CITY_NAME],[DEST_CITY_NAME]),
<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>IIf([DEST_CITY_NAME]>[ORIGIN_CITY_NAME],[DEST_CITY_NAME],[ORIGIN_CITY_NAME])<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>ORDER BY Sum([2007 Passenger Statistics - Orig and
Dest].PASSENGERS) DESC;<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>I’ve never used conditional logic in SQL before, but after
reviewing the query it seems pretty straight forward.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Cheers,<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Tim<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
bostonphptalk-bounces@bostonphp.org
[mailto:bostonphptalk-bounces@bostonphp.org] <b>On Behalf Of </b>Joshua
Johnston<br>
<b>Sent:</b> Tuesday, June 17, 2008 1:26 PM<br>
<b>To:</b> bostonphptalk@bostonphp.org<br>
<b>Subject:</b> Re: [Bostonphptalk] Any SQL geniuses out there?<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Could you show us your query so far?<o:p></o:p></p>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>If you are looking for a single route then the query is
simple:<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>(Assume NY = id 1 and Bos = id 2 for simplicities sake)<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>SELECT sum(Passengers) FROM tblAirTraffic WHERE (Origination
= 1 AND Destination = 2) OR (Origination = 2 AND Destination = 1);<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>If you are looking for the sum between routes for all points
grouped together then you may need to do something like:<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>SELECT <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> sum(gPassengers), <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> Route <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>FROM (<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> SELECT <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> sum(Passengers) AS gPassengers, <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> CASE WHEN <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>
Origination > Destination <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> THEN <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>
Origination || ' ' || Destination <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> ELSE <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>
Destination || ' ' || Origination <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> END AS Route <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> FROM <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> tblAirTraffic <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> GROUP BY <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>
Origination, Destination<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> ) <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal>GROUP BY <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal> Route;<o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
<div>
<p class=MsoNormal>This is completely untested and might depend on your
database software and version but it illustrates the point. Definitely
test this query and benchmark it. It may be faster to handle the final grouping
in code instead of SQL <o:p></o:p></p>
</div>
<div>
<p class=MsoNormal><o:p> </o:p></p>
<div>
<div>
<p class=MsoNormal>On Jun 17, 2008, at 1:08 PM, Timothy Boyden wrote:<o:p></o:p></p>
</div>
<p class=MsoNormal><br>
<br>
<o:p></o:p></p>
<div>
<div>
<div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'>I have a database table named “tblAirTraffic” which
contains three fields: Passengers (double), Origination (text), and Destination
(text).<o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'>This table contains data on how many passengers flew from one
locale to another. I built a query that sums all of the passengers for a given
route such as Boston to New York grouped by the Origination and Destination
fields. The catch is that you will get a row result for both “Boston to
New York” and “New York to Boston”, often with a different
number of Passengers for the return route.<o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'>Is there a way to combine the row pairs and sum the combined passenger
totals?<o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'>Thanks for your help on this in advance.<o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'>Cheers,<o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'> <o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'>Tim<o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><span style='font-family:"Arial","sans-serif";color:black'>---------------------------<br>
</span><b><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:gray'>Timothy Boyden</span></b><b><span style='font-size:24.0pt;
color:black'><br>
</span></b><i><span style='font-size:10.0pt;font-family:"Arial","sans-serif";
color:black'>Network Administrator</span></i><span style='font-size:10.0pt;
font-family:"Arial","sans-serif";color:black'><br>
</span><span style='color:black'><a href="mailto:tboyden@supercoups.com"
title="blocked::mailto:tboyden@supercoups.com"><span style='font-size:10.0pt;
font-family:"Arial","sans-serif"'>tboyden@supercoups.com</span></a></span><span
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:black'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal style='line-height:14.4pt'><b><span style='font-size:7.5pt;
font-family:"Verdana","sans-serif";color:gray'>SuperCoups<sup>®</sup></span></b><span
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:black'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal style='line-height:14.4pt'><b><span style='font-size:7.5pt;
font-family:"Verdana","sans-serif";color:gray'>A Valassis Company</span></b><span
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:black'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal style='line-height:14.4pt'><b><span style='font-size:7.5pt;
font-family:"Verdana","sans-serif";color:gray'>350 Revolutionary Drive | E.
Taunton, MA 02718</span></b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal style='line-height:14.4pt'><b><span style='font-size:7.5pt;
font-family:"Verdana","sans-serif";color:gray'>Phone: 508-977-2034 | Fax:
508-977-0290 | <span class=apple-converted-space> </span><a
href="http://www.supercoups.com/" title="blocked::http://www.supercoups.com/">www.supercoups.com</a></span></b><span
style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:black'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><b><span style='font-size:7.5pt;font-family:"Verdana","sans-serif";
color:gray'> </span></b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><b><span style='font-size:7.5pt;font-family:"Verdana","sans-serif";
color:#999999'>Watch For Your Bright Blue and Yellow Envelope – Coming
Soon!</span></b><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:black'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><sup><span style='font-family:"Arial","sans-serif";
color:black'>---------------------------</span></sup><span style='font-size:
11.0pt;font-family:"Calibri","sans-serif";color:black'><o:p></o:p></span></p>
</div>
<div>
<p class=MsoNormal><u><sup><span style='font-size:10.0pt;font-family:"Verdana","sans-serif";
color:gray'><a href="http://www.redplum.com/"
title="blocked::http://www.redplum.com/ http://www.redplum.com/">RedPlum</a></span></sup></u><span
class=apple-converted-space><sup><span style='font-size:10.0pt;font-family:
"Verdana","sans-serif";color:gray'> </span></sup></span><sup><span
style='font-size:10.0pt;font-family:"Verdana","sans-serif";color:gray'>is the
ultimate source of value — online, in your mailbox, on your doorstep,
with your newspaper, and in your store.</span></sup><span style='font-size:
11.0pt;font-family:"Calibri","sans-serif";color:black'><o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><span style='font-size:9.0pt;font-family:"Helvetica","sans-serif";
color:black'>_______________________________________________<br>
Bostonphptalk mailing list<br>
<a href="mailto:Bostonphptalk@bostonphp.org">Bostonphptalk@bostonphp.org</a><br>
<a href="http://seven.pairlist.net/mailman/listinfo/bostonphptalk">http://seven.pairlist.net/mailman/listinfo/bostonphptalk</a><o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
</div>
</div>
</body>
</html>