Mathematical Additions inside Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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,
 
Back
Top