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

#### robsmith

##### New member
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!
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
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
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.

• robsmith

#### robsmith

##### New member
Thanks Pat, I’ll try that.

Replies
10
Views
458
Replies
5
Views
342
Replies
4
Views
424
Replies
8
Views
632
Replies
19
Views
359