Help with program logic

[Rattlesnake]

hi,
Mine is not a technical question ,but a logic question.
I am creating a time and attendance software.
I have an access database that stores the IN and OUts of employee recorded by a finger scan device.
We have three shifts in our company.(7 to 15, 15 to 23 and 23 to 7)
I donot want to specify any shifts for the employees but want my application to decide on the shift depending on the IN time.
I was wondering if any one has worked on something like this before and has any ideas on how i can achieve this.

Thanks

 

[dcx693]

Since you've got the actual In and Out times stored, you can go two routes.

One is to create a calculated field in a query. You can do that using some complicated IIf or Switch function, or even by writing your own custom VBA function. All are interesting and good to attempt if you want to learn more about Access.

The other, more elegant solution, is to create a table, call it "tblShifts" that contains the beginning and end points of your shifts and join it to the table containing your In and Out times (I'll call that table "tblInOut". The join operator in this case will not be the typical equijoin that occurs when you "drag" one field from a table onto another. Instead, use the Between operator. The SQL for the query will be:
SELECT tblInOut.EmployeeID, tblInOut.[In], tblShifts.Shift
FROM tblInOut, tblShifts
WHERE (((tblInOut.[In]) Between [tblShifts].[Start] And [tblShifts].[End]));


If that all scares you don't worry, I've attached a simple version of the database so you can examine it. It's in Access 97 format. If you're opening it into a more recent version, choose the Convert option when prompted, and save it under a different name. Run the qryShift query and you'll see the results. Just adapt the database to your own needs.

 

[FoFa]

I would create the table with the shift times, and a "fudge" factor for each shift as well. As an example, Shift 1 is 7 to 15, but John checks in 6:45, is that shift 3 or 1? A fudge factor allows you to take someone checking in early as the correct shift. So if you set the fudge factor to 20 minutes, then John would be within Shift 1 checkin time. Here we also have a late fudge factor, where lets say it is set to 15 minutes, if they are late, but within that fudge factor, everything is OK, if they are past that, they have to see their supervisor to check in because they are so late. Since the application is running 24X7, we load these values into global variables and use a case statement to check, that keeps performance up without undo table access for each person checking in/out. It also allows us to change the numbers with a simple restart of the application (like that happens often) but that option is there.