Using A Calculated Field to Perform Another Calculation (1 Viewer)

robsmith

New member
Local time
Today, 08:18
Joined
Feb 17, 2020
Messages
26
Hi,

I have put together the query below to perform various calculations. It works ok but I am trying to now calculate the average of the BatAve results, ie the average of all BatAves in League.

I thought
Code:
AVG([BatAve]) AS LgBatAve
would work but I just get a "Subqueries cannot be used in the expression" error.

Can anyone see what the issue is?

Thanks

Code:
SELECT Data.venue, Data.League, Round(Sum(runs)/Sum(IIf([howDismissed]<>"not out",1,0)),2) AS BatAve, Sum(ballsFaced) AS balls, Round(Sum(runs)/Sum(ballsFaced)*100,2) AS SR, Round((Sum(Batting.[_4s])+Sum(Batting.[_6s]))/Sum(ballsFaced)*100,2) AS BPercent
FROM Data INNER JOIN Batting ON Data.matchId = Batting.matchId
GROUP BY Data.venue, Data.League;
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:18
Joined
May 7, 2009
Messages
19,094
why this expression:

Round(Sum(runs)/Sum(IIf([howDismissed]<>"not out",1,0)

you might get Division by Zero error msg.

EDIT:

you may try to put on Separate Calculated Columns each Expressions, eg (Query1)

... Sum(runs) As sumRuns, Sum(IIf([hosDismissed]<>"not out", 1, Null)) As SumNotDismissed, ....

then create another Query to Calculate:

select Round(Query1.sumRuns / Query1.SumNotDismissed, 2) As Expr1, ...
 
Last edited:

robsmith

New member
Local time
Today, 08:18
Joined
Feb 17, 2020
Messages
26
Hi arnelgp,

To calculate BatAve I need to sum all the runs then divide by the number of times batsmen were "out". There are lots of different ways of getting out so the easiest way was to say anything other than "not out".

Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:18
Joined
Feb 19, 2002
Messages
42,872
I believe the SQL Server variant of SQL allows you to use a calculated field in another calculation but I wouldn't swear to it. However, Access SQL does not. If you want to use calculated values in other calculations rather than repeating one calculation inside another, create a final query that takes the first one as input and then performs additional calculations.
 

Users who are viewing this thread

Top Bottom