Web Development

Ask something
Question Locked

Update Queries/Make Table

0 helpful
8 replies

by mcrouse

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a overtime database in which the bulk of the data resides in the tblhours which tracks each and every occasion of overtime they have. Once a year, per our union contract, we have to rollback the overtime subtracting the lowest person's overtime from the each and every employee within their classification. I have tried this numerous ways and I am really struggling here. In my mind I should be able to bring the hoursworked and the class in the a query, change it to an update query subtracting the amount of hours, but it subtracts the total amount from each and every occasion of overtime, so I then in the update line typed =sum[hoursworked]-10.5 and that replaces all the hours in the table with a "0" Can anyone please offer some advice as to how I would achieve this.

8 replies

by tony hopkinson

Update hoursovertime set totalhours = totslhours - (Select Min(ot.totalhours) from hoursovertime ot where ot.class = hoursovertime.class)

This is hellish expensive though
another way might be to create a table to hold minimum hours by class

Update hoursovertime set totalhours = totslhours - (Select minimumhours from minimumHoursworked where minimumHoursworked.class = hoursovertime.class)

This way you aren't calculating the min for every record, just looking it up.

I wouldn't recommend this sort of query on a big system though.

by mcrouse

Thanks for the info Tony, but where would I type that information into?... a SQL? I am fairly new at all this (if you can't already tell). Would it work if I append a negative hour figure back into the main table if I did an append query?

by tony hopkinson

Not completely au fait, but there used to be a way of switching views on a query.
Designer, data or SQL View.
It's the last one you want.
In there you can type in raw SQL.
Presumably you've got used to the GUI designer, if you do sql view on the queries you understand you'll see the corresponding XML (XML ?, SQL is what I meant) and vice versa, it will show you what designer looks like for a query.

Couldn't tell you how to put it in teh designer itself, I learnt sql well before that came out and just type my queries in direct.

Best O' luck.

by bsmith523

1) If your update query was made correctly then your problem is the "=" sign in the update field. The "=" is used to compare 2 things. Basically you are comparing nothing (since there is nothing infront of the equal sign) to the result of your sum. This will always result in false which is a 0. Thats why you get 0s everywhere. Just try removing the "=" and see if it works.

2) If it doesn't I would set this up as 3 separate queries. #1 find the sum of overtimes for each employee. #2 find the minimum overtime for each category. #3 link the 2 tables above and subtract the result of #3 from each category in #2. You can make #3 as a make table. Of coarse you can take the time to convert this to one large query but why bother. You would just need to run #3 and #1 and #2 would automatically be run too.

I would advise against actually updating the total overtime hours incase someone questions the math and you need to show them the actual calculated times.

Good luck.

by mcrouse

No. 2 seems the route to go, but let me ask you this. I already have a query (two individual queries) and they sum the actual hours worked in one column and the refused hours in a second column with those two adding together for the total hours worked. If I could do a query from this paticular query it would be great. I will post this SQL for you.

SELECT henryqry.Tmsemployeeno, henryqry.EmployeeName, henryqry.ClassNo, henryqry.OutPost, henryqry.SumOfHoursWorked, refusedqry.SumOfHoursWorked, Sum((henryqry.sumofhoursworked)+nz(refusedqry.sumofhoursworked)) AS totalhours
FROM henryqry LEFT JOIN refusedqry ON henryqry.Tmsemployeeno = refusedqry.TMSEmployeeNo
GROUP BY henryqry.Tmsemployeeno, henryqry.EmployeeName, henryqry.ClassNo, henryqry.OutPost, henryqry.SumOfHoursWorked, refusedqry.SumOfHoursWorked
HAVING (((henryqry.ClassNo)<>"11111"));

I need to subtract the min, as you say from each and every person in that class and also "0" out the refused hours, but when do that in this manner it gives me a negative in the YTD hours. I hope I am making sense. Thanks for you help.

by bsmith523

Can you explain what data henryqry and refusedqry are calculating? Where is the overtime hours? Here is what you should have.

Query 1: Sum the total hours worked per employee. This will be the final hours that will then be reduced by the minimum overtime.

Query 2: A sum of all the overtime worked for each employee.

Query 3: Based on Query 2, find the minimum for each employee category.

Query 4: Join Query 1 to Query 3 at category. TotalHours (Query 1) - Minimum Overtime (Query 4)

What is refused time and why do you want to 0 it out? Why not just leave it in?
When you do this next year, just change the range of data being calculated to only include the new year and the refused time from the previous year will not be used. No data actually gets changed and you have backup data of the actual hours used in the calculations incase it gets questioned.

by mcrouse

Henryqry---That is the number of overtime hours calculated that are actually worked is sorted by an overtime code. The refusedqry is all the overtime hours that have been offered to you but are refused. All overtime hours (refused or worked) are fed from a data entry form back to tblhours are sorted by overtime code. I only calculate the total hours which equals worked hours + refused hours = total hours. When we roll back the total hours, the employees refused hours will all return to "0" and the calculation starts over for this year. Example. S. Evans 71.9 worked, 34.1 refused, total hours 106.0, next employee 103.5 worked, 7.3 refused, total 110.8. I need to append a record of 106.0 back into the table, that part works, but as son as I update the refused query and take the hours back to 0, the hours worked and total hours have a negative in them. Good suggestion on the refused time, let me go in a set the criteria by date. Thanks

This discussion is closed to new replies.