Looking for a new approach

[doulostheou]

I have made several posts requesting help for the coding of a scheduling program I am attempting to make. However, as I keep thinking through the process; it seems there must be a better way. I need Access to figure out how many people I have staffed at any given half hour during the day (this is for a 24/7 operation). I currently have part of it working, with one table to store the number staffed at each half hour interval and the other to edit the start and end time of each representatives shift. I have created an AddStaff and RemoveStaff function that removes 1 from the specified time slot on the Staff table upon entering the control in the Shift table and then adds 1 from the specified time slot upon exiting the control. This seems to work except for a glitch when the value is Null.

But as I'm thinking through this project, it seems my whole approach may be wrong. I am dealing with a little under 200 employees. About half of these have set schedules. My original plan was to create a new table for each new schedule. It would create a record(including Start and End Times/Monday through Sunday) for each active employee. I was then going to have the set schedules stored in a separate table and have that updated in the new table for those employees who are set. For each shift it added it would then have to use the AddStaff function to increase the number in the Staff table by 1 during that shift time. I'm just realizing that in addition to being a lot of work to create, the project described above is also going to require alot of processing by the computer.

Does anyone have any suggestions for a better approach? It would be ideal if there was some way for access to dynamically evaluate the number staffed during each interval in a query. I'm guessing there is no way for it to be that simple.

 

[Kalor]

Most approaches use something like this:

Employees
--------
EmployeeID
Name
Address
etc

HoursWorked
-----------
EmployeeID
StartTime (full date, ie day and time)
StopTime

Then, to tell how many employees are working at any given time, use a query like:

Select count(*) As [Employees Working] FROM HoursWorked where <Given Moment> Between HoursWorked.StartTime and HoursWorked.Stoptime

Is this too simplistic for your requirements?

 

[doulostheou]

This is exactly what I needed (though it didn't seem that simple to me). Now I just need to try to figure out a dynamic way to use this query to display the number of people working at 34 different times, seven days a week. I'm guessing that using the runSQL string in code for each of the 238 fields would be ineffective.

 

[doulostheou]

I keep running into a dead end. I am trying to make a separate field for each of the slots on my form I will want filled. So my thought was I would replace [Given Moment] with an actual value and duplicate this for each time slot needed. But I keep getting a data type mismatch whenever trying to enter in the General Date format. I tried it with and without quotation marks. Yet it works when I leave it a paramater query and type in the general date myself. I don't understand where I'm going wrong.

 

[Rich]

238 Fields? surely not.

 

[doulostheou]

I don't know what else to do. We need to be able to evaluate exactly how many people we have staffed for all 7 days of the week to decide where people are most needed when making the schedule. The overnights are in a category of their own, but I still need to be able to see how many are staffed each half hour from 7am until midnight. That's a total of 238 fields.

Currently, this is all setup in an excel workbook; but it's really a pain to work with. I wanted to move it into access, so that I could automate more of the process and set it up so that all tools are on one screen. In the excel workbook, you have to manually type "1" in each half hour slot you want to schedule one of the reps. Plus you have to constantly flip between pages to find out how many you have staffed, what the rep's availability is, and to actually adjust the schedule.

 

[jatfill]

The only flaw in your approach is that you are relying on data storage for each time interval. If you store the fixed schedules (employeeID, start time, stop time), you can build the snapshot based on that schedule by looking at a particular time, i.e. 5/7/2002 7:30AM & see how many have start time less than 5/7/2002 7:30AM and a stop time greater than 5/7/2002 8:00AM (so they are available in the time frame chosen) on that day. Then you have a return of everyone on duty at the time you are inquiring about.

Plugging these calculations into a Pivot Table would give you exactly what you are looking for.

 

[doulostheou]

I have never built a pivot table before so I am not exactly clear about the extra functionality it adds.

However, the actual need is to see how many people are scheduled at all intervals during the week at one time. I haven't been able to figure out how to do all the scheduling automatically (most of our employees do not have fixed schedules). Therefore, I was going to implement a scheduler in access that was similar to what was built in excel but had all the elements on one screen, which would save a huge amount of time. On excel there was one sheet we could go to and see exactly how many more people were needed to meet the forecast of the number of employees needed for each shift (I am in a call center, so the number of employees needed fluctuates regularly). Some of our employees have offered us 80+ hours of availability. I have to look at those 80 hours and pick 16-30 of them to actually have them work. The rules of course become more nuanced and change per employee.

But to do this, I need to see how many people I need scheduled during each interval so I know which days and which hours to pick for them to work. Initially, I was trying to store the data for each interval. Kalor pointed out that I could query how many would be working at each interval. I certainly like this better than storing the data. But I don't know of a practical way to display all 238 intervals at once.

 

[jatfill]

So between dates A and B, you need to be able to display each 30-minute increment from 12AM to 12AM? That's not too hard to calculate, actually:

Try creating a test form with a list box, two fields (for start date and stop date), and a command button:

ListBox: lstIncrement (the field source should be set to Value List)
Field 1: txtStart (General Date format)
Field 2: txtStop (General Date format)
Command Button: cmdCalculate

put the following code behing the button:

Private Sub cmdCalculate_Click()

On Error GoTo ErrorCalc

'Check for start & stop
If IsNull(Me.txtStart) Then
    MsgBox "Please enter a start date", vbOKOnly, "Criteria Missing"
    Exit Sub
ElseIf IsNull(Me.txtStop) Then
    MsgBox "Please enter an end date", vbOKOnly, "Criteria Missing"
    Exit Sub
End If

DoCmd.Hourglass True

Dim Start As Date
Dim tStop As String
    Start = Format(Me.txtStart, "mm/dd/yyyy hh:nn:ss")
    tStop = Format(Me.txtStop, "mm/dd/yyyy hh:nn:ss")
    

    
    'Clear list if previously built
    If Me.lstIncrement.RowSource <> "" Then
        Me.lstIncrement.RowSource = ""
    End If
    
    'Build the first row of the new list
    If Me.lstIncrement.RowSource = "" Then
        Me.lstIncrement.RowSource = Format(Start, "mm/dd/yyyy hh:nn:ss")
    End If
    
Dim PrevDate As String
Dim NewDate As String
            
Do While Not NewDate = tStop 'Run until the new value equals the end date specified
        PrevDate = Right(Me.lstIncrement.RowSource, 19)
        'Add 30 minutes
        NewDate = Format(DateAdd("n", 30, PrevDate), "mm/dd/yyyy hh:nn:ss")
        'Add new values to list
        Me.lstIncrement.RowSource = Me.lstIncrement.RowSource & ";" & Format(NewDate, "mm/dd/yyyy hh:nn:ss")

Loop
DoCmd.Hourglass False

ErrorCalc:
    If Err.Number <> 0 Then
        DoCmd.Hourglass False
        MsgBox Err.Description
    End If
    
    Exit Sub

End Sub

I haven't tried this, but I suspect wider date ranges would take some time before they would finish

Run the form & enter a start and stop date in the fields. Click calculate and the list box should return every half hour increment from midnight on the start date to midnight on the end date. Basically using something like this could allow you to build your query criteria for checking employee schedules.

This doesn't solve your problem completely, but hopefully it's a good start...

 

[doulostheou]

I really appreciate your post jatfill. Unfortunately, I'm having a few problems with it.

First, the list box doesn't quit filling out when it reaches tStop. It keeps going and then gives me an error that it can only contain so many characters.

Second, it looks like it reaches its limit of characters after three days; and I'll be consistently needeing a 7 day period (Monday through Sunday).

Third, I'm not sure how I would implement this. Can I make a list box the criteria for my query?

As a side note, I don't need all 24 hours each day. I only need 7am until 12am. I just want to populate the half hour intervals between those time periods Monday through Sunday of the week I'm making the schedule for. I'll need to refresh this query after each schedule change, so if the query takes too long to run there will be a problem. I might be asking too much of Access, but I have trouble believing that Excel can do anything more effectively than access.

 

[neideb]

I was just passing in the forum and saw your problem. Have you already solved? I work with Excel and I could give you some ideas about datatable in excel and crosstabquerry.
If you want pls e-mail me neideb@gbl.com.br

 

[The_Doc_Man]

I think part of this problem is, as you say in your earlier comments, conceptual. You are building a solution in Access (or attempting to, anyway) in which you treat each time interval as a cell in a spreadsheet. Access is not a spreadsheet program. You have a square-peg-in-a-round-hole situation, totally big time.

Here is what I might do...

First, you need a function that displays a number as hh:mm although it is stored as minutes since midnight. This probably means some public function (VBA) code to build a string, i.e. do the format conversion.

Second, you need a public function that takes two numbers (assumed to be separate hours and minutes) and converts this to a number of minutes past midnight. (Or you could take a single string and parse it to find the colon, then find the numbers before and after the colon. Your call.) The coward's way out is to make the hours field 00-23 in true military style. The gutsy way is to include AM and PM. I'd take military in a heartbeat. Less work.

You will need these functions to support data input and output for times of day. You cannot use a Date/Time variable quite so easily in this application. That is because the bloody things imply a date and get all huffy if you didn't really mean for them to have one. Don't get me wrong, they are great for what they are intended for, but if you stray from that intent, WHAMMO!

Then, build some tables:

tblPerson: loID, stFirstName, stLastName, stMiddleInit, etc. Things about the person only. Phone, address, ... NOTHING about schedule. loID (Long integer) is the primary key. You COULD include department or other stuff here as long as it is only related to the person. Or you could build a department table with a loDeptID and include all department data there, just link to it from here. (The latter is the more technically correct solution.)

tblSchedule: loID (from tblPerson), loWeekday (1-7 for Monday through Sunday or pick your other favorite mapping of days to numbers), loStartTime, loEndTime, loShiftNum.

[ShiftNum(ber)] is used when the person has more than one shift broken by a gap for lunch or perhaps a long split-shift situation. You have as many of these per day as the person has continous shifts. You would have the person's schedule entered ahead of time for an entire week. If the person isn't working on a given day, don't have a record for that day.

You could surely index the loID, loWeekDay, and lo{TIMES} fields, as long as they allow duplicates. Perhaps you could make a compound primary key out of loID, loWeekDay, and loShiftNum. They are all numbers, fairly simple, and the combination of person, shift number, and weekday should be unique. (Otherwise someone is running two shifts at the same time!)

As an option, you could include a location, station, or assigned post identifier (i.e. where this person is) for the given time. (Employee #326 will be at Building 4 office for the early portion of his shift on Tuesday.)

OK, the next step is that the number of folks on duty at any time is just a DCount -

loCount = DCount( "[loID]","tblSchedule", "[loWeekDay] = & CStr(DayOfParticularInterest) & " AND [loStartTime] < " & CStr( MinutesAfterMidnightOfParticularInterest) & " AND [loEndTime] > " & Cstr(MinutesAfterMidnightOfParticularInterest) )

Hidden assumption - if a shift crosses midnight rather than ends or begins at midnight, this sucker gets complicated as all hell. Not impossible, but pretty ugly. The easiest way is to divide the time up into two shifts: StartTimeBeforeMidnight to midnight, then midnight to EneTimeAfterMidnight. Since you would have to have different day numbers for the two parts of the shift in that case anyway, that is one way to approach the problem.

Now, the next part of your problem is your report that you want to build. For that, I don't see an easy way around it other than to write a table that lists every day in question. I.e. a table called

tblCritTimes: loWeekDay, loCritTime, maybe the conventional equivalents for weekday name and critical time as hh:mm. (Where the pair of long integers is the primary key). You put each time of interest in one row of the report for each day where you have this interest in the time. If the business is closed on Sundays, obviously you would not have a Sunday entry in the table.

In any case, you only build this table and populate it ONCE. Thereafter, it could be totally read-only. One entry might look like

1, 60, "Monday", "01:00"

(To the purists out there, I know this table contains what appears to be replicated or duplicated data, but that is being done for the expressed purpose of making the Son-of-a-Gun run within someone's lifetime. It is to make the reports easier to write.)

Then you could do a query that uses the tblCritTimes and computes the DCount of the messy expression based on the loCritTime field of that table as the critical time for the DCount, and the loWeekDay of the table as the weekday part of the DCount query.

This query would then be available for a report that groups by whatever you want it to group by. Like, maybe the day of the week or the hour of the day (but that would require you to compute the hour separately, of course.)

Now, as to how you would present this in your report, I'll leave all formatting issues to you.

By the way, how the heck do you come up with 238 time fields? 238/7 = 34, which does not seem like a reasonable number of time slots for a 7x24 operation. The closest it gets is 238/5 = almost 48. But you don't have to answer that, I'm just being curious.


[This message has been edited by The_Doc_Man (edited 05-21-2002).]

 

[doulostheou]

Thanks for your reply. I go into work tomorrow and will hopefully have time then to play with some of your suggestions.

The reason I have 238 is because we are not concerned with the number of people staffed during the overnight. We get very few calls between midnight and 6am, and the number staffed at midnight nearly always stays the same until 6a. These 238 fields are a way of measuring how close I am to the forecasted number of reps needed during any given half hour interval. I use it frequently as I am deciding when to schedule someone (which is why I need real time updates of it). We run the forecast from 6am-12am. There are 34 half hour intervals between 6 and midnight. Multiply that by 7 and you get 238.