Modify Sum in a query

[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.

 

[Team]

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?

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".

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.

 

[John Spencer]

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
..

 

[John Spencer]

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
..

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
.

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.

 

[Team]

Perfect. Thanks John.

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
.

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.