If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculation in Group query
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 |
#2
|
|||
|
|||
Calculation in Group query
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]; -- Duane Hookom MS Access MVP "Ellan" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
query wizard error | Christen | General Discussion | 12 | August 13th, 2004 08:37 PM |
Compound interest calculation in a query | Ray Gunderson | General Discussion | 2 | August 9th, 2004 02:57 AM |
Nested Query only returning lowest number | Nine Doors | Running & Setting Up Queries | 5 | July 22nd, 2004 05:56 PM |
SELECT function in Query | alexparks | Running & Setting Up Queries | 9 | July 5th, 2004 11:31 AM |
query field reference help | -dch | Running & Setting Up Queries | 4 | June 2nd, 2004 07:30 PM |