Looping through records

[MarionD]

Hi All,

I am hoping someone can give me a bit of advice. I'm not sure how to do this:
Basic Info:
The application id for staff hours - the School has set times 08:00 till 17:00
If the Staff work outside of these hours they are awarded overtime
If they work after 21:00 (Parents evening) they get an extra bonus per Hour.

I have a table where the staff enter their hours. e.g.
Staff Id Date From Till
001 01/02/2016 08:00 17:00
001 01/02/2016 18:00 23:00

Now at the end of each month I would like to run a module/code that will
go through each record and add a record to another table/s noting on 01/02/2016 overtime of 4 Hours and bonus for 2 Hours for Staff member 001.

BUT hours like from 09:30 till 18:00 could also be entered. (1 hour overtime even though hours not exceeded but no bonus)

or 06:30 till 12:00 (1/2 hour overtime)

Hope this makes sense to someone and that someone...anyone can offer a bit of advice or ideas how best to achieve this.
Thanks
Marion

 

[MarkK]

It doesn't make sense to me to enumerate your data and save incremental calculations. If you write a tool to do this, it is a risk, since you will forget you ran this tool and create dupes, or you will forget to run this tool.

Typically in a database, record the facts that describe the events as they occur. Period. Done.

Then, if you develop a tool that does calculations, use that tool as required to do those calculations ON THE RAW DATA, and ON DEMAND ONLY, but don't save those calculations.

This is the difference between storing someone's birthdate, and storing someone's age. One is raw data, one is a calculation. Don't store the calculation.

 

[MarionD]

Hi Mark,
Thanks for the answer - I thought it would be easier to store the data as I have to print various reports using the results. But still, what I Need to know is if I am the right track here - and how to compare from and till Dates to the set criteria.

Something like if from_time >= set_startTime and tilltime is <= set_till_time etc.etc.

or would that be very Long winded?

Is there a better/cleaner/easier/faster way?

 

[jdraw]

Do you have a list of your rules?

- 06:30 till 12:00 (1/2 hour overtime)
-09:30 till 18:00 could also be entered. (1 hour overtime even though hours not exceeded but no bonus)
-anything special for weekends and/or holidays

 

[MarionD]

Hi there from Germany to Canada!

Yes I do have a list of rules and they are really quite straightforward:

1. I know how many hours each employee should work a day. (7:25)
2. I know the "Frame" Timespan. (Monday - Friday 07:00 till 17:00)
3. They enter actual hours worked.

I have to Report monthly on:

1. How many actual hours worked and any over/less time (should work -actual time worked)
2. How many of These hours were worked outside of the "Frame"
3. How many hours were worked after 21:00

So if an employee worked on the 1.2.2016 form 05:30 - 09:00 then again from 12:00 till 14:00 then again from 16:00 -22:00

05:30 - 09:00 =3:30 (5:30 till 7:00 outside Frame = 1:30)
12:00 - 14:00 =2:00
16:00-22:00 = 6:00 (17:00 till 22:00 outside Frame =5hrs) (1hour after 21:00)

Total Hours worked : 11:30 - 7:25 = 4:05 +

total of 6:30 outside of Frame and 1 hour after 21:00
Weekend/Public Holidays
All work Counts as outside of Frame and + hours

Hope this makes sense! and thanks for the answer!!

 

[MarionD]

Forgot to say I also need to keep a "running Balance" of their + or - hours and carry the Balance as a starting balace over to the next month.

My idea was to sort of write a month end procedure..(I am very brave) it could obviously run more than once if for example they had made a wrong entry, they could change the day involved and rerun the month end.

Hence my title "Looping through the records".....Do you think this is a good way to go?

Thanks

 

[jdraw]

Marion,

For what it's worth, I mocked up a small table based on your initial posts. It is a first attempt to get some of the logic working with simple debug.print statements.
Here is the table I used.

	table_name	field_name	data_type	length	
	tblStaffHrs		WorkDate	Date	8	
	tblStaffHrs		From		Date	8	
	tblStaffHrs		Till		Date	8	
	tblStaffHrs		ID		Long	4

StaffID	WorkDate	From	Till	ID
1	01/02/2016	8:00:00 AM	5:00:00 PM	1
1	01/02/2016	6:00:00 PM	11:00:00 PM	2
1	01/03/2016	6:30:00 AM	12:00:00 PM	3
2	01/04/2016	9:20:00 AM	5:00:00 PM	4
2	01/05/2016	8:00:00 AM	5:00:00 PM	5
1	01/05/2016	8:00:00 AM	5:00:00 PM	6
1	17/03/2006	8:00:00 AM	4:00:00 PM	7
2	17/03/2016	5:30:00 PM	6:45:00 PM	8

Here is the code --NOTE this is not a solution--it is just some code to do some checking.
I did not deal with monthly totals nor carryover.

'---------------------------------------------------------------------------------------
' Procedure : shrs
' Author    : mellon
' Date      : 01/03/2016
' Purpose   : Valery trying to loop through data at
'http://www.access-programmers.co.uk/forums/showthread.php?t=285020
'
'This is simply a mockup and trying to get some of the logic set up.
'
'********This is NOT a response to the post *************
'
'---------------------------------------------------------------------------------------
'
Sub shrs()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim currentUser As Long
   On Error GoTo shrs_Error

    currentUser = 898
    Dim totalRegHours As Single
    Dim totalOTHours As Single
    Dim regE As Variant
    Dim regS As Variant
    regS = #8:00:00 AM#
    regE = #5:00:00 PM#
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select staffId,WorkDate, [from], till from tblstaffhrs  group by staffid,workdate,[from],till order by staffid,workdate,[from], till")

    Do While Not rs.EOF
        If rs!StaffId <> currentUser Then
            Debug.Print " Print totals for StaffId " & vbCrLf & vbTab & "Total Reg Hours  " & currentUser & "  " & totalRegHours
            Debug.Print vbCrLf & vbTab & "Total OT  Hours  " & currentUser & "  " & totalOTHours & vbCrLf
            totalRegHours = 0
            totalOTHours = 0
            currentUser = rs!StaffId
        End If
        If rs![From] >= regS And rs!till <= regE Then
            Debug.Print rs!StaffId & "  " & rs!workdate & "  " & rs![From] & "  " & rs!till & " " & (rs!till - rs![From]) * 24 & " hrs regular time"
            totalRegHours = totalRegHours + (rs!till - rs![From]) * 24
        End If
        If rs![From] > regE And rs!till > regE Then
            Debug.Print rs!StaffId & "  " & rs!workdate & "  " & rs![From] & "  " & rs!till & "  " & (rs!till - rs![From]) * 24 & " hrs overtime time"
            totalOTHours = totalOTHours + (rs!till - rs![From]) * 24
        End If
        If rs![From] < regS Then
            Debug.Print rs!StaffId & "  " & rs!workdate & "  " & rs![From] & "  " & rs!till & "  " & (regS - rs![From]) * 24 & " hrs overtime time"
            totalOTHours = totalOTHours + (regS - rs![From]) * 24
        End If
        rs.MoveNext
    Loop
    If rs.EOF Then
        Debug.Print vbCrLf & " Print totals for StaffId " & vbCrLf & vbTab & "Total Reg Hours  " & currentUser & "  " & totalRegHours _
                    & vbCrLf & vbTab & "Total OT  Hours  " & currentUser & "  " & totalOTHours
        totalRegHours = 0
        totalOTHours = 0
        Debug.Print "Finished"
    End If

   On Error GoTo 0
   Exit Sub

shrs_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure shrs of Module AWF_Related"

End Sub

Output of the mock up as it stands:

 Print totals for StaffId 
    Total Reg Hours  898  0

    Total OT  Hours  898  0

1  17/03/2006  8:00:00 AM  4:00:00 PM 8 hrs regular time
1  01/02/2016  8:00:00 AM  5:00:00 PM 9 hrs regular time
1  01/02/2016  6:00:00 PM  11:00:00 PM  5 hrs overtime time
1  01/03/2016  6:30:00 AM  12:00:00 PM  1.5 hrs overtime time
1  01/05/2016  8:00:00 AM  5:00:00 PM 9 hrs regular time
 Print totals for StaffId 
    Total Reg Hours  1  26

    Total OT  Hours  1  6.5

2  17/03/2016  5:30:00 PM  6:45:00 PM  1.25 hrs overtime time
2  01/04/2016  9:20:00 AM  5:00:00 PM 7.66666666666667 hrs regular time
2  01/05/2016  8:00:00 AM  5:00:00 PM 9 hrs regular time

 Print totals for StaffId 
    Total Reg Hours  2  16.66667
    Total OT  Hours  2  1.25
Finished

It's a very crude start, but may be useful.
I recommend processing the data as a recordset and looping. You'll have to determine some logic to deal with
-staff member
-monthly calculations
-reg and OT hours
-carry over.

Start with a flow chart of the logic; do some testing at your desktop; once it's working at the flow chart level; build your program/procedure(s).

Good luck.

 

[MarionD]

Thank you so much for your time and effort - really appreciated.

I am working through it and will let you know how I get on!

Marion

 

[MarionD]

Once again thank you so much! I have worked on it all day...it is exactly what I needed to give me a kick-start! We really need a kiss kiss smiley!

Marion