monthly schedule

[AlbertJ]

My company is contracted to service a number of sites, we keep an excel sheet of the following information



- Site name
- Number of hours required to service each site

This is an extract of the table

SiteName	NumberOfHours
Site 1	1.005
Site 2	0.904
Site 3	0.502
Site 4	2.010

I want to create a query in access that will generate a monthly schedule for the service of the sites, so for each day we have 8 hours, after exhausting the 8 hours we move to the next day (more than one site can be serviced in a day or one site can take more than one day to be serviced depending on how many hours that site takes to be completed), so the first step is adding column of a running total for NumberOfHours column like so

SiteName	NumberOfHours	RunningTotal
Site 1	1.005	1.005
Site 2	0.904	1.909
Site 3	0.502	2.441
Site 4	2.010	4.421

I know how to add a running total in access (using a subquery), but where to go from here? how to turn the running total to the monthly calendar for the current month while skipping Sundays?

[June7]

Do you want to build a calendar style of output? This is not easy. Will require a lot of coding. Might be able to download some examples to see what is involved:

http://download.cnet.com/Microsoft-A...-77364272.html

http://download.cnet.com/Microsoft-A...-77364272.html

MS has a couple of templates categorized as Calendar but don't know what they really do: File > New > Calendars

[AlbertJ]

thanks for your replay June7,

am not really concerned about style, I only want the query to generate dates for the current month based on the number of hours, so for example if the first site takes 10 hours to finish then I want the adjacent cell to be 2nd of March for the current month and if the next site takes only one hour then it will be also 2nd of march :

site number one 10 hours - one day 8 hours = this site will not finish in the first day it will take two hours from the next day
site number two 2 hours = we have already consumed two hours from the previous day, but still we have 6 hours left, this site will take two hours, so we are still in the 2nd march

[June7]

I doubt what you want can be done solely with SQL.

Options:

1. manually determine how many days are required and enter records for the dates

2. lots of VBA code to do 1 or even just return date values to query (the latter is probably the harder approach)

How do you determine the start date?

[AlbertJ]

start date is always the beginning of the month

[ArviLaanemets]

Workdays only, or Saturdays and Sundays too?

State holidays are off?

You sayd 8 hrs per day. Arent there no exceptions? In some countries a workday before state holiday may be less than 8 hrs - are you sure this is not the case for you?

Has your company only one employee (8 hrs per day) or several? Have employee(s) a lunch time, and is the lunch time included in 8 hrs or not?

When service for one site is completed, then how do you count the time your employee need to arrive at next site? Is this time included into service time? When yes, then how much is minimal time left for starting the next service? (An example: 1st service was 7 hrs. The time to go to next site is 1 hr. When you put into schedule that on same day 1 hr of service must be done, then your employee arrives at site, and the workday is over!)

The number of workdays in month varies - and the number of work hours too. How is this taken into account?

All those questions indicate, to make such schedule automatic is a task nearly impossible.