Help/Direction Please

[Ste4en]

Help PLEEEEAAAASSSEEE

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. I use the Max of punchfield for the Out time and Min of punchfield for the In time. This is done by a query.

This 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..... I believe the following rules / code structure will work but I have no idea where to put them or how the 2 Report Dates in the else will work...

If shift = 1 then
PunchDate = ReportDate
InTime = MinofPunchTime
Outtime = MaxofPunchtime

else '(shift=2)
PunchDate = ReportDate
InTime =MaxOfPunchTime

PunchDate=ReportDate+1
OutTime=MinofPunchTime

End if


Is this a function or a procedure and how do I call it from a query. Any suggestions and guidance appreciated.

Thanks


Steve

 

[Ste4en]

I am not getting any reply to what I thought was an easy problem - Let me explain some more.

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.

This worked fine until we introduced a night shift (for the next few
months only).


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.

My "code" structure as in my original post may make more sense now.


Hope that helps clarify - It does not seem difficult to do so I am
sure its my explaination that's the problem, please ask if you need more clarification - let me help you to help me!

Many thanks.