Using a summed field to calculate another field (1 Viewer)

[Salwa]

I have the following query
SELECT COSTCENTRES.GROUP, Corp1.COSTCENTRE, COSTCENTRES.RESP_DESC, AbsenteeismTable.EMP_ID, AbsenteeismTable.NAME, Corp1.GRADE_DESC, Corp1.EMPLOYMENT, Sum(AbsenteeismTable.TAKEN) AS SumOfTAKEN
FROM (Corp1 INNER JOIN COSTCENTRES ON Corp1.COSTCENTRE = COSTCENTRES.COSTCENTRE) INNER JOIN AbsenteeismTable ON Corp1.EMP_ID = AbsenteeismTable.EMP_ID
WHERE (((Corp1.EMP_STATUS)="A1") AND ((AbsenteeismTable.TYPE)="A") AND ((AbsenteeismTable.FROM) Between #7/1/2021# And #6/30/2022#))
GROUP BY COSTCENTRES.GROUP, Corp1.COSTCENTRE, COSTCENTRES.RESP_DESC, AbsenteeismTable.EMP_ID, AbsenteeismTable.NAME, Corp1.GRADE_DESC, Corp1.EMPLOYMENT
HAVING (((Corp1.COSTCENTRE) Between "70000" And "89999"));

i want to add a field to the query which uses the Sum(AbsenteeismTable.TAKEN) AS SumOfTAKEN. balance = Sum(AbsenteeismTable.TAKEN) AS SumOfTAKEN + [AL_ENT_PRO]

is this possible in the same query?

 

[plog]

Perhaps, but probably not.

1. How many times have you or someone you know burned down their house by running SQL you weren't sure would work?

2. Where is AL_ENT_PRO coming from? Is it one of the data sources in the FROM? Does it need to be summed and then added?

3. Can you get to a fireproof location and try it? What happens? Error message? wrong data?

 

[Salwa]

is a field in AbsenteeismTable that i need to add to the Sum(AbsenteeismTable.TAKEN to end up with a balance.

 

[plog]

Maybe your question is not "is it possible?", but "how would I construct it?" In that case you would add this to a new field in that query:

Balance: Sum(AbsenteeismTable.TAKEN) + [AL_ENT_PRO]

Then underneath where it says 'Group By' you change it to Expression

 

[Salwa]

ok thanks