Comparing EndTime from one record to StarTime of another record within same query

[RobOtowski]

Hello all, my apologies if this is answered elsewhere but I've spent the better part of the day seeking the answer to no luck. I am relatively new to Access, and know pretty much nothing about VBA or SQL language.

I'll try to be brief in the background for this: I work at a college, we connect several rooms via camera/mics, allowing the instructor to teach to multiple rooms at the same time. We have a system that automatically connects all the necessary rooms to a conference at a set time (class start time + a variable pad), then disconnects everything at another set time (end time + 4 minutes). That "variable pad" is what is giving me the headaches. If NONE of the rooms in a class are in use 15+ minutes before class starts, we put a 10 minute pad on the start of the conference. If ANY of the rooms in a class are in use within 15 minutes before start time, we put a 4 minute pad on the start of the conference.

I have several tables linked together within a query to give me (among other things): Title, StartTime, StartPad (which is just StartTime - 15 minutes), EndTime, Room. I would like to compare the StartPad of one class to the EndTime of the previous class and let me know if there's a conflict. Or indicate how much time there is in between. Or anything at all that would let me know that which classes get which pad (10 minutes or 4 minutes).

I found this last week: http://allenbrowne.com/appevent.html and it's a great way to see where the conflicts are, but it includes both events in a given conflict. This means that it will give me the title of both classes, even if one of them has no conflict before it. (For example, I have a HIST class that runs 9:20 - 10:15 and there is no class right before it. There is a GEOG class that runs 10:25 -11:20. The method linked above will return BOTH the HIST class and the GEOG as "conflicting", even though the HIST class has nothing before it. I understand why it works that way, but it's not what I need.) So I ultimately get a list of any class that either starts or ends within 15 minutes of another, rather than only those that start within 15 minutes of another's ending. This is why I emphasized "previous" above.



I also found this: http://allenbrowne.com/subquery-01.html#AnotherRecord, but the issue I find here is that I'm comparing different fields (EndTime to StartPad), whereas the example uses identical fields (ReadDate). I attempted to substitute my fields for the example ones and got an error message ("This expression is typed incorrectly, or it is too complex to be evaluated...") I've triple checked all my spelling and such and I do not see any errors there.

Again, I know almost nothing about SQL or VBA so go easy on me with the "I assume you know how to ____", I probably don't know how to ____! Any light that can be shed on this topic would make me and my ulcer (I call it "Databasery") very happy! If I've not been clear enough about anything I'm happy to go into more detail, I just didn't want to weigh the post down with anything unnecessary.

[ItsMe]

Can you post a screenshot of one of your tables in datasheet view, so we can see some data within your table(s). I am curious what data is being stored and I think a screenshot might provide some insight to your data structure.

[RobOtowski]

Can you post a screenshot of one of your tables in datasheet view, so we can see some data within your table(s). I am curious what data is being stored and I think a screenshot might provide some insight to your data structure.

Here's one of the tables, named "CourseListTermDetails". The structure goes:
CourseMasterList, containing info that never changes, such as title, subject, course, credit hours
CourseListTermDetails, with info that's the same across all sections, such as term (linked to another table), days (separate field for M - Sat), start time, end time, delivery method
CourseSectionList, with info about each individual section like section number, room (linked to another table), enrollment
Other tables linked into these include TermDetails, RoomDetail, CourseInstructor.

So that's why my title and initial question were about comparing data from within the same query as opposed to the same table. I have a query that pulls all the information about all the sections for a specific term, then I work from there as needed. I have ones that don't care about sections, I just need class and instructor data, others with room details (campus, capacity). All these work together to give me reports for the various people that need them. I have reports for the two delivery methods that include instructor and section numbers for one person in our department, I have a report for the guy that "builds" the conferences, with all the sites that need to connect and the meeting days and times. He's the reason I need that "10 or 4 minutes pad" thing. There are others, too, but I doubt you need all that.

I just previewed this message and that screen grab is huge! Sorry about that. I'd resize it further, but then we get into illegible territory.

[RobOtowski]

A breakthrough! Playing in a sandbox database I came up with some SQL code (is "code" the right word?) and I believe I have it worked out

So what it comes down to: create a Make Table query to get just the classes that meet on Monday (other days are irrelevant at this time), sort the table as oldest to newest. Then make a query with the following SQL:

SELECT MondayClasses.Title, MondayClasses.SUBJ, MondayClasses.COURSE, MondayClasses.StartPad, MondayClasses.StartTime, MondayClasses.EndTime, MondayClasses.Room, (SELECT  TOP 1 Dupe.EndTimeFrom MondayClasses AS Dupe
Where Dupe.Room = MondayClasses.Room
AND Dupe.EndTime<MondayClasses.StartTime
Order By Dupe.EndTime DESC) AS PriorClassEnd, IIf([PriorClassEnd]>[StartPad],"4","10") AS PadTime
FROM MondayClasses;

This gives me each room for each class and what time the previous class in that room ends. From there it compares the PriorClassEnd (time) to StartPad, if they overlap then "PadTime" returns "4", if they don't overlap, it returns "10". This is exactly what I was needing!

[ItsMe]

Sorry I did not have the chance to respond earlier. Things got pretty hectic yesterday.

It seems you have a solution to your original question. However, I think it is convention to not allow data to be entered that would cause an overlap of time. For example, a user would request a time and a room via unbound controls. A query would search the existing records within the database for conflicts on the Start and Finish times using the BETWEEN operator.
http://www.w3schools.com/sql/sql_between.asp

So the idea is to not allow data to be stored in the table unless it is pure and valid. This technique would be similar to the style of a standard search form. It is likely the code in your SQL BETWEEN statement could also include syntax that would resolve the 4 and 10 rule.

[orange]

Further to ItsMe's comment re overlapping, you may find this article from PBaldy of interest.

[RobOtowski]

Thank you both for your help and insight. For now I have to move on to other aspects of the project. I have it working, though it may not be the most efficient way, so I'll have to live with it for now. I have bookmarked this thread and the references you've shared and I'll come back to this at a later date to see if I can clean it up a bit.