Databases Reference
In-Depth Information
19.7 Time to Completion III—A MaxMin Problem
Let's make the time-to-completion problem even more involved. Suppose each module is
composed of several parts. Periodically, the workers involved with a particular part will
make an entry into a database table, as shown in Table 19-10.
Table 19-10. Widgets table: Time to completion of a widget whose modules consist of
multiple parts
WidgetID
ModuleID
PartID
TimeToCompletion
1
1
1
3
1
1
1
4
1
1
1
5
1
1
2
2
1
1
2
4
1
1
2
1
1
2
1
6
1
2
1
5
1
2
1
3
1
2
2
7
1
2
2
4
1
2
2
3
1
2
3
4
1
2
3
5
1
2
3
6
1
3
1
8
1
3
1
5
1
3
2
2
1
3
2
4
We want to compute the time to completion for each part, module, and widget. Note that
there may be several entries for a given part. The time to complete a given part is the
minimum of the times in these rows.
19.7.1 Solution 1
Let's take a step-by-step approach to the solution. Later, we can present a more elegant,
but less readable, solution.
First, we create an SQL statement that returns only those rows of the table that, for each
widget/module, have the smallest part TimeToCompletion. We can do this in two steps.
The first SQL statement selects the TimeToCompletion field for all records in Widgets2
that have a given WidgetID, ModuleID, and PartID.
' Times to completion for given WidgetID/ModuleID/PartID
sSQL1 = "SELECT TimeToCompletion FROM Widgets2 AS W2" & _
" WHERE (W2.WidgetID = W1.WidgetID)" & _
