Query by Columns?

[Adams]

Can Access 97 query by columns? For example:

Departure City Arrival City Amount
Chicago Orlando 100.00
New York St. Louis 200.00
Orlando Chicago 200.00
St. Louis Orlando 100.00

Results
Chicago-Orlando 300.00
New York-St.Louis 300.00

Does additional coding need to be done in VB? Any ideas.

 

[ghubbell]

Hi Adams,
Please make a copy of your table and name it Table1.
Open a new query in design view and select the SQL view. Paste below in to the SQL view:

SELECT Table1.[Departure City] AS [From/To], Table1_1.[Departure City] AS [To/From], [Table1]![Amount]+[Table1_1]![Amount] AS RoundTrip
FROM Table1 INNER JOIN Table1 AS Table1_1 ON (Table1.[Arrival City] = Table1_1.[Departure City]) AND (Table1.[Departure City] = Table1_1.[Arrival City]);

Hope this is what you're looking for...Please verify before running the query, that your field names are in fact as you have listed above. I'm guessing your last line of supplied data should have read :
St. Louis New York 100.00

Give it a go!
Gord
ghubbell@total.net

 

[RickSpr]

Gord's response is all you need if a given combination of departure and arrival cities occurs exactly once in the table, and if the reverse combination also occurs exactly once. However, if there are any duplicate rows, the resulting amounts will be too large for those rows. And if a given combination occurs but the reverse doesn't, those rows will be left out.

Instead, you can write a VBA function to create a column that lists the cities in alphabetical order, and use that column in a GROUP BY clause to add up the amounts. The function, which you would put in a standard module, could look like this:

    Public Function ArrDepCities(City1 As String, City2 As String) As String
        If City1 < City2 Then
            ArrDepCities = City1 & &quot; - &quot; & City2
        Else
            ArrDepCities = City2 & &quot; - &quot; & City1
        End If
    End Function

Your query would create the following SQL statement:
SELECT ArrDepCities([Arrival City], [Departure City]) AS Cities, Sum([Amount]) AS Total FROM Table1 GROUP BY Cities ORDER BY Cities; Rick Sprague