IIf Statements with running total

[monkeyman77]

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.

Example: If these are the results of my query

Day: Job: Hours: OT:
Mon 1 8 0
Tues 1 4 0
Tues 2 4 1
Tues 3 0 3
Wed 2 4 0
Wed 2 4 0

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.

Any help is much appreciated!!

-Steven

 

[monkeyman77]

My spacing in the above post didn't work so i have attached a picture.

 

[plog]

Is that the before or after data? And can you provide the other.

I need to see what you are starting with (data from your tables) and then what you hope to end with.

 

[boerbende]

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
...

 

[monkeyman77]

Plog, I am starting with table and want to create a query. I have attached an example of what the table would like look.


Boerbende, i'm not sure how that works. How does Mon receive two records?

 

[boerbende]

you had a table with 3 records on tuesday. So I guessed it were records of hours which were added to the table, probably with project ID's

 

[plog]

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:

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:

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:

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:

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.