I posted this Q on the VBA board but no answers – So I am reposting here….
I have a database which reads data from a table in a handscanner database for time in and out of work. I have to link to a payroll system and so unable to use the handscan software to do what I need.
The scanner provides 2 lines of raw data per employee each day - see example below. I use the Max of punchfield for the Out time and Min of punchfield for the In time. This is done by a query which has been working flawlessly for sometime, however we have introduced a second shift ending after midnight and so there are some difficulties because the out time is now the Min of Punchfield on the following day.....
The punch data that I get is in minutes which are zeroed at midnight. So my data will be as follows:
Typical Day Shift - Shift 1
01/20/02 JoeDayWorker; Punch 360
01/20/02 JoeDayWorker; Punch 960
From this the time of entry was 360 minutes after midnight on 01/20/02 i.e. 6am and that he left 600 minutes later at 960 minutes after midnight i.e. 4pm on 01/20/02.
For any date & name I query the data and select MinofPunch as the In Punch and MaxofPunch as the Out Punch. Each person is designated to shift 1 or 2 in the roster.
Typical Night Shift - Shift 2
01/21/02 JoeNightWorker; InPunch 1080
01/22/02 JoeNightWorker; Outpunch 240
He arrived at 6pm and leaves the following morning at 4am.
Following the same rules as for shift 1 would provide that he clocked in at 4am and went home at 6pm. The time difference is correct but it is based on the wrong pair of punches as we would find out on Monday when there would be no out punch for the Sunday night when we don't work.
So my plan was, when the shift = 2 to use the Max Punch of
[ReportDate] as the in Punch and the Min Punch of the [Report Date]+1 (tomorrow) as the outPunch. This way I would be reading the correct In and Out Punch pair.
Code something like this ...
If shift = 1 then
PunchDate = ReportDate
InTime = MinofPunchTime
Outtime = MaxofPunchtime
else '(shift=2)
PunchDate = ReportDate
InTime =MaxOfPunchTime
PunchDate=ReportDate+1
OutTime=MinofPunchTime
End if
My problem is I don’t know how to write this in code or where to put the code. Is it a public or private function or sub and how do I call this from a query.
Any help appreciated.
Many thanks
Steve
I have a database which reads data from a table in a handscanner database for time in and out of work. I have to link to a payroll system and so unable to use the handscan software to do what I need.
The scanner provides 2 lines of raw data per employee each day - see example below. I use the Max of punchfield for the Out time and Min of punchfield for the In time. This is done by a query which has been working flawlessly for sometime, however we have introduced a second shift ending after midnight and so there are some difficulties because the out time is now the Min of Punchfield on the following day.....
The punch data that I get is in minutes which are zeroed at midnight. So my data will be as follows:
Typical Day Shift - Shift 1
01/20/02 JoeDayWorker; Punch 360
01/20/02 JoeDayWorker; Punch 960
From this the time of entry was 360 minutes after midnight on 01/20/02 i.e. 6am and that he left 600 minutes later at 960 minutes after midnight i.e. 4pm on 01/20/02.
For any date & name I query the data and select MinofPunch as the In Punch and MaxofPunch as the Out Punch. Each person is designated to shift 1 or 2 in the roster.
Typical Night Shift - Shift 2
01/21/02 JoeNightWorker; InPunch 1080
01/22/02 JoeNightWorker; Outpunch 240
He arrived at 6pm and leaves the following morning at 4am.
Following the same rules as for shift 1 would provide that he clocked in at 4am and went home at 6pm. The time difference is correct but it is based on the wrong pair of punches as we would find out on Monday when there would be no out punch for the Sunday night when we don't work.
So my plan was, when the shift = 2 to use the Max Punch of
[ReportDate] as the in Punch and the Min Punch of the [Report Date]+1 (tomorrow) as the outPunch. This way I would be reading the correct In and Out Punch pair.
Code something like this ...
If shift = 1 then
PunchDate = ReportDate
InTime = MinofPunchTime
Outtime = MaxofPunchtime
else '(shift=2)
PunchDate = ReportDate
InTime =MaxOfPunchTime
PunchDate=ReportDate+1
OutTime=MinofPunchTime
End if
My problem is I don’t know how to write this in code or where to put the code. Is it a public or private function or sub and how do I call this from a query.
Any help appreciated.
Many thanks
Steve