Query with Calculations

D

Dos Equis

Hi all,

I am trying to calculate pay for employees using a query. The Pay
query pulls calculated totals from 2 other queries and multiplies them
by the appropriate pay in a table. It works like this:

PayTable AreaTable qryNumberofHomes
qrySubs
PayType HomesInArea Carrier - GroupBy
Carrier - GroupBy
PayAmount SumOfHomes
SumOfSubs

QryPay
Carrier - GroupBy
HomePay: CCur(qryNumberofHomes!SumOfHomes*PayTable!PayAmount)
SubPay: CCur(qrySubs!SumOfSubs*PayTable!PayAmount)
Pay: HomePay+SubPay

I should have a QryPay that resembles:

Joe $5.00 $3.00 $8.00

Instead I either get an error stating that I am not using an aggregate
expresion or an error stating that the field is too small for the data
and try reducing the data. I am working with 6 employees and with a
CCur function if I understand properly, it's reduced to $???.??. None
of them are making more than $400.00 and week and most are under $70.
Please help.

Byron
 
S

Smartin

Dos said:
Hi all,

I am trying to calculate pay for employees using a query. The Pay
query pulls calculated totals from 2 other queries and multiplies them
by the appropriate pay in a table. It works like this:

PayTable AreaTable qryNumberofHomes
qrySubs
PayType HomesInArea Carrier - GroupBy
Carrier - GroupBy
PayAmount SumOfHomes
SumOfSubs

QryPay
Carrier - GroupBy
HomePay: CCur(qryNumberofHomes!SumOfHomes*PayTable!PayAmount)
SubPay: CCur(qrySubs!SumOfSubs*PayTable!PayAmount)
Pay: HomePay+SubPay

I should have a QryPay that resembles:

Joe $5.00 $3.00 $8.00

Instead I either get an error stating that I am not using an aggregate
expresion or an error stating that the field is too small for the data
and try reducing the data. I am working with 6 employees and with a
CCur function if I understand properly, it's reduced to $???.??. None
of them are making more than $400.00 and week and most are under $70.
Please help.

Byron
Click to expand...

Hi Byron,

You can't use an expression alias in another expression (pity, though!).
The way you are doing it I think results in some Boolean weirdness in
the Pay: expression. You could try

QryPay
Carrier - GroupBy
HomePay: CCur(qryNumberofHomes!SumOfHomes*PayTable!PayAmount)
SubPay: CCur(qrySubs!SumOfSubs*PayTable!PayAmount)
Pay: CCur(qryNumberofHomes!SumOfHomes*PayTable!PayAmount) +
CCur(qrySubs!SumOfSubs*PayTable!PayAmount)
 
D

Dos Equis

I've looked at the query I'm trying to build and I get an error
stating the field is too small for the data I am trying to put in it.
I've tried each field I would want to be part of a calculation, just
names and then just moving each field from the selected tables/queries
to feilds of the query... nothing is small enough. Any thoughts?

Byron
 
Top