Is it possible to have an iif statement in a query that will count or sum as it runs, then when the count/sum reaches a value then it will do what you'd like.
Basically, I want the iif statement to realize that once the number of hours reaches 8 on a particular day to stop counting. Then in the OT column i will have it start counting once the hours reach 8.
You can solve it with only one hour column per day and the result with a query
for example
select day, iif(sumofhours>8,8,sumofhours) as hrs, iif(sumofhours>8,sumofhours-8,0) as OT from
(
SELECT Sum(Table1.Hours) AS SumOfHours, Table1.Day
FROM Table1
GROUP BY Table1.Day
HAVING (((Sum(Table1.Hours))<=8)) OR (((Sum(Table1.Hours))>8)))
so my table is
day hours
mon 2
mon 5
tue 2
tue 6
...
I accomplished this with 3 queries and 1 function. First though, you didn't provide a table name, so replace all isntances of "YourTableNameHere" with the name of your actual table.
First set of code is the function:
Code:
Function get_Hours(in_NewHrs, in_OldHrs, in_Type) As Double
' gets how many new hours (in_NewHrs) get allocated to each in_Type (Reg or OT) based on how prior hours have been allocated (in_OldHrs)
ret = 0 ' return value, default will be 0
dbl_RegHrsRemain = 8 - in_OldHrs ' how many regular hours are needed to fill them up
If (dbl_RegHrsRemain < 0) Then dbl_RegHrsRemain = 0
' regular hours remaining can't be negative
If (dbl_RegHrsRemain <= 0) And (in_Type = "OT") Then ret = in_NewHrs
' no hours remain and want OT value--all go to OT
If (in_NewHrs <= dbl_RegHrsRemain) And (in_Type = "Reg") Then ret = in_NewHrs
' new hours less than remaining hours and want regular hours--all to new hours
If (dbl_RegHrsRemain <= 8) And (in_NewHrs > dbl_RegHrsRemain) Then
' part of new hours will go to Reg, part to OT-- assigns them based on in_Type requested
If (in_Type = "Reg") Then ret = dbl_RegHrsRemain
If (in_Type = "OT") Then ret = in_NewHrs - dbl_RegHrsRemain
End If
get_Hours = ret
End Function
Paste that into a module in your database and save. It takes the amount of new hours to add, the amount of hours that occured prior and then the type of hours you want (Reg or OT). It then computes where those hours will go.
Next we build the queries to generate the data to feed those functions. PAste this SQL into a query:
Code:
SELECT YourTableNameHere.Day, YourTableNameHere.Job, YourTableNameHere.TimeIn, DateDiff("h",[TimeIn],[TimeOut]) AS DayHours
FROM YourTableNameHere;
Name it "sub1". It simply calculates total time difference in hours between TimeIn, TimeOut for each record. Next past this SQL intoa new query:
Code:
SELECT sub1.Day, sub1.Job, sub1.DayHours, 1*Nz((SELECT SUM(DayHours) AS TotHours FROM sub1 AS A WHERE A.Day=sub1.Day AND A.TimeIn<sub1.TimeIn),0) AS PriorDayHours
FROM sub1;
Name that query "sub2". It's a sort of running total query, except it excludes the current records data. It determines how many hours have been allocated so far to where its going to allocate its hours. With taht figured out we can run the final query that will produce the results you want:
Code:
SELECT sub2.Day, sub2.Job, sub2.DayHours, sub2.PriorDayHours, get_Hours([DayHours],[PriorDayHours],"Reg") AS Hours_Reg, get_Hours([DayHours],[PriorDayHours],"OT") AS Hours_OT
FROM sub2;
Let me know if you have any questions or can't get it to work.