Mathematical Additions inside Queries

H

Heidi and Brenda

This is our SQL statement

SELECT Tbl_Personnel.Work_Order_Number, Tbl_Personnel.Last, Tbl_Personnel.[Pay Rate], Tbl_Personnel.[OT Hours], Tbl_Personnel.[Regular Hours], Tbl_Personnel.[Holiday Hours], Tbl_Personnel.Date, IIf(Tbl_Personnel![Pay Rate]>100,Tbl_Personnel![Pay Rate]*24/2080,Tbl_Personnel![Pay Rate]) AS True_Pay_Rate, ([True_Pay_Rate]*[Regular Hours]) AS Reg_Labor, ([True_Pay_Rate]*[OT Hours])*1.5 AS OT_Labor, ([True_Pay_Rate]*2)*[Holiday Hours] AS Holiday_Labor, [Reg_Labor]+[OT_Labor]+[Holiday_Labor] AS Total_Labor, ([Regular Hours]+[OT Hours]+[Holiday Hours]) AS Total_Hours, ([Total_Labor]*0.177) AS Fringes, [Total_Hours]*2.8 AS Insurance, [Total_Labor]*[tbl - Workers Comp Codes]!Rate AS Work_Comp, [Total_Labor]+[Fringes]+[Insurance]+[Work_Comp] AS Total_Cos
FROM Tbl_Personnel INNER JOIN [tbl - Workers Comp Codes] ON Tbl_Personnel.WorkersCompCodw = [tbl - Workers Comp Codes].Cod
WHERE (((Tbl_Personnel.Work_Order_Number) Like [Enter Work Order Number]))

It works through Holiday_Labor. None of the other calculations are working. Most are based on Total_Labor and Total_Hours which are mathematical additions. Why do these addition statements not work

[Reg_Labor]+[OT_Labor]+[Holiday_Labor] AS Total_Labor, ([Regular Hours]+[OT Hours]+[Holiday Hours]) AS Total_Hours

Any assistance will be more than appreciated.
 
R

Rick B

What data type are those fields? Are they numbers?

Rick B


This is our SQL statement:

SELECT Tbl_Personnel.Work_Order_Number, Tbl_Personnel.Last,
Tbl_Personnel.[Pay Rate], Tbl_Personnel.[OT Hours], Tbl_Personnel.[Regular
Hours], Tbl_Personnel.[Holiday Hours], Tbl_Personnel.Date,
IIf(Tbl_Personnel![Pay Rate]>100,Tbl_Personnel![Pay
Rate]*24/2080,Tbl_Personnel![Pay Rate]) AS True_Pay_Rate,
([True_Pay_Rate]*[Regular Hours]) AS Reg_Labor, ([True_Pay_Rate]*[OT
Hours])*1.5 AS OT_Labor, ([True_Pay_Rate]*2)*[Holiday Hours] AS
Holiday_Labor, [Reg_Labor]+[OT_Labor]+[Holiday_Labor] AS Total_Labor,
([Regular Hours]+[OT Hours]+[Holiday Hours]) AS Total_Hours,
([Total_Labor]*0.177) AS Fringes, [Total_Hours]*2.8 AS Insurance,
[Total_Labor]*[tbl - Workers Comp Codes]!Rate AS Work_Comp,
[Total_Labor]+[Fringes]+[Insurance]+[Work_Comp] AS Total_Cost
FROM Tbl_Personnel INNER JOIN [tbl - Workers Comp Codes] ON
Tbl_Personnel.WorkersCompCodw = [tbl - Workers Comp Codes].Code
WHERE (((Tbl_Personnel.Work_Order_Number) Like [Enter Work Order Number]));

It works through Holiday_Labor. None of the other calculations are working.
Most are based on Total_Labor and Total_Hours which are mathematical
additions. Why do these addition statements not work?

[Reg_Labor]+[OT_Labor]+[Holiday_Labor] AS Total_Labor, ([Regular Hours]+[OT
Hours]+[Holiday Hours]) AS Total_Hours,

Any assistance will be more than appreciated.
 
G

Gerald Stanley

Is there any liklihood that one or more of these columns
could be null? If so, you could use the Nz function to
deal with that e.g.
Nz([Reg_Labor],0)+Nz([OT_Labor],0)+Nz([Holiday_Labor],0) AS
Total_Labor

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
This is our SQL statement:

SELECT Tbl_Personnel.Work_Order_Number,
Click to expand...
Tbl_Personnel.Last, Tbl_Personnel.[Pay Rate],
Tbl_Personnel.[OT Hours], Tbl_Personnel.[Regular Hours],
Tbl_Personnel.[Holiday Hours], Tbl_Personnel.Date,
IIf(Tbl_Personnel![Pay Rate]>100,Tbl_Personnel![Pay
Rate]*24/2080,Tbl_Personnel![Pay Rate]) AS True_Pay_Rate,
([True_Pay_Rate]*[Regular Hours]) AS Reg_Labor,
([True_Pay_Rate]*[OT Hours])*1.5 AS OT_Labor,
([True_Pay_Rate]*2)*[Holiday Hours] AS Holiday_Labor,
[Reg_Labor]+[OT_Labor]+[Holiday_Labor] AS Total_Labor,
([Regular Hours]+[OT Hours]+[Holiday Hours]) AS
Total_Hours, ([Total_Labor]*0.177) AS Fringes,
[Total_Hours]*2.8 AS Insurance, [Total_Labor]*[tbl -
Workers Comp Codes]!Rate AS Work_Comp,
[Total_Labor]+[Fringes]+[Insurance]+[Work_Comp] AS Total_Cost
FROM Tbl_Personnel INNER JOIN [tbl - Workers Comp Codes]
Click to expand...
ON Tbl_Personnel.WorkersCompCodw = [tbl - Workers Comp
Codes].Code
WHERE (((Tbl_Personnel.Work_Order_Number) Like [Enter Work Order Number]));

It works through Holiday_Labor. None of the other
Click to expand...
calculations are working. Most are based on Total_Labor
and Total_Hours which are mathematical additions. Why do
these addition statements not work?
[Reg_Labor]+[OT_Labor]+[Holiday_Labor] AS Total_Labor,
Click to expand...
([Regular Hours]+[OT Hours]+[Holiday Hours]) AS Total_Hours,
 
Top