M
Melinda
I had posted this question some time ago, but I am back at it, trying to get
it to work. I have an overtime database. The database collects overtime
data and it is catagorized by overtime codes, numbering 1 through 7,
depending on the situation with 1 being overtime worked, 2 being overtime
refused, etc., etc. What I need to do is one time a year, per our union
contract, is to be able to rollback the overtime taking the lowest number of
hours within that classification and subtracting it from each and every
employee above them. So in a sense it should "0" out the lowest employee
and reduce everyone elses balances.
I don't know if I wrote the database wrong, but when I go to roll back the
overtime, I get the negative in the hours worked column.
Ex. hours worked 42.2, hours refused 10.0, ytd hours 52.2. When I
subtract 52.2 hours, the results are hours worked -10.0, hours refused 10.0,
ytd hours 0. How can I get the results to be hours worked 0, hours refused
0, ytd hours 0. I always end up with a negative in my hours worked. I
need all overtime codes with a 2, 5 or 7 to return to a "0". I will post
what I have so far, but it really isn't doing exactly what I need it to do.
I think somehow I need to be setting the criteria in the overtime code.
INSERT INTO tblhours ( EmployeeName, TMSEmployeeNo, DateWorked,
OvertimeCodes, HoursWorked )
SELECT tblHours.EmployeeName, tblHours.TMSEmployeeNo, Date() AS X,
"Rollback" AS Y, -[Enter the number of hours to rollback] AS Z
FROM tblEmployees INNER JOIN tblHours ON tblEmployees.Tmsemployeeno =
tblHours.TMSEmployeeNo
GROUP BY tblHours.EmployeeName, tblHours.TMSEmployeeNo, "Rollback", -[Enter
the number of hours to rollback], tblEmployees.ClassNo, tblEmployees.OutPost
HAVING (((tblEmployees.ClassNo)="52133") AND ((tblEmployees.OutPost)>-1));
This is my first database that I have done and everything I read about
access advises me that I should not do the calculations in the tables, but it
seems as if I should have a year to date total or a running sum somewhere.
Any help would be greatly appreciated.
Melinda
it to work. I have an overtime database. The database collects overtime
data and it is catagorized by overtime codes, numbering 1 through 7,
depending on the situation with 1 being overtime worked, 2 being overtime
refused, etc., etc. What I need to do is one time a year, per our union
contract, is to be able to rollback the overtime taking the lowest number of
hours within that classification and subtracting it from each and every
employee above them. So in a sense it should "0" out the lowest employee
and reduce everyone elses balances.
I don't know if I wrote the database wrong, but when I go to roll back the
overtime, I get the negative in the hours worked column.
Ex. hours worked 42.2, hours refused 10.0, ytd hours 52.2. When I
subtract 52.2 hours, the results are hours worked -10.0, hours refused 10.0,
ytd hours 0. How can I get the results to be hours worked 0, hours refused
0, ytd hours 0. I always end up with a negative in my hours worked. I
need all overtime codes with a 2, 5 or 7 to return to a "0". I will post
what I have so far, but it really isn't doing exactly what I need it to do.
I think somehow I need to be setting the criteria in the overtime code.
INSERT INTO tblhours ( EmployeeName, TMSEmployeeNo, DateWorked,
OvertimeCodes, HoursWorked )
SELECT tblHours.EmployeeName, tblHours.TMSEmployeeNo, Date() AS X,
"Rollback" AS Y, -[Enter the number of hours to rollback] AS Z
FROM tblEmployees INNER JOIN tblHours ON tblEmployees.Tmsemployeeno =
tblHours.TMSEmployeeNo
GROUP BY tblHours.EmployeeName, tblHours.TMSEmployeeNo, "Rollback", -[Enter
the number of hours to rollback], tblEmployees.ClassNo, tblEmployees.OutPost
HAVING (((tblEmployees.ClassNo)="52133") AND ((tblEmployees.OutPost)>-1));
This is my first database that I have done and everything I read about
access advises me that I should not do the calculations in the tables, but it
seems as if I should have a year to date total or a running sum somewhere.
Any help would be greatly appreciated.
Melinda