Sum ??

[Shing]

hi,

I had a query that will gives me as follow :

INV# ITEM# QTY $COST $SALE/unit Profit
1001 7789 1 1.00 5.00 4.00
1001 6532 1 10.00 10.00 0.00

since each item will have a new record even they are in the same invoice,
what do I have to do to have the query add up the profit and show in a new
column as long as the record have the same invoice# ?

Thanks

 

[Michel Walsh]

Hi,




SELECT [Inv#], SUM( Qty*(cost-sale))
FROM myTAble
GROUP BY {inv#]



would give a summary by invoice. If you want a running sum:


SELECT a.inv, a.item, LAST(a.qty), LAST(a.cost), LAST(a.sale),
SUM(b.qty*(bsale-b.cost))
FROM myTable as a INNER JOIN myTable as b
a.inv=b.inv AND a.item>=b.item
GROUP BY a.inv, a.item



Note: I assumed there is no duplicated record with the SAME ( invoice
number AND item number )



Hoping it may help,
Vanderghast, Access MVP