Query to add rows with a criteria

[subs]

ocity dcity total price

A B 1222
C D 1345
E F 1000
B A 10
D C 14



i have a table like the one above. i would like to know the query
which can add the total price for rows with ocity and dcity reversed.
F or example i need a query which can get the following result

ocity dcity sumoftotal price
A B 1232
C D 1359
E F 1000


i.e A,B and B,A rows are added - as well C,D and E,f are added
because they exchange their places in ocity and dcity.

Thanks for any assistance

 

[David]

Try self joining the table and adding the price column from each side of the
join.

SELECT tblTest.ocity,
tblTest.dcity,
tblTest.price,
tblTest_1.price,
NZ([tblTest].[price],0)+NZ([tblTest_1].[price],0) AS CalcTotal
FROM tblTest
LEFT JOIN tblTest
AS tblTest_1

 

[kc-mass]

Create a new query.

Set an expression in the query to equal "Combo: IIF(Ocity< Dcity,Ocity &
Dcity,Dcity & Ocity)". Include price in the query.

Reference that query from a second query; select totals from View.
Group on the Combo field and total the price.

Regards

Kevin

 

[subs]

Try self joining the table and adding the price column from each side of the
join.

SELECT  tblTest.ocity,
        tblTest.dcity,
        tblTest.price,
        tblTest_1.price,
        NZ([tblTest].[price],0)+NZ([tblTest_1].[price],0) AS CalcTotal
FROM tblTest
LEFT JOIN tblTest
AS tblTest_1

ocity    dcity     total price

A          B            1222
C          D            1345
E          F             1000
B          A               10
D           C            14

 i have a table like the one above. i would like to know  the query
which can add  the total price for rows with ocity and dcity reversed..
F or example i need a query which can get the following result

ocity dcity    sumoftotal price
A       B         1232
C       D         1359
E      F           1000

i.e  A,B and B,A rows are added - as well C,D and E,f are added
because they exchange their places in ocity and dcity.

Thanks for any assistance- Hide quoted text -

- Show quoted text -

your query does not work if the data is like this

ocity dcity price
frankfort amelia 1211
p r 1390
o i 3450
amelia frankfort 12
frankfort o 10

i am getting the output like this

ocity dcity tblTest.price tblTest_1.price CalcTotal
frankfort amelia 1211 12 1223
p r 1390 1390
o i 3450 10 3460
amelia frankfort 12 1211 1223
frankfort o 10 12 22

o,i are different from frankfort ,o combination. The ocity and dcity
combinations must be exactly same in letters but reverse in two
different rows

 

[Sam Davis]

I think this should do it....

SELECT IIf([ocity]>[dcity],[dcity] & [ocity],[ocity] & [dcity]) AS tmpCity,
Sum(TotalPrice) AS SumOfTotalPrice
FROM YourTableOrQueryName
GROUP BY IIf([ocity]>[dcity],[dcity] & [ocity],[ocity] & [dcity])

Regards, Sam