Create a query to have the change % for two columns

[accessfever]

Hi,

I have a database which has product lines, product code, ship qty, sales price 1 and sales price 2. I tried to create a query to summarize the data at the product line's level to look at the change % between (sales price 1 * ship qty) and (sales price2 * ship qty) . The initial calculation of the change % column in the query is not good though. I want the change column to look at the final sum of price 1 * qty and price 2 * qty and the formula like this:
(((sum of price1 * shipqty) - (sum of price 2 * shipqty) )/ (sum of price2 * shipqty) ) * 100

Any idea?

 

[MarkK]

With a little algebra you can show that ...

(Q*P1)-(Q*P2)/Q*P2 = Q*(P1-P2)/Q*P2 = [B](P1-P2)/P2[/B]

... so quantity, in respect to the formula you posted, is irrelevant.

 

[accessfever]

you are right with the algebra. However, I used that formula but then I did not get the result I wanted. I want to have the change % after the sum of price1/2 * qty. see below for an example:

database
productLn productcd shipqty price1 price2
A D 5 $2 $2.5
A E 2 $4 $3
A F 4 $5 $10

query output
productLn shipqty sum(price1*qty) sum(price2*qty) change%
A 11 $38 $58.5 -35%

where shipqty = 5+2+4
where sum(price1*qty) = 10+ 8+20=$38
where sum(price2*qty) = 12.5+6+40=$58.5
desired change% = ($38 - $58.5) / $58.5

Any idea?

 

[accessfever]

nevermind. My original query was wrong so I got bad result. It works now.