Attempting to Re-Create Intra Day Performance

[Thrackan]

Hi Folks!

It's been awhile since I dabbled in MS Access/SQL...ok years and I'm trying to create an interval level staffing report.

My issue is that I can't remember for the life of me how to take a shift or activity like Voice and have it distributed in the different Time Intervals for it's entire duration.

For example:

Start Time Stop Time
7:00 15:00
7:00 15:00
8:00 16:00
8:00 16:00
8:30 16:30
10:30 19:00

End Result:

Interval Start Interval Stop Scheduled Agents
7:00 7:30 2
7:30 8:00 2
8:00 8:30 4
8:30 9:00 5
9:00 9:30 5
9:30 10:00 5


10:00 10:30 5
10:30 11:00 6

SO long story short...take shift duration and have it distributed in each and every 30-minute interval where it exists.

Thank you!

[June7]

Use VBA to create records.

I don't see any 'shift or activity like Voice' data.

[CJ_London]

Or use a Cartesian query

Not at all clear what you want to do with the data - change it ? view it?

[Thrackan]

Thank you. Let me rephrase. I want to determine my over and under of staffing every 30 minutes by taking the agents schedule details and grouping how many have been scheduled each interval of 30 minutes and then comparing that to what I need on a daily basis. I just can't seem to figure out how to distribute multiple shifts (Start and Stops) into quantities per 30 minute interval in a day. I'm sorry if this is confusing. I did this years ago for another company but for the life of me can't figure it out today.

[CJ_London]

still not enough information to suggest a solution

every 30-minute interval where it exists.

clarify what 'where it exists' means

I want to determine my over and under of staffing every 30 minutes

over and under from what?

every 30 minutes

- when do they start? on the hour and half hour? how many in a day? 48? is it a 24 hour/day operation?

and to be clear? say a 30 minute period starts as 7am. Someone is on shift from say 6:50am to 7:02am - that counts as 1 for the 7-7:30 shift? Or all agents start/stop on the 1/4 hour? but 1/4 hour in a 30 minute period counts as 1?

best I can suggest at the moment is you want a count of all agents where

1. their start time is on or before the 30min end time and
2. their stop time is on or after the 30min start time

[Thrackan]

ok. Let me try this again and let me preface by saying I am not an expert. My experience in coding is amateur at best. I don't spend a lot of time in this space but trying to fill a void that is missing.

I'm trying to determine how much staff I have scheduled every 30 minutes

I have scheduling data that looks like this:

Employee Name Start Time Stop Time

John SMith 7:00 15:00
Jane Smith 7:30 15:30
Tom Smith 8:00 16:00
Tara Smith 10:00 18:00

I want to take the information above and turn it into something like this:

Interval Start Interval STop # of Agents Scheduled

6:00 6:30 0
6:30 7:00 0
7:00 7:30 1
7:30 8:00 2
8:00 8:30 3
8:30 9:00 3

This is where the Schedule Data shows how many people are scheduled during each time interval. I just can't seem to figure out how to take a shift start and stop time(s) from a number of shifts and show how many people are scheduled to work during each 30-minute interval.

The second phase is something I have already figured out: over/under, etc. I just dont know how to take a range of time and have it show split up by Time Interval.

I appreciate your help and hope this makes it more clear.

Thank you.

[CJ_London]

Well you haven’t answered my questions so I’ll assume your examples are as far as we are going to get for an explanation.

You’ll need a table for your half hour sessions with start and end times. Then have Cartesian aggregate query with your employee table with criteria as per my last post

Not shown but presumably you also have a date field as well which you will also need to take into account.

[Thrackan]

To answer your questions:

1. Every 30 minute Interval where it exists. What does "Where it exists mean"? This is my gap in explaining properly so I tried to re-iterate above. What I meant by that is that if a shift is from 8AM to 11AM, "it" exists in, is a part of or contributes to the following intervals: 8:00-8:30, 8:30-9:00, 9:00-9:30, 9:30-10:00, 10:00-10:30, and 10:30-11:00.

2. Over and Under explained. The ultimate goal is to see every half an hour whether Ive scheduled enough people. I have the schedule that shows when people have been scheduled to work and I have a separate forecast file that tells me how many people I need to work each half an hour. The over and under is simply number of people scheduled versus number of people required. I left this out of my last post as I do not require help here, just with distributing shift times within the various time intervals

3. This is a 24 hour operation but will only be focusing on the times from 7AM to 11PM daily. By having this information we can proactively see where the gaps are on a daily basis and make certain staffing decisions to ensure we are meeting the requirements as suggested by the forecasts. With that being said, if I want Intervals every 30 minutes and the Hours of Operation are from 7AM to 11PM then that would mean I would have 32 daily interviews.

Based on your suggestions, I'm thinking that I haven't painted an effective enough picture. When you speak of Cartesian, you mean cross-tab query? Also these aren't half an hour sessions. I just want to count how many agents I have scheduled every half an hour based on their schedule start and stop times. That's it.

[Thrackan]

I'm looking to automate this view as I am dealing with hundreds of agents schedules across multiple sites so I am looking for any or all suggestions that will help me determine this without having to invest in software that already does it. ty

[Micron]

So using this data

John SMith 7:00 15:00
Jane Smith 7:30 15:30
Tom Smith 8:00 16:00
Tara Smith 10:00 18:00

the agent count between 8:00 and 8:30 AM is 4? At 16:00 it is 2?

You would not check from 8:30 to 9:00 as well as 9:00 to 9:30 but rather 8:30 to 8:59 and 9:00 to 9:29 or something similar?
A form with a timer event could run a query (or set of) every 30 minutes. Rather than rely on the system clock staying in sync with some starting point so that your interval check is what you want, I think I'd have a table of frequencies of start and ends (2 additional fields) and a sequence field (1 to n, rather than an autonumber ID field). A form level variable could start with 1 (sequence 1) and every time you run the test, it increments by 1 so that the next time you use the next sequence (2, which is 8:30 to 8:59). You should be able to get a count of all the scheduled people WHERE Start >= 8:30 AND End <= 8:59 (where those time values come from the record for sequence 2) by using a Totals query.

Not sure if that is as clear as mud or what.

[Welshgasman]

How about giving us some data to work with?
I would not know how to write that off hand, but would experiment until I found out how.?

I am thinking a table to hold the intervals, then someway to get all those values compared to the agent shift times, nested Select perhaps?

As I said, I would experiment and for that I would need data.

[CJ_London]

1 you will still need a table of all possible times then exclude when they are null
2. And what does this table look like? Would have thought that would contained this information

You are not helping yourself providing so little information. You have decided how this should be done- the bit you have asked about I have already answered.