in need of advice 1

[krzysrb]

hello guys

i need your opinion on the problem i have. i have a db table that contains start time for a shift, end time for a shift, the day that the worker wants to work that particular shift, as well as their name.

i.e
Joe Monday 8:00 13:00

the work begins at 8am and ends at 11pm, and the schedule is made on half hour intervals, someone can work only from 8:00 til 8:30, 10:30 til 13:30, and such.

the schdule i am trying to make would be something like this:
Mon Tue Wed ...
8:00-8:30 Joe
8:30-9:00 Joe
...
22:30-23:00 ...

i would like to hear what would you think the most efficient way to solve this would be. i have been trying to do it with arrays, i failed; i tried sql, i never got around to finish it. maybe you can suggest some ideas, as i am willing to listen.

thanks a lot in advance
vlada

 

[BlackburnKL]

You can use an array. Give yourself enough rows to handle the time intervals you want and enough columns to handle the days on your schedule. I also like to use row 0 and column zero for headers. I recommend making it flexible (using variables) so that it can handle change.

First, figure out how many days you want on the schedule. This could be accomplished by user data entry. Use DateDiff to determine the number of days between the starting date and the ending date. Be sure to add 1 to the difference. Store the number of days into a variable (lngDaysInSchedule).

Second, figure out how many time intervals you want on the schedule. This can be calculated in much the same way as the dates. Find out how many hours there are between the starting time and the ending time (15 in your example). You don't have to add 1 because you don't work the last hour (but you do work the last day). Multiply the hours by the number of time intervals per hour (2 in your example). Store the result into a variable (lngTimeIntervalsInSchedule).

Then create your array:

Dim astrWorkSchedule(0 to lngDaysInSchedule,0 to lngTimeIntervalsInSchedule) As Variant

Fill in the headers using For/Next loops:

For i = 1 to lngDaysInSchedule
astrWorkSchedule(i,0) = DateAdd("D",datStartDay,i - 1)
Next i

The time intervals are a little trickier, but work in much the same way (except the i should be the second number rather than the first).

To fill in the data, I would pull the records from the database into a recordset. Select only those records which correspond to the date interval you want on the schedule. I would loop through the recordset, seeking the corresponding date (in astrWorkSchedule(i,0) by using a For/Next loop. I would then use another For/Next loop (using j instead of i because you need i) to loop through the time intervals. If the time interval falls within the range specified in the record, I would do the following:

astrWorkSchedule(i,j) = rsRecordset!Name

If there is the possibility that multiple people could be assigned the same time interval, I would test to see if the cell in the array is empty. If so, use the command above. If not, use this command:

astrWorkSchedule(i,j) = astrWorkSchedule(i,j) & vbCrLf & rsRecordset!Name

That places the name of the current employee you are looking at on the next line, below any others listed there already.

You can then use your array to report/print the schedule.

Good luck.



BlackburnKL

 

[krzysrb]

Thanks a lot BlackburnKL,

Your advice just crystalized what I had originally intented to do, and for that * goes to you. Thanks a lot again, and I am already making a progress to get it all done and working.

vlada

 

[CCLINT]

krzysrb, would you mind try giving BlackburnKL the star again? For some reason it didn't take (or at least I do not see it yet for some reason)

 

[krzysrb]

hope it went this time )
thanks for letting me know CCLINT

vlada