Appointment/Shift Overlaps (1 Viewer)

[dazza61]

Hi there,

I'm posting here under queries even though the solution may be something else....

I've checked all over the web and here and have found some examples that come close but not exactly what I'm looking for.

I have designed and maintain the recruitment databases (in Access) that run 2 employment agencies in a kind of brother/sister company scenario. Internal staff book our employees on varying shifts that can run during the day but can also traverse midnight. The time calculations are all sorted, thanks to this forum. However, I'm having a problem accounting for any overlapping shifts...

Example:

Client: Employee: Shift Start: Shift End:

Client 1 Employee 1 06:00 10:00
Client 2 Employee 2 08:00 15:00
Client 2 Employee 3 12:00 -midday 16:00
Client 3 Employee 3 15:00 00:00 (midnight)
Client 3 Employee 4 16:00 04:00 - next day - very early!
Client 3 Employee 4 18:00 23:00

Okay, you can see that Employee 3 and 4 have overlapping shifts...and I'm wondering what kind of query, SQL, or VBA code would be the most elegant way of listing ONLY the overlaps in a separate form to alert internal staff they have double-booked an employee...I've tried the wonderful Mr Allen Browne's model but can't seem to get it to work for this scenario, though that's probably my stupidness LOL

I'm not concerned with Clients - all I need to do is find overlapping shifts of an employee irrespective of which client they are working for...

Many thanks in advance if anyone can help me

dazza61

 

[HiTechCoach]

The Clashing code example you referred to is the best example I know of.

What are you needing help with?

 

[dazza61]

I guess it's two fold:

1) Examples I have seen take say 20 appointments/shifts to see if ANY clash - we can have 100+ shifts per day and I only want to see if individual employees clash on their own shifts. We accept certain shifts will overlap because you could have say 3 employees on the same shift to the same client...and
2) When comparing stuff, midnight (if I enter 24:00 I get an error) I represent as 00:00, so maybe I'm compicating stuff in my head, but if you compare a start time on one day at 20:00 to midnight 4 hours later, then the start time would equate to "earlier" and if the start time falls in the following day say at 02:00 then comparing this start time to the "same" midnight then the start time would be "later"...if that makes sense...

Or maybe I'm simply approaching this wrongly...

Many thanks for the initial reply and any more that follow

dazza61

 

[HiTechCoach]

This may help:

How to store, calculate, and compare Date/Time data in Microsoft Access

and

Time, adding and subtracting time and timesheets

It may help to store the date and time in the same field for both the start and end times. This way you can handle passing midnight.

 

[dazza61]

Thanks for the links, however I'm okay at working with time conversions although your last sentence has opened new ideas for me...(i.e. storing date/time in the same field)...

Currently, internal staff enter shift start and end times and I only reference the time and not the date (although a separate field handles the date). I then use code to calculate the shift lengths, using the logic that a shift ending time of midnight is always "later" than ANY start time, etc and then more code to traverse midnight, etc. Quite short code actually....it works!

My reasons for this are that we have daily "views" of workers out on shifts and I've always found that queries are easier to work with if you reference dates and times separately e.g. show all workers out on a given date (you don't need a reference to timings naturally)....try a query to "display all workers on shifts greater than Now()" messed me up totally, however that was a couple of years ago when I was still new to Access....anyway I digress LOL

Cutting to the chase, I was really looking for code/query, SQL, subquery, etc that perhaps loops through ALL shifts in one day for multiple workers, picking out any workers that have more than one shift for the same day and then checking to see if any of the individual workers shifts overlap. The code would then move to the next worker with multiple shifts, etc. I can find workers that have more than one shift in a day quite easily, but what I was struggling with was....

e.g. Worker 1 has 2 shifts in a day, so check for overlaps in the workers shifts, Worker 2 only has one shift for the same day so "ignore" this obviously, Worker 3 has 3 shifts in the same day so again check for overlaps between these 3 shifts, etc, etc - bearing in mind that ANY of these shifts will traverse midnight...

I hope I'm making sense because I'm very good at confusing myself

dazza61