append queries

[mkc9929]

I have a database in which I calculate overtime hours earned and refused. I have tblhours which adds all the hours up which were worked refering to an overtime code. The same column in tblhours when refering to a different overtime code, ie: "2, 5, or 7" adds up all the overtime hours refused. Everything works great, but once a year per our union contract I need to roll back overtime taking the lowest employees total (hours worked + hours refused) and subtract them from each and everyone's overtime. The problem I am having is the structure of my table I have all the hours reported in one column and I query on different overtime codes sorting out the data I need. Say....an employees hours worked are 22.2, hours refused are 2.2 and ytd hours are 24.4 and is indeed the lowest number of hours, so I need to subtract his hours from each employee, when I do an append query and subtract 24.4 hours from him, the results I get are -2.2 hours worked, 2.2 refused hours, and 0 ytd. The ytd date is 0 and that is what I want, but how do I subtract those hours and and get 0 worked, 0 refused, and 0 ytd? I will post an append query that someone else was helping me with, but it doesn't quite work. Do I have the structure of my table set up wrong or possibly should I be storing a running total somewhere? Any help would be greatly appreciated.

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));

Thanks,
Melinda

 

[lespaul]

Do I have the structure of my table set up wrong

I don't know the structure of the table in order to answer this...if you want to describe it (field names) and show some sample data and maybe what you want the final results to be, I'm sure someone will be able to help...

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[mkc9929]

Structure of tblhours is:
employeename, tmsemployeeno (PK), PGAC, reasonworked,payperiodending, dateworked, overtimecodes, hoursworked, hoursbanked, comments.

I will post the one append query that somewhat works but does not "0" out the refused hours worked. I need to roll back the overtime per our union contract, and I also need to "0" out the hours with overtime codes of 2, 5 or 7. The query shown below works well to rollback the hours but what it does not do is if an employees hours are 22.2 (hours worked) 2.2 (refused hours) total 24.4, when I roll back 24.4 hours, the results are -2.2 (hours worked), 2.2 (refused horus) total "0". I was trying to append the negative hours back into the table.

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)="16512") AND ((tblEmployees.OutPost)>-1));

I know I can be quite confusing and I apologize--so shoot back any questions you have.

Thanks Melinda