Query to find Overlapping Time Entries

[JithuAccess]

Hello Guys,

I want to find a query for overlapping Time Entries. These are the fields of my Table:

And these are the values in my Table:

I want to create a query to display the result of the overlapping entries. In this case:

I am attaching my database with the query which I tried to make.

Could you guys please let me know how to do this?

Thanks.

 

[MajP]

I thought we solved this already.

Tracking Duplicate File Review Time

Hello Guys, I am trying to Track Reviewed Hours of Files by various Departments. There are 3 Levels of Reviewing, 1) Admin Assistant 2) Business Analyst and 3) Manager. I want to eliminate Duplicate Entry. For Example an Admin Asst Reviewed a File on 31-May-2021 from 8:15 AM to 4:30 PM. Again...

www.access-programmers.co.uk

 

[plog]

In technical terms--you make a non-equijoin on your table to itself. Simpler, you make a query with 2 instances of your table, link them EmpStartTime to EmpEndTime and EmpStartTime to EmpEndTime; bring in all the fields you want to show in your query, then go into SQL view of your query and change the JOIN portion so that it looks like this:

...FROM YourTable INNER JOIN YourTable AS YourTable_1 ON (YourTable.EmpEndTime >= YourTable_1.EmpStartTime) AND (YourTable.EmpStartTime <= YourTable_1.EmpEndTime)...

A bunch of buts---

1. But you cannot do this because you are storing your Date and Time values in different fields (EmpSTartTime, EmpSTartDate). Its called a Date/Time field for a reason--it can hold both. Get your data into 1 field for the Date and time and you can use the above solution.

2. But you also really need an ID field your in table because otherwise the same records will match with themselves since your joining the same table to itself. With a unique key you can ensure that only different records match.

3. But, you didn't really tell us if EmpID or EmpManager plays into this. My JOIN assumes neither--I just checked for overlapping Date/Time values, not if it was the same employee or the same manager. If you need those to match as well they would be included in the JOIN.



.

 

[CJ_London]

I want to create a query to display the result of the overlapping entries. In this case:

What does this actually mean? you've provided some very simple example data (and I agree with Plog, needs a PK for the record and date/time need to be combined, which could be done in a query) what do you actually want as a result?

And your example data is a bit on simple side - what if one record has a start of 11:00 and end of 12:00 and another 11:30 and 12:30?