Queries?

[Melinda]

Is it possible to make an "update query" to roll back year to date overtime
hours? I have an tblhours that totals all overtime hours worked and refused
in which I have ran queries to sum all of those. Once a year we have to
rollback the year to date overtime hours which means we take the lowest
employee, he goes to "0" and we subtract the lowest employees hours from the
highest employees hours. I was trying to do this in an update query, but
everything I try subtracts, for example, 10 hours from every line of
overtime in the tblhours table. I know you are not suppose to do
calculations in the table, but I'm not sure how to subract the amount from
the totals. Any help would be appreciated.

 

[KARL DEWEY]

Your table would not have a record that is the total to subtract from. So add
a record that has a negative value equal to that of the lowest employee.

 

[Melinda]

I am not sure I understand what you are saying. Do I need to add an
additional column in the table? I am not certain where to show the
negative amount. Wouldn't the negative take off of each record? Sorry for
not understanding.

 

[KARL DEWEY]

Add a negative record for each individual.

 

[Melinda]

I have to roll back 400+ employees overtime and adding a record for each
individual would be a little cumbersome, but if that is the only way then I
will have to. Will an update query not work for this situation since the
hours are totaled in a query? I made a seperate table and the update
query works fine for that paticular table, but I don't have any relationships
set up for that table and it is a table that only reflects year to date
totals.

 

[KARL DEWEY]

I assumed that you were recording hours on a weekly basis - one record per
employee per week. How are you recording hour and overtime? Wheredo you
expect to rollback the quanity?

 

[Melinda]

We plow snow for the State of Ohio and I am recording overtime everytime they
are called in for any type of overtime. I am recording each and every
occurance. So if we have a snow storm--it will be on a daily basis. Where
I roll back the overtime?---I am not sure. This paticular time I did the
negative hours in the form, just as you said, but it was cumbersome and
everyone is equal by class, etc., etc. I am thinking possibly I need to
create a seperate table with just year to date hours, but everything that I
have learned in Access advises you not to calculate in the tables and that is
exactly what I would be doing. I was messing with an update query and it
works in a standalone table, but I don't have that table related to anything
at this point, so I have just entered by own fake numbers.

Thanks for all your helpl

 

[KARL DEWEY]

What is your table structure. Post sample data that you store.

 

[Melinda]

Employee Name.......Text
TMSEmployeeNo......Text
PGAC.......Number
ReasonWorked...........Text
PayPeriodEnding...........Text
DateWorked...............Date/Time
OvertimeCodes.............Text
HousWorked.................Number
HoursBanked..............Number
Comments...............Memo

This is the table I record all the overtime in and I total it by the
different overtime codes and so forth. I would like to update this table,
but I think possibly the only way to do it, is to do as you say and put in a
negative recrod....but it could get quite cumbersome.

Thanks

 

[KARL DEWEY]

Backup your database and try this after you determine how many hours to
rollback. Change the table name from Melinda to the actual name. Use any
additional fields and codes as necessary.

INSERT INTO Melinda ( [Employee Name], TMSEmployeeNo, DateWorked,
OvertimeCodes, HoursWorked )
SELECT Melinda.[Employee Name], Melinda.TMSEmployeeNo, Date() AS X,
"Rollback" AS Y, -[Enter the number of hours to rollback] AS Z
FROM Melinda
GROUP BY Melinda.[Employee Name], Melinda.TMSEmployeeNo, "Rollback", -[Enter
the number of hours to rollback];