Gaps in time in a schedule database

[clew3]

I oversee tutors and often need to quickly see if a tutor is available for a specific subject at a specific location, weekday, and time. I created a database that tracks all this information to be used for a printed report. This report should not show the tutor's names, just the tutoring specialty availability.


The query results in something like this:
LocationA Monday Algebra 8:00am 1:30pm
LocationA Monday Physics 12:00pm 4:30pm
etc.

This all works fine as long as the different tutors' time ranges overlap, but the minute they don't it all goes out the window since Min and Max aren't able to tell if there is a gap in time. If there is a gap in time, then I need the query/report to look more like this:
LocationA Monday Algebra 8:00am 10:00am
LocationA Monday Algebra 12:00pm 1:30pm
etc.

I played around a little bit with DMin and DMax, but it didn't seem to be the answer unless I'm not using them properly. I searched multiple forums and websites, but can't find a similar problem.

Any help or ideas would be appreciated.



Edit:
Added some table/query screenshots.

[CJ_London]

you say

need to quickly see if a tutor is available for a specific subject at a specific location, weekday, and time

how does this tie up with the schedules table? Is that table showing when they are teaching (so not available for any other subject) or does it just show they are available for any subject? Or is it one teacher, one subject? or what?

Also it is very bad practice to name fields the same as tables- at least rename the tables in the plural (e.g. specialities) - and to use the same name for different datatypes (speciality again) - so you have an object called 'speciality' which might be a field, might be a number or it might be text. Your use of multivalue fields will also confuse things since they can be difficult to work with.

You have shown some query results - you also need to show the source data that generated those results

[pbaldy]

Does this help?

http://www.baldyweb.com/OverLap.htm

[clew3]

The Employees table just stores employee info: Name & Specialty which is a multi-value choice field.

The Schedule table stores that employee's schedule by Day of the Week (so the schedule can be viewed chronologically, and because most employees have the same schedule for multiple days of the week this is setup as a multi-value choice field), their location on that day, and their time in and time out (Date/Time fields set to Medium Time). Then that has a relationship to the ID from the Employee table.

So each employee can have multiple subjects they are tutoring. Each subject then is available during the hours between TimeIn and TimeOut. The point is to be able to key in an employee's time on an individual level, pick their subject areas (specialties), and then see a result of when that subject is available for tutoring.

I'll upload some table screen shots in the original post.

[clew3]

Does this help?

http://www.baldyweb.com/OverLap.htm

I don't think that's it. That's looking for individual records, whereas I'm trying to aggregate them by Specialty. And that is entering a date into a form to check between, whereas my query should do it automatically with no input other than the table data itself.

[CJ_London]

looks like you need to use a Cartesian query - no joins. but don't think you can do it with multivalue fields