Query to add rows with a criteria

  • Thread starter Thread starter subs
  • Start date Start date
S

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
 
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
 
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
 
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
Click to expand...
A          B            1222
C          D            1345
E          F             1000
B          A               10
D           C            14
Click to expand...
 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
Click to expand...
ocity dcity    sumoftotal price
A       B         1232
C       D         1359
E      F           1000
Click to expand...
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.
Click to expand...
Thanks for any assistance- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
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
 
Back
Top