-3

I have to tables, a clock in table and clock out table, they both do the same: store the time where the user clock in or clock out.

I have a query that displays and add the times to show me how many hours they all worked but when they clock out for lunch and then clock back in the query register 3 times, from the 1st punch in time to lunch then from lunch to last the clock out time and then from the 1st clock in to the last.

I would like the query to get it right only add the hours that each employee worked.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Minion87
  • 3
  • 2
  • 1
    It's extremely unclear what you are asking; showing the code you have so far would be very helpful. – LittleBobbyTables - Au Revoir Nov 06 '13 at 14:57
  • 2
    A description of your table structure (column names, data types, and *representative* sample data) would be very helpful, too. – Gord Thompson Nov 06 '13 at 15:21
  • Thanks for replying, I have to tables, a clock in table and clock out table, they both do the same: store the time where the user clock in or clock out. I have a query that displays and add the times to show me how many hours they all worked but when they clock out for lunch and then clock back in the query register 3 times, from the 1st punch in time to lunch then from lunch to last the clock out time and then from the 1st clock in to the last. – Minion87 Nov 06 '13 at 15:38

1 Answers1

0

The way I approached this way back when was to add a 'shift' field into the table design. So 1st clock in of the day was morning shift, second was afternoon, and so on. (This was added via code, not the user) That way if you have only one clock in per shift, calculating is straightforward. If a person needs to clock in and out on a more frequent basis, then you put a simple counter that gets set to 1 whenever a new day (time card speaking) is started. The first clock in and clock out have the count id of 1. The next have 2, etc... Once again, just match your id fields and dates and user ids and your calculations should be straightforward. If you need, I'll try and find that old bit of design and forward it to you.

craig.white
  • 401
  • 2
  • 8