Add sequence number to time punch table

[aflamin24]

I have a table at work that contains employee time card punches. There are actually 2 tables, the table punch in contains the employee scheduled start time and then their actual start time. I am able to get their actual punch in.

The challenge is that the other table, we'll call it Punches has multiple punches becauce the employees are full time. They leave for lunch and change work areas occassionally, all during the same work day. I'm trying to add a sequence number to my query, or maybe I need to create a local copy of these tables daily and add the sequence number there? I'm assuming I can do this by taking the an employees first punch time say 1700 hours and that would be 1, then each punch after would be 2, 3, 4 etc..

The nice thing about the Punches table is that instead of going back to 0001 at 12:01 am it goes to 2401 and that saves me a hassle from having to deal with different dates b/c many of these employees finish work after midnight.

Anybody ever done something like this? The goal here is to get the amount of time these people worked at each area and display it on a gantt chart, but I have that part taken care of.



Thanks again, I'm a noob.

[June7]

I haven't seen this question asked before.

Should this sequence start over each workday? How would you know that employee's 'first' punch time is at 1700 hours (5 pm)? Would it be the first time that follows scheduled start time? What if they punch in before scheduled start time? No matter what, shifts crossing midnight will still be an issue.