Modify Sum in a query

  • Thread starter Thread starter Team
  • Start date Start date
T

Team

Hello,
I have a table in my database called OpenTrades. In this table, there
are 2 fields I am concerned with, Shares and TransCode.

I want to have a query add or subtract all the open trades for a
particular CUSIP (Found in the OpenTrades Query column CUSIP) that is
in a certain Account (in table in column called AccNum). Currently, I
have code for a query to do this, however it only adds the trades. I
want to have it look to see what to do, as follows:

If TransCode is B, I want it to subtract, and if TransCode is SEL, I
want it to add.

Here is what I have so far.

SELECT AccNum, CUSIP, Sum([Shares]) AS OTShares
FROM [Open Trades]
GROUP BY AccNum, CUSIP;

Does anyone know how to modify this script to get the process to work
correctly?
Thanks.
 
Thanks Karl. TransCode could have several B's and several SEL's for
each CUSIP in each AccNum. Can I change this code to take the net of
the B's and SEL's?


KARL said:
Try this ---
SELECT AccNum, CUSIP, Sum(IIF([TransCode] = "B", -[Shares], +[Shares])) AS
OTShares
FROM [Open Trades]
GROUP BY AccNum, CUSIP;

This assumes that [TransCode] will only be "B" or "SEL".

Team said:
Hello,
I have a table in my database called OpenTrades. In this table, there
are 2 fields I am concerned with, Shares and TransCode.

I want to have a query add or subtract all the open trades for a
particular CUSIP (Found in the OpenTrades Query column CUSIP) that is
in a certain Account (in table in column called AccNum). Currently, I
have code for a query to do this, however it only adds the trades. I
want to have it look to see what to do, as follows:

If TransCode is B, I want it to subtract, and if TransCode is SEL, I
want it to add.

Here is what I have so far.

SELECT AccNum, CUSIP, Sum([Shares]) AS OTShares
FROM [Open Trades]
GROUP BY AccNum, CUSIP;

Does anyone know how to modify this script to get the process to work
correctly?
Thanks.
Click to expand...
Click to expand...
 
Perhaps something as simple as the following will work for you

SELECT AccNum, CUSIP
, Sum(IIF(Transcode='B',[Shares],-[Shares])) AS OTShares
FROM [Open Trades]
GROUP BY AccNum, CUSIP;

If you want to be really careful you could nest two IIF statements

Sum(IIF(Transcode='B',[Shares],IIF(TransCode='Sel',-[Shares], Null))) AS
OTShares

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Whoops, I misread the request.and put the minus in the wrong place

Sum(IIF(Transcode='B',-[Shares],[Shares])) AS OTShares

If you want shares bought and shares sold as separate fields

Sum(IIF(Transcode='B',[Shares],Null) as SharesB
Sum(IIF(Transcode='Sel',[Shares],Null) as SharesSel
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John Spencer said:
Perhaps something as simple as the following will work for you

SELECT AccNum, CUSIP
, Sum(IIF(Transcode='B',[Shares],-[Shares])) AS OTShares
FROM [Open Trades]
GROUP BY AccNum, CUSIP;

If you want to be really careful you could nest two IIF statements

Sum(IIF(Transcode='B',[Shares],IIF(TransCode='Sel',-[Shares], Null))) AS
OTShares

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Team said:
Hello,
I have a table in my database called OpenTrades. In this table, there
are 2 fields I am concerned with, Shares and TransCode.

I want to have a query add or subtract all the open trades for a
particular CUSIP (Found in the OpenTrades Query column CUSIP) that is
in a certain Account (in table in column called AccNum). Currently, I
have code for a query to do this, however it only adds the trades. I
want to have it look to see what to do, as follows:

If TransCode is B, I want it to subtract, and if TransCode is SEL, I
want it to add.

Here is what I have so far.

SELECT AccNum, CUSIP, Sum([Shares]) AS OTShares
FROM [Open Trades]
GROUP BY AccNum, CUSIP;

Does anyone know how to modify this script to get the process to work
correctly?
Thanks.
Click to expand...
Click to expand...
 
Perfect. Thanks John.


John said:
Perhaps something as simple as the following will work for you

SELECT AccNum, CUSIP
, Sum(IIF(Transcode='B',[Shares],-[Shares])) AS OTShares
FROM [Open Trades]
GROUP BY AccNum, CUSIP;

If you want to be really careful you could nest two IIF statements

Sum(IIF(Transcode='B',[Shares],IIF(TransCode='Sel',-[Shares], Null))) AS
OTShares

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Team said:
Hello,
I have a table in my database called OpenTrades. In this table, there
are 2 fields I am concerned with, Shares and TransCode.

I want to have a query add or subtract all the open trades for a
particular CUSIP (Found in the OpenTrades Query column CUSIP) that is
in a certain Account (in table in column called AccNum). Currently, I
have code for a query to do this, however it only adds the trades. I
want to have it look to see what to do, as follows:

If TransCode is B, I want it to subtract, and if TransCode is SEL, I
want it to add.

Here is what I have so far.

SELECT AccNum, CUSIP, Sum([Shares]) AS OTShares
FROM [Open Trades]
GROUP BY AccNum, CUSIP;

Does anyone know how to modify this script to get the process to work
correctly?
Thanks.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum in Query problem/question 15
...cannot use a calculated field in a Where clause... 2
Unlikely error msg from query 2
Simple iif question 2
sum if column has similar values (cusip) 3
Delete duplicate cells in a row based on the best price in anothercell from another row 1
SQL Grouping Syntax 5
'Query too complex' need help with Joins 1

Back
Top