Getting Winners and Losers stats with two iif(<condition>) clauses in a single query

[EricRex]

Hey there,

I just discovered the sum(iff()) syntax and it's magic but I have many fields to return and wondering if there's a simpler syntax than my incredibly repetitious use of those iif conditions



Current very simplified query:
Select Pattern, Sum(iif(cPosSizeR > 0, 1, 0)) AS NumWin, Sum(iif(cPosSizeR < 0, 1, 0)) AS NumLoss, (+ many fields with Sum(iif(...)) or Count(iif(...)) to calculate %win/loss, stdev win/loss, etc.)

Is there a way to do a group iff like so:
Select Pattern,
iif(cPosSizeR > 0, Sum(cPosSizeR) AS NumWin, (+ other fields that need cPosSizeR > 0), 0)
iif(cPosSizeR < 0, Sum(cPosSizeR) AS NumLoss, (+ other fields that need cPosSizeR < 0), 0)
From StockTrades
Group By Pattern

Thanks!
Eric

[CJ_London]

instead of

Sum(iif(cPosSizeR > 0, 1, 0))

use

-sum(cPosSizeR > 0)

note the - sign

as for the rest of your query, not enough to make any sense but your comment

+ other fields that need cPosSizeR > 0

suggests might be able to use a union query

SELECT count(cPosSizeR) as winType, Count(thisfield) as countsomething, sum(anotherfield) as somethingelse FROM myTable WHERE cPosSizeR>0
UNION ALL SELECT count(cPosSizeR) as winType, Count(thisfield) as countsomething, sum(anotherfield) as somethingelse FROM myTable WHERE cPosSizeR<0

[June7]

To answer your question, no. Each aggregation calc would be an independent expression.

The UNION might seem to simplify calcs but not sure it would present data in useful arrangement. The first SELECT defines field names and data types.

SELECT Count(cPosSizeR) As winType, Count(thisfield) As countsomething, Sum(anotherfield) As somethingelse FROM myTable WHERE cPosSizeR>0
UNION ALL SELECT Count(cPosSizeR), Count(thisfield), Sum(anotherfield) FROM myTable WHERE cPosSizeR<0

And both records could show same value for winType so not sure what purpose this calc serves.

Maybe:
SELECT "Win" As WinLoss, Count(thisfield) As countsomething, Sum(anotherfield) As somethingelse FROM myTable WHERE cPosSizeR>0
UNION ALL SELECT "Loss", Count(thisfield), Sum(anotherfield) FROM myTable WHERE cPosSizeR<0

[EricRex]

instead of

Sum(iif(cPosSizeR > 0, 1, 0))

use

-sum(cPosSizeR > 0)

note the - sign

as for the rest of your query, not enough to make any sense

Hey CJ_London,

Trying to consolidate this monstrosity with 4 classes of iif. Classes are: Winners = cPosSizeR > 0; Losers = cPosSizeR < 0; Scratches are cPosSizeR = 0; Summary and Totals = <no iif>:

SELECT Sum(iif(cPosSizeR > 0, 1, 0)) AS NumWin, Round(Sum(iif(cPosSizeR > 0, 1, 0)) / Count(cPosSizeR),3) AS PctWin, Round(Sum(iif(cPosSizeR > 0, cPosSizeR, 0)), 2) AS CumRWin, Round(Sum(iif(cTradePL > 0, cTradePL, 0)), 2) AS CumDollarWin, Round(Avg(iif(cPosSizeR > 0, cPosSizeR, NULL)),2) AS AvgRWin, Round(Avg(iif(cTradePL > 0, cTradePL, NULL)),2) AS AvgDollarWin, Round(Max(iif(cPosSizeR > 0, cPosSizeR, 0)),2) AS MaxRWin, Round(Max(iif(cTradePL > 0, cTradePL, 0)),2) AS MaxDollarWin, Round(Min(iif(cPosSizeR > 0, cPosSizeR, 0)),2) AS MinRWin, Round(Min(iif(cTradePL > 0, cTradePL, 0)),2) AS MinDollarWin, Round(StDev(iif(cPosSizeR > 0, cPosSizeR, NULL)),2) AS StDevRWin, Round(StDev(iif(cTradePL > 0, cTradePL, NULL)),2) AS StDevDollarWin,

Sum(iif(cPosSizeR < 0, 1, NULL)) AS NumLoss, Round(Sum(iif(cPosSizeR < 0, 1, NULL)) / Count(cPosSizeR), 3) AS PctLoss, Round(Sum(iif(cPosSizeR< 0, cPosSizeR, 0)),2) AS CumRLoss, Round(Sum(iif(cTradePL< 0, cTradePL, 0)),2) AS CumDollarLoss, Round(Avg(iif(cPosSizeR< 0, cPosSizeR, NULL)),2) AS AvgRLoss, Round(Avg(iif(cTradePL< 0, cTradePL, NULL)),2) AS AvgDollarLoss, Round(Min(iif(cPosSizeR< 0, cPosSizeR, 0)),2) AS MaxRLoss, Round(Min(iif(cTradePL< 0, cTradePL, 0)),2) AS MaxDollarLoss, Round(Max(iif(cPosSizeR< 0, cPosSizeR, 0)),2) AS MinRLoss, Round(Max(iif(cTradePL< 0, cTradePL, 0)),2) AS MinDollarLoss, Round(StDev(iif(cPosSizeR< 0, cPosSizeR, NULL)),2) AS StDevRLoss, Round(StDev(iif(cPosSizeR< 0, cPosSizeR, NULL)),2) AS StDevDollarLoss,

Sum(iif(cPosSizeR = 0, 1, 0)) AS NumScratch, Round(Sum(iif(cPosSizeR = 0, 1, 0)) / Count(cPosSizeR), 3) AS PctScratch,

Count(cPosSizeR) AS NumTrades, Round(StDevP(cPosSizeR), 2) AS StDevRAll, Round(StDevP(cTradePL), 2) AS StDevDollarAll, Round(Sum (cPosSizeR), 2) AS CumRAll, Round(Sum (cTradePL), 2) AS CumDollarAll, Round(Avg(cPosSizeR), 2) AS Expectancy, Round(SQR(iif(Count(*)>100, 100, Count(*))) * (Avg(cPosSizeR) / StDevP(cPosSizeR)), 2) AS SQN

FROM StockTrades
WHERE TradeDate BETWEEN [startDate] AND [endDate]

Every UNION query I've put together like "Winners" Union "Losers" puts the winning and losing results into the same column which isn't at all useful for populating a form/report.

The magic of the Sum(iif(...)) syntax is that I can put separate criteria on each field, which gives me all of the records and fields I want in one query. It also allows me to replace NULL's with zeros for Sum, or keep them as NULLs for Count, Avg, and StDevP. There's a bit more to this because I also have a switch statement for a specific type of record set I'm looking for, and a Group By that uses that same SWITCH. An example of the SWITCH parameter is Weekday so I can see my trading results for each day of the week.

Hope that gives you more than enough detail to understand why I'd like to consolidate the iif()'s into a less cumbersome format.

Cheers,
Eric

[EricRex]

To answer your question, no. Each aggregation calc would be an independent expression.

The UNION might seem to simplify calcs but not sure it would present data in useful arrangement. The first SELECT defines field names and data types.

SELECT Count(cPosSizeR) As winType, Count(thisfield) As countsomething, Sum(anotherfield) As somethingelse FROM myTable WHERE cPosSizeR>0
UNION ALL SELECT Count(cPosSizeR), Count(thisfield), Sum(anotherfield) FROM myTable WHERE cPosSizeR<0

And both records could show same value for winType so not sure what purpose this calc serves.

Maybe:
SELECT "Win" As WinLoss, Count(thisfield) As countsomething, Sum(anotherfield) As somethingelse FROM myTable WHERE cPosSizeR>0
UNION ALL SELECT "Loss", Count(thisfield), Sum(anotherfield) FROM myTable WHERE cPosSizeR<0

Hey June7,

You very much understand my dilemma. The biggest problem with consolidating the criteria into the WHERE clause is that I am forced to have separate queries for Winners, Losers, Scratches, and Totals. I also have a query for the category labels such as Weekday. It also makes calculating a Win % or Loss Percent either impossible or too complicated for my n00B knowledge. This Sum(iif()) syntax makes those things trivial, although horribly clumsy. See my response to CJ_London for the more complete query.

Unions have never provided me a valuable output that I can use to populate a form/report because I can't give separate queries separate field names (AS NumWins and AS NumLosses), or I simply don't know how to do it.

The benefit of having separate iif() expression is that I can tune various expressions as needed. For example, SUM(iif(..., 1, 0)) allows me to count only fields that fit the criteria, whereas AVG(iif(..., <field>, 0) gives the wrong result because it adds a zero to the sum and one to the count. It needs to be AVG(iif(..., <field>, NULL) to give the right average. Same with StDevP and surprisingly, Min and Max. Those can't be zeros either.

Thanks for your feedback!
Eric

[June7]

Could you provide sample of data - I would like to do some testing.
Can build a table (or even copy/paste) in post or attach file.

[EricRex]

Could you provide sample of data - I would like to do some testing.
Can build a table (or even copy/paste) in post or attach file.

TradeID	TradeDate	Time	Symbol	EntryPrice	ActualShares	Direction	TrailStop	StopLoss	ExitPrice	VWAPValue	R10	ATR	Pattern	Notes	EntryOrder	ExitOrder	EODAction	cWeekday	cInitialStop	cVWAPDirection	cPosSizeTradeRisk	cPosSizeR	cIStopRiskReduction	cIStopTradeRisk	cIStopR	cTradePL	cVWAPR10Distance
2	11/29/2023	11:29	DIA	355.95	1	Long	0.38		355.64	355.13	0.38		K2PBF	Bot near the high of the day. Support and Resistance are real?	Market	STP		Wed	356.33	Long	0.38	-0.82	0.00	-0.38	0.82	($0.31)	2.16
3	12/4/2023	9:21	DIA	361.54	1	Long	0.37		361.34	361.85	0.37		SSCHW	Made sure I got the right account and right shares for this trade!	Market	STP	Hi	Mon	361.91	Short	0.37	-0.54	0.00	-0.37	0.54	($0.20)	-0.84
4	12/4/2023	9:51	DIA	361.76	1	Long	0.37		362.25	361.83	0.37		SSCHW	Made sure I got the right account and right shares for this trade!	Market	STP		Mon	362.13	Short	0.37	1.32	0.00	-0.37	-1.32	$0.49	-0.19
5	12/8/2023	10:50	DIA	362.46	1	Long	0.37		362.70	362.27	0.35		K2NFPB	"Late entry of a nice looking trend. Trailing stop place at -0.35. After a nice run, I pulled the stop way up to the dot below the live bar."	Market	STP	Drop	Fri	362.83	Long	0.37	0.65	0.00	-0.37	-0.65	$0.24	0.54
6	12/8/2023	11:42	DIA	362.55	1	Short	0.35		362.75	362.31	0.35		K2DPB	Tried to get out but accidently shorted an additional share! Got out eventually!	Market	MKT		Fri	362.9	Long	0.35	-0.57	0.00	0.35	-0.57	($0.20)	0.69
7	12/8/2023	12:10	DIA	362.86	1	Long	0.19		362.93	362.34	0.35		K2NFPB	"Re-entry on a nice trend. Trailing stop place at -.19 at the RL10 Swing Low. Had to leave, so moved it up to -.17 and got stopped out. Trend continued."	Market	TS		Fri	363.05	Long	0.19	0.37	0.00	-0.19	-0.37	$0.07	1.49
8	12/11/2023	8:18	DIA	363.87	1	Long	0.34		363.62	363.63	0.37		K2NFPB	"Bar-by-bar paper trade. Good re-entry trade after being stopped out. The RL10 swing low was only 0.09 away from the swing high which was much too tight. Used the normal R10 stop instead. The position moved up quickly and then reversed equally quickly,	Market	TS	Hi	Mon	364.21	Long	0.34	-0.74	0.00	-0.34	0.74	($0.25)	0.65
9	12/11/2023	8:33	DIA	364.02	1	Long	0.34		363.76	363.67	0.37		K2NFPB	"Bar-by-bar paper trade. Good re-entry trade after being stopped out. The RL10 swing low was only 0.09 away from the swing high which was much too tight. Used the normal R10 stop instead. The position moved up quickly and then reversed equally quickly,	Market	TS		Mon	364.36	Long	0.34	-0.76	0.00	-0.34	0.76	($0.26)	0.95
10	12/13/2023	8:31	DIA	366.53	1	Long	0.34		366.33	366.35	0.34		K2NFPB	Used the R10 value as the initial stop. Moved the stop up too aggressively and the trade took off. Used ToS Web UI without RL10 indicator for the first time.	Market	MKT	Pop	Wed	366.87	Long	0.34	-0.59	0.00	-0.34	0.59	($0.20)	0.53
11	12/13/2023	8:41	DIA	366.33	99	Short	0.34		366.62	366.35	0.34		MIST	99 shares MISTAKE! This was a pure mess up in the UI. I accidently sold 99 shares for no reason.Took a little while to figure out that I was short when the trend was up! Exited ASAP. Lost ~$28.	Market	MKT		Wed	366.67	Short	0.34	-0.85	0.00	0.34	-0.85	($28.71)	-0.06
12	12/14/2023	11:27	DIA	372.39	1	Short	0.35		371.85	372.60	0.35		K2NFPB	"When it went sideways, I changed the TS to a Stop and moved it aggressively. When I had to leave my desk, I changed it back to a tight trailing stop and made another 0.35 or so! Love trailing stops!"	Market	TS	Pop	Thu	372.74	Short	0.35	1.54	0.00	0.35	1.54	$0.54	-0.60
13	12/14/2023	13:00	DIA	372.78	1	Long	0.35		372.74	372.51	0.35		K2NFPB	"Slightly late on the entry because I was away from my desk, but a nice move up, so I went with it. The entry price was likely pretty similar to the entry bar. Exited on the PSAR flip, below where I hoped, but above my trailing stop."	Market	LMT		Thu	373.13	Long	0.35	-0.11	0.00	-0.35	0.11	($0.04)	0.77
14	12/15/2023	13:27	DIA	372.20	1	Long	0.34		372.94	372.09	0.34		DISC	"Purely discretionary trend-continuation. Entered at the market on a nice uptrend with a 100% MMRB trailing stop. Before the end of the day, it rocketed upward. Moved my trailing stop to -0.10 and got stopped out. Could have waited a bit, but it's ok!"	Market	TS	Pop	Fri	372.54	Long	0.34	2.18	0.00	-0.34	-2.18	$0.74	0.32
15	12/19/2023	8:12	DIA	374.58	1	Long	0.34		375.08	373.87	0.34		K2PBF	Put in the buy stop before the trend continued with trailing stop in place. Worked on this spreadsheet while I watched that lovely trend unfold. Exited when price dropped $.20 and the PSAR flipped.	Stop	MKT	Pop	Tue	374.92	Long	0.34	1.47	0.00	-0.34	-1.47	$0.50	2.09
16	12/19/2023	9:57	DIA	374.94	1	Short	0.34		374.90	374.34	0.34		K2DPB	Put in the sell stop at the market at the trend developed with with trailing stop in place. Exited on the PSAR flip instead of waiting for the trailing stop to kick in.	Stop	MKT		Tue	375.28	Long	0.34	0.12	0.00	0.34	0.12	$0.04	1.76
17	12/19/2023	10:10	DIA	374.68	1	Short	0.34		374.76	374.35	0.34		K2PBF	Put in the sell stop 0.02 below the low of a previous bar with trailing stop in place. surprised into the trade. Exited on the PSAR flip instead of waiting for the trailing stop to kick in.	Stop	MKT		Tue	375.02	Long	0.34	-0.24	0.00	0.34	-0.24	($0.08)	0.97

[June7]

Not seeing any simpler way to produce this output.

Recommend not using popup input prompts. Reference controls on form for parameters.

[EricRex]

Not seeing any simpler way to produce this output.

Recommend not using popup input prompts. Reference controls on form for parameters.

Not seeing any simpler way to produce this output.

Recommend not using popup input prompts. Reference controls on form for parameters.

This is the only complete query I've found so far that delivers all of the values. I have a set of forms that generate the input parameters based on Form Name that populates the Switch statement for Labels and Group By and a Filters subform that populates the rest. This will significantly simplify the code for the version that runs through 5 separate queries based on the WHERE cPosSizeR > 0 (or < 0, or = 0, or <none>) logic.

For completeness, the Parameters statement is: PARAMETERS [ResultsBy] Text ( 255 ), [startDate] DateTime, [endDate] DateTime;
Also, the Switch clause in the Select and Group By clause is:

SELECT Switch
(
[ResultsBy] = 'Direction', [Direction],
[ResultsBy] = 'Pattern', [Pattern],
[ResultsBy] = 'Weekday', Weekday([TradeDate]),
[ResultsBy] = 'EntryOrder', [EntryOrder],
[ResultsBy] = 'ExitOrder', [ExitOrder],
[ResultsBy] = 'Symbol', [Symbol],
[ResultsBy] = 'VWAPDirection', [cVWAPDirection],
[ResultsBy] = 'Summary', "Summary"
) AS Label1, Sum(iif(cPosSizeR > 0, 1, 0)) AS NumWin, blah, blah, blah.

One of the simpler forms is attached.

[EricRex]

Hey guys,

I made it even worse by making it better. Added Nz to all of the fields because there are conditions that have zero winners and N losers (or vice versa) and I want to see Zeros.

The no-holds-barred query is:

PARAMETERS [ResultsBy] Text ( 255 ), [startDate] DateTime, [endDate] DateTime;
SELECT Switch
(
[ResultsBy] = 'Direction', [Direction],
[ResultsBy] = 'Pattern', [Pattern],
[ResultsBy] = 'Weekday', Weekday([TradeDate]),
[ResultsBy] = 'EntryOrder', [EntryOrder],
[ResultsBy] = 'ExitOrder', [ExitOrder],
[ResultsBy] = 'Symbol', [Symbol],
[ResultsBy] = 'VWAPDirection', [cVWAPDirection],
[ResultsBy] = 'Summary', "Summary"
) AS Label1, Nz(Sum(iif(cPosSizeR > 0, 1, 0)), 0) AS NumWin, Nz(Round(Sum(iif(cPosSizeR > 0, 1, 0)) / Count(cPosSizeR) * 100, 1), 0) AS PctWin, Nz(Round(Sum(iif(cPosSizeR > 0, cPosSizeR, 0)), 2), 0) AS CumRWin, Nz(Round(Sum(iif(cTradePL > 0, cTradePL, 0)), 2), 0) AS CumDollarWin, Nz(Round(Avg(iif(cPosSizeR > 0, cPosSizeR, NULL)), 2), 0) AS AvgRWin, Nz(Round(Avg(iif(cTradePL > 0, cTradePL, NULL)), 2), 0) AS AvgDollarWin, Nz(Round(Max(iif(cPosSizeR > 0, cPosSizeR, NULL)), 2), 0) AS MaxRWin, Nz(Round(Max(iif(cTradePL > 0, cTradePL, 0)), 2), 0) AS MaxDollarWin, Nz(Round(Min(iif(cPosSizeR > 0, cPosSizeR, NULL)), 2), 0) AS MinRWin, Nz(Round(Min(iif(cTradePL > 0, cTradePL, NULL)), 2), 0) AS MinDollarWin, Nz(Round(StDev(iif(cPosSizeR > 0, cPosSizeR, NULL)), 2), 0) AS StDevRWin, Nz(Round(StDev(iif(cTradePL > 0, cTradePL, NULL)), 2), 0) AS StDevDollarWin, Nz(Sum(iif(cPosSizeR < 0, 1, NULL)), 0) AS NumLoss, Nz(Round(Sum(iif(cPosSizeR < 0, 1, NULL)) / Count(cPosSizeR) * 100, 1), 0) AS PctLoss, Nz(Round(Sum(iif(cPosSizeR< 0, cPosSizeR, 0)), 2), 0) AS CumRLoss, Nz(Round(Sum(iif(cTradePL < 0, cTradePL, 0)), 2), 0) AS CumDollarLoss, Nz(Round(Avg(iif(cPosSizeR< 0, cPosSizeR, NULL)), 2), 0) AS AvgRLoss, Nz(Round(Avg(iif(cTradePL < 0, cTradePL, NULL)), 2), 0) AS AvgDollarLoss, Nz(Round(Min(iif(cPosSizeR< 0, cPosSizeR, NULL)), 2), 0) AS MaxRLoss, Nz(Round(Min(iif(cTradePL < 0, cTradePL, NULL)), 2), 0) AS MaxDollarLoss, Nz(Round(Max(iif(cPosSizeR< 0, cPosSizeR, NULL)), 2), 0) AS MinRLoss, Nz(Round(Max(iif(cTradePL < 0, cTradePL, NULL)), 2), 0) AS MinDollarLoss, Nz(Round(StDev(iif(cPosSizeR< 0, cPosSizeR, NULL)), 2),0) AS StDevRLoss, Nz(Round(StDev(iif(cPosSizeR< 0, cPosSizeR, NULL)), 2), 0) AS StDevDollarLoss, Nz(Count(cPosSizeR), 0) AS NumTrades, Nz(Round(StDevP(cPosSizeR), 2), 0) AS StDevRAll, Nz(Round(StDevP(cTradePL), 2), 0) AS StDevDollarAll, Nz(Round(Sum (cPosSizeR), 2), 0) AS CumRAll, Nz(Round(Sum (cTradePL), 2), 0) AS CumDollarAll, Nz(Round(Avg(cPosSizeR), 2), 0) AS Expectancy, Round(iif(Nz(StDevP(cPosSizeR), 0) = 0, 0, (Avg(cPosSizeR) / StDevP(cPosSizeR))), 2) AS SQN, Sum(iif(cPosSizeR = 0, 1, 0)) AS NumScratch, Nz(Round(Sum(iif(cPosSizeR = 0, 1, 0)) / Count(cPosSizeR) * 100, 1), 0) AS PctScratch
FROM StockTrades
WHERE TradeDate BETWEEN [startDate] AND [endDate]
GROUP BY Switch
(
[ResultsBy] = 'Direction', [Direction],
[ResultsBy] = 'Pattern', [Pattern],
[ResultsBy] = 'Weekday', Weekday([TradeDate]),
[ResultsBy] = 'EntryOrder', [EntryOrder],
[ResultsBy] = 'ExitOrder', [ExitOrder],
[ResultsBy] = 'Symbol', [Symbol],
[ResultsBy] = 'VWAPDirection', [cVWAPDirection],
[ResultsBy] = 'Summary', "Summary"
);


In case you thought that was bad, there's a two-parameter version of the same where I want to see, say Trades by Direction and Pattern.
And, because I can't stop, there's a version of the query that retrieves 1/2 hour time intervals from a different table so I can see results by Time.
I have even more planned where I can see by Time Interval by Pattern...

Thanks for your help!
Eric

[CJ_London]

and I want to see Zeros.

no need to use the nz function if all you want to do is display nulls as zeros - use the format property (not function). You would still need it if the result is used in a subsequent calculation.

the format property for numbers has 4 parts - positive; negative; zero; null

so you might have

0.00;[red]0.00;0.00;0.00

which will display
1.23
-1.23
0.00
0.00

the property is available in the QBE and form/report design for the relevant field/control. Note that applying to the query will not transfer to a form or report

see this link
https://learn.microsoft.com/en-us/of...r.and.currency

[EricRex]

no need to use the nz function if all you want to do is display nulls as zeros - use the format property (not function). You would still need it if the result is used in a subsequent calculation.

the format property for numbers has 4 parts - positive; negative; zero; null

so you might have

0.00;[red]0.00;0.00;0.00

which will display
1.23
-1.23
0.00
0.00

the property is available in the QBE and form/report design for the relevant field/control. Note that applying to the query will not transfer to a form or report

see this link
https://learn.microsoft.com/en-us/of...r.and.currency

Hey CJ,

Didn't know about that property, thanks for the idea.

In this case, I'm looping through the query recordsets to populate the forms in VBA, and it's not happy with Nulls, although I suppose I could change the label format properties to fix that.

So many ways to do things!

I take it you didn't find any elegant way to accomplish the iif's in a single expression rather than treating each field separately?

Cheers,
Eric

[CJ_London]

Not sure what you mean by elegant. What matters more to me is a) generate the right result, b) performance and c) readability of the code - the latter important when working with others.

In sql, you do not need to state the false part of the iif statement. for example, both these will return null if false

iif(cTradePL > 0, cTradePL, NULL)

iif(cTradePL > 0, cTradePL)


And as stated before, if you want to return a 1 or 0, instead of

Sum(iif(cPosSizeR > 0, 1, 0))

use

-Sum(cPosSizeR > 0)


you probably don't need your round function since you don't appear to be adding data - as per my comment on the nz function, you can use the format property to display 2dp

removing those will probably save you 20-30% of calculations

Finally it looks to me like some of those calculations could be done in the form. For example

Round(iif(Nz(StDevP(cPosSizeR), 0) = 0, 0, (Avg(cPosSizeR) / StDevP(cPosSizeR))), 2) AS SQN

in your query your would return StDevP(cPosSizeR) AS StDPSR and Avg(cPosSizeR) AS AvgPSR (perhaps you already are, I haven't checked)

and in your SQN control

=iif(StDPSR=0,0 AvgPSR/StDPSR)

Not sure where you get stDevP from - perhaps a later version of Access or you have a public function, I only have stDev

[Micron]

It's a function you can use in 365 and 2016+ but I believe you can't choose it from the list of query functions in design view.

[EricRex]

Not sure what you mean by elegant. What matters more to me is a) generate the right result, b) performance and c) readability of the code - the latter important when working with others.

In sql, you do not need to state the false part of the iif statement. for example, both these will return null if false

iif(cTradePL > 0, cTradePL, NULL)

iif(cTradePL > 0, cTradePL)


And as stated before, if you want to return a 1 or 0, instead of

Sum(iif(cPosSizeR > 0, 1, 0))

use

-Sum(cPosSizeR > 0)


you probably don't need your round function since you don't appear to be adding data - as per my comment on the nz function, you can use the format property to display 2dp

removing those will probably save you 20-30% of calculations

Finally it looks to me like some of those calculations could be done in the form. For example

Round(iif(Nz(StDevP(cPosSizeR), 0) = 0, 0, (Avg(cPosSizeR) / StDevP(cPosSizeR))), 2) AS SQN

in your query your would return StDevP(cPosSizeR) AS StDPSR and Avg(cPosSizeR) AS AvgPSR (perhaps you already are, I haven't checked)

and in your SQN control

=iif(StDPSR=0,0 AvgPSR/StDPSR)

Not sure where you get stDevP from - perhaps a later version of Access or you have a public function, I only have stDev

Hey CJ,

Those are some awesome ideas for making it more elegant. My original definition of elegant was how to have 3 different iif(...) statements that covered each of the three cases, cPosSizeR > 0, cPosSizeR < 0, and cPosSizeR = 0. Apparently that can't be done.

Moving on, I tried your suggestion of removing the rounding from the query and putting it in the form. Everything I've tried so far doesn't work:
1. Those boxes on the Form sample were labels, so I changed them to TextBoxes. They are Unbound text boxes.
2. Set all of the formats to General Number, Decimal Places to 0, 1, or 2.
3. Set all of the formats to Fixed, Decimal Places to 0, 1, or 2.
4. Set some of the Formats to currency. That worked, but only some of the values are actual currencies.

Wondering if there's a trick to this that I don't know about.

Cheers,
Eric