Work Hours Calculation issue Multiple Records

[RobinR]

Hi all,

I work for a company that sells access control systems to stores. Access data is stored in a MSSQL database, and I am assigned to build an Access frontend to extract this data and put it in nice reports

When an employee works in a store, he logs in (action), when he goes on a break, there is another action, etcetera. When the system is used correctly, the data shows something like:

1. Work In
2. Break In
3. Break Out
4. Break In
5. Break Out
6. Work Out

Calculating worktime is easy in this case: the time elapsed between record 1 and 6 minus the time between records 2 and 3 and records 4 and 5.

The problem starts when the employee does not select the correct actions during the day. His entries might end up looking like this:

1. 12:00 Work In
2. 15:00 Break In
3. 15:15 Break Out
4. 15:30 Break Out
5. 17:45 Break In
6. 18:30 Work Out
7. 20:00 Work Out

In this case, only the time between records 1 and 2 ( 3 hours) and records 4 and 5 (2.25 hours) are considered worktime. A Break Out should be followed by either a Break In or a Work Out, but not by another Break Out. Same goes for Work Out.

What I need is a way to find an action and look for the next record for that user and check that action. If the action is the same action as the one already found, the time of the first one is replaced by the next identical action, etcetera.

In the example, when Break In is found, the program should look for a Break Out. It finds that in record 3, so worktime starts again at 15:15. The next record however also shows Break Out, so the time should be replaced by 15:30, so worktime starts at that time now.

In the end, I want to calculate times worked in different departments. E.g. an employee in a supermarket might work 3 hours in the groceries department, 2 hour in dairy, etcetera. I want to be able to calculate the time worked per department.

I expect it will be some VBA function using If... Then... Else loops or Case statements, but I am at a loss here!

Any help or pointers is greatly appreciated!

 

[JamesMcS]

How about setting some keys up in the table that records all this? So you'd have a key on name and action say, and have options like "AM Check In", "AM Break" and so on. Then, if they try and go for a morning break in the afternoon (assuming they've taken a morning break already), it'll pop up with an error.

 

[JamesMcS]

Oh and of course a key on the date field. So, 3 in total.

 

[RobinR]

Oh and of course a key on the date field. So, 3 in total.

You are right, but the SQL database is populated by the software controlling the access readers. The users SHOULD be doing the right thing, but that is not being checked or enforced.

The data as is is the only thing I can work with...

 

[JamesMcS]

OK, how about assigning each event an ID number and when you're calculating the times, and pulling back the time with a FirstOfIDNumber type thing in a select statement? So, in the example you've got above, it would pick 1200 as the first of workin, 1515 as the first breakout, and 1830 as the first workout.

Wouldn't help if they selected the wrong event though, not sure how to trap that - perhaps using the event ID, you could make sure a 1(workin) comes before a 2 (breakin)

 

[DCrake]

I suggest you get the Access Control system correct in the first place then there would be no need to worry about garbage data. How is the data being captured?

 

[RobinR]

OK, how about assigning each event an ID number and when you're calculating the times, and pulling back the time with a FirstOfIDNumber type thing in a select statement? So, in the example you've got above, it would pick 1200 as the first of workin, 1515 as the first breakout, and 1830 as the first workout.

Wouldn't help if they selected the wrong event though, not sure how to trap that - perhaps using the event ID, you could make sure a 1(workin) comes before a 2 (breakin)

Each record has an ID number and a description. Your suggestion of FirstOf... is a good one, though, using the ID number as a starting point, didn't think of that!

So I should query the rest of the data starting from the record number I retrieved first.

The problem still will be that I should start calculating the second worktime portion (between records 4 and 5) from the second Break Out since that one is later than the first one.

The report will be used to educate the employees, but mistakes will happen nonetheless.

 

[RobinR]

I suggest you get the Access Control system correct in the first place then there would be no need to worry about garbage data. How is the data being captured?

The problem is that we do not have the possibility to adjust the way the access control system works, so the data is as it is.

Data is being captured when an employee places his finger on one of the readers.

 

[JamesMcS]

It's a toughie for sure - maybe go for last instead of first (I was being un-generous )

To err is Access....