Query for Overtime Staff

[EileenAchil]

Hi, I have a staff daily report that auto download everyday. My problem is:
1. Person A is working on 28/12/16 ( Shift C) from 8pm till 8.15am.
2. I want to calculate how many times person A scan in and out his/her card.
(using excel graph)
3. The daily report time has been set to download record from 12am till 11.59pm. So means that in 29/12/16 report have a person A slide out record ( which is suppose to be in 28 /12/16 report). Then i come out with this solution:
IIf ((Record.TransTime<#9:00:00#) And (calendar.Shift="night"), Record.TransDate (1-0-0), Record.TransDate) AS ActualDate


But then, I realise what if person A is actually working overtime? Because i found out person A entry time is 7:45am (29/12)
Anyone have any idea on how to solve this?
I have 2 excel.
1. Record ( staffno, TransTime, Transdate)
2. Calendar
Eg:
27/12. Shift A. Day Shift B night
28/12. Shift A. Day Shift C night
29/12. Shift A. Day Shift C night
29/12 is day shift for shift A , some of shift C is working on day shift (OT). Day shift start at 8.15am till 8pm and night shift start at 8pm till 8.15 am.
Please help..... Thanks in advance

[orange]

See this info at Baldyweb regarding time periods and overlaps.
It may help.