Some Common Data Manipulation Problems - Access Database Design and Programming - page 335

We want to compute the maximum number of workers that each supervisor must
supervise at one time.

19.3.1 Solution

This problem can be handled in a two-step process. First, we count the number of
workers in each hour. Consider the following SQL statement:

SELECT Hours.Hour,
(SELECT Count(EmpType) FROM SuperLoad
WHERE (Starthour <= Hours.Hour) And (Hours.Hour < EndHour)
And (EmpType='Worker')) AS CountOfWorkers
FROM Hours

This, again, uses a subquery that returns a single record giving the number of workers
that are working during a given hour.

Using this SQL statement, we make a query named qry1 , so it can be used in the next
step. See Table 19-5 for the result of this query.

Table 19-5. Number o f workers working at a particular hour
Hours

CountOfWorkers

1 2:00:00 PM 1

1 :00:00 PM 1

2 :00:00 PM 2

3 :00:00 PM 2

4 :00:00 PM 2

5 :00:00 PM 3

6 :00:00 PM 2

7 :00:00 PM 1

8 :00:00 PM 2

9 :00:00 PM 1

1 0:00:00 PM 1

1 1:00:00 PM 1

The next step is computing the supervisor load as the maximum number of workers in
each supervisor's shift. Do this by using the name of the query from the previous step in
the following SQL statement:

sSQL2 = "SELECT SuperLoad.EmpID, SuperLoad.EmpType," & _
" (SELECT Max(CountOfWorkers) AS WorkerLoad" & _
" FROM [" & qry1.Name & "]" & _
" WHERE ((Hours.Hour >= StartHour) And (Hours.Hour < Endhour)))" & _
" FROM SuperLoad" & _
" WHERE SuperLoad.EmpType = 'Super'"

The results are shown in Table 19-6.