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

" And (W2.ModuleID = W1.ModuleID)" & _
" And (W2.PartID = W1.PartID)"

The second SQL statement returns all records whose TimeToCompletion is less than or
equal to all records returned in the first SQL statement—that is, all records for the given
WidgetID, ModuleID, and PartID:

' Those records that have minimum time to completion for each part
sSQL2 = "SELECT WidgetID, ModuleID, PartID," & _
" TimeToCompletion AS TimeToFinishPart FROM Widgets2 AS W1" & _
" WHERE TimeToCompletion <= ALL (" & sSQL1 & ")"

An alternative approach is to use a single nested SELECT statement:

sSQL2 = "SELECT DISTINCT WidgetID, ModuleID, PartID," & _
" (SELECT MIN(TimeToCompletion)" & _
" FROM Widgets2 as W2 WHERE" & _
" (W2.WidgetID = W1.WidgetID) And" & _
" (W2.ModuleID = W1.ModuleID) And" & _
" (W2.PartID = W1.PartID))" & _
" AS TimeToFinishPart" & _
" FROM Widgets2 AS W1"

Running this query

Set qry1 = db.CreateQueryDef("temp1", sSQL2)
DoCmd.OpenQuery qry1.Name

will result in Table 19-11.

Table 19-11 . Results table f or qry1

WidgetID ModuleID PartID TimeToFinishPart

1 1 1 3

1 1 2 1

1 2 1 3

1 2 2 3

1 2 3 4

1 3 1 5

1 3 2 2

Using this query, it is simple to get the time to completion for each module:

' Time to finish each module
sSQL3 = "SELECT WidgetID, ModuleID," & _
" Max(TimeToFinishPart) AS TimeToFinishModule FROM " & qry1.Name & _
" GROUP BY WidgetID, ModuleID"

Set qry2 = db.CreateQueryDef("temp2", sSQL3)

' Show it