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

Salwa

New member
Local time
Tomorrow, 09:25
Joined
Oct 10, 2023
Messages
12
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

Banishment Pending
Local time
Today, 16:25
Joined
May 11, 2011
Messages
11,587
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

New member
Local time
Tomorrow, 09:25
Joined
Oct 10, 2023
Messages
12
is a field in AbsenteeismTable that i need to add to the Sum(AbsenteeismTable.TAKEN to end up with a balance.
 

plog

Banishment Pending
Local time
Today, 16:25
Joined
May 11, 2011
Messages
11,587
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
 

Users who are viewing this thread

Top Bottom