Query Expression using sum of another calculated field in query

[Kutach770]

Hello. I've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:

Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)

100,000 1,000 Sum of total bonuses/salary*4%
90,000 900

How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?

 

[CJ_London]

it would help if you posted your actual query - I can't make sense of your example

 

[Kutach770]

Wow - thanks for responding so soon. Unfortunately, I'm using the design grid to build my query rather than code.

EMPLOYEE and SALARY data come from Table 1. A query uses an expression to compute the bonus amounts, so here is the query's output:

EMPLOYEE SALARY BONUS ADJUSTMENTS
Albert 150,000 1,437 ?
Arielle 34,400 329 ?
Elianna 34,440 330
Aimee 123,340 1,182
Trudy 33,400 320

I need the query to compute total bonuses (3,598) and use that total for another expression that will adjust the bonuses proportionately.

Is there a function that will tally the sum on the calculated amounts in the BONUS column that can be incorporated into an expression in the ADJUSTMENTS column.

 

[Kutach770]

My columns get squished together when I post my question. Assume there are 4: EMPLOYEE, SALARY, BONUS, and ADJUSTMENTS. Data in the EMPLOYEE and SALARY columns come from a table. The query calculates the BONUS amounts. I need to get the sum total of the computed BONUSES to use in an expression in the ADJUSTMENTS column

 

[CJ_London]

I'm using the design grid to build my query rather than code.

the design grid is just a GUI to create the code, click on the SQL button (top left of design grid) to see the resultant code. Then copy and paste

My columns get squished together when I post my question

you need to use the advanced editor and surround the text with the code tags (highlight text and click the code button - looks like #)

 

[Kutach770]

I guess Im asking why cant I make the expression [BONUS]/Sum([BONUS])*[Expression A) without getting some weird error message? I think that formula would solve my problem

 

[Kutach770]

Sorry, I didnt see your response. Here's the code:

SELECT Data.Employee, Data.Salary, [Data]![Salary]/[Total Salary]![SumOfSalary] AS [Comp%], [Comp%]*[Constants]![Bonus Pool] AS Bonus, IIf([Bonus]<[Constants]![FloorEX],[Constants]![FloorEX],0) AS MinCheck, IIf([MinCheck]=0,0,+[Bonus]-[MinCheck]) AS Reallocate1, IIf([MinCheck]=0,[Bonus],0) AS Expr1, [Expr1]/Sum([Expr1])*Sum([Reallocate1]) AS Expr2
FROM Data, [Total Salary], Constants;

Not sure how you determine which is which field, but the formula I'm trying to use is Expr2: [Expr1]/Sum([Expr1])*Sum([Reallocate1])

When I run the thing, I get "You tried to execute a query that does not include the specified expression 'Employee' as part of an aggregate function."

 

[Kutach770]

Seems to have to do with putting other fields in a group by clause.

 

[Kutach770]

So close. I simplified the code to hone in on what I want to do. It's ignoring the "Sum" and just dividing Expr1 by Expr1

SELECT Data.Employee, Data.Salary, [Data]![Salary]*0.01 AS Expr1, [Expr1]/Sum([expr1]) AS expr2
FROM Data
GROUP BY Data.Employee, Data.Salary;

 

[Kutach770]

Sorry for all these posts. I'll be the problem is the "group by" employee. It's calculating the sum of Expr1 by employee, rather than all the employees as a single group. The group by got rid of the error message, but I need to figure out how to tell Access that all employees are a single group.

 

[Kutach770]

I THINK I GOT IT!!!

SELECT Data.Employee, Data.Salary, (SELECT Sum(Data.Salary) AS SumOfSalary
FROM Data) AS Total, [Salary]/[Total] AS Expr1
FROM Data;

 

[Kutach770]

Ugh. I get a circular reference error after I close and reopen the query

 

[CJ_London]

you need to alias the table in the subquery, try

SELECT Employee, Salary, (SELECT Sum(Salary) FROM Data[COLOR=red] AS D[/COLOR]) AS Total, [Salary]/[Total] AS Expr1
FROM Data;

 

[Kutach770]

Sorry I hasn't responded sooner. It worked! ! Thank you so much for help.