Calculation in Group query

[Ellan]

Hi,

I have the following table called Transaction Table (it
does have other fields but these are the only ones I
believe I need). I have done a query to give me the total
sold and total bought but I can't seem to get it to then
take the 'sell' sum away from the 'buy' sum to give me a
total shares figure.

The Buy and Sell value comes from a 'Transaction Lookup'
table



Transaction Type No. of Shares

Buy 52
Sell 63
Buy 51
Buy 200
Buy 600
Sell 200

The Buy and Sell value comes from a 'Transaction Lookup'
table:

Transaction Type
Buy
Sell

I have used the Transaction Lookup table to group the buy
and sell and then used no. of shares field from
Transaction Table in my query but then how do I get it do
a simple buy - sell calculation.

Any help would be appreciated.

Many thanks

 

[Duane Hookom]

SELECT Sum(Abs([TransType]="Buy") * [NumShares]) As SumBuy,
Sum(Abs([TransType]="Sell") * [NumShares]) As SumSell,
Sum(Abs([TransType]="Buy") * [NumShares]) -Sum(Abs([TransType]="Sell") *
[NumShares]) As SumNet
FROM [tblTransaction];