One approach is to use the Cartesian product of the table with itself. In this way, we can access all records whose Event number precedes that of a given record. For instance, for the record with Event number 5, we need access to the records with Event numbers 1 through 4. The Cartesian product provides us with these records.

Here is the SQL statement that does the job:

SELECT Running.Event, Sum(RunningCopy.Duration) AS StartTime
FROM Running, Running AS RunningCopy
WHERE (RunningCopy.Event < Running.Event)
GROUP BY Running.Event

The FROM clause creates the Cartesian product of the table with itself. The WHERE clause restricts the records to those for which:

RunningCopy.Event < Running.Event

that is, to the records that provide information about the records preceding each record in Table 19-1. Finally, we GROUP BY Event and compute the sum of the durations.

The problem is that Cartesian products are very inefficient and use a lot of resources. (If Table 19-1 has 100,000 rows, then the Cartesian product has 100,000 x 100,000 = 10,000,000,000 rows!)

A more efficient solution is to use a nested SELECT statement, that is, to use a SELECT statement within the main SELECT statement. Recall that this is permitted in Access SQL, provided that the internal SELECT statement returns at most one record.

In the following SQL statement, note the use of table aliases, which are needed because we must refer to Table 19-1 in two contexts:

SELECT R1.Event,
(SELECT SUM(R2.Duration) FROM Running As R2 WHERE R2.Event < R1.Event)
   AS StartTime
FROM Running As R1

The internal SQL statement:

SELECT SUM(R2.Duration) FROM Running As R2 WHERE R2.Event < R1.Event

returns the sum of the duration for all events preceding the current event, which is denoted by R1.Event.

Example 19-1 shows a VBA procedure to execute this SQL statement. The DoCmd object is used in VBA to run an Access action. Thus, the line DoCmd.OpenQuery opens a query in Access.

Private Sub RunningSumSQL(  )

Dim db As Database
Set db = CurrentDb

Dim qry As QueryDef
Dim sSQL As String

On Error Resume Next
db.QueryDefs.Delete "temp"
On Error GoTo 0

sSQL = "SELECT R1.Event," & _
" (SELECT SUM(R2.Duration)" & _
" FROM Running As R2" & _
" WHERE R2.Event < R1.Event)" & _
" AS StartTime" & _
" FROM Running As R1"

Set qry = db.CreateQueryDef("temp", sSQL)

DoCmd.OpenQuery qry.Name

End Sub

Another approach is to use DAO, which provides a very simple solution in this case. It creates a permanent result table, whereas the previous solution creates a select query. Example 19-2 shows the DAO code performing the same operation. The results are placed in Table 19-1.

Private Sub RunningSumDAO(  )

Dim db As Database
Dim rs As Recordset
Dim lRunningSum As Long

Set db = CurrentDb

lRunningSum = 0

Set rs = db.OpenRecordset("SELECT * FROM Running ORDER BY Event")
Do While Not rs.EOF
   rs.Edit
   rs!RunningSum = lRunningSum
   rs.Update
   lRunningSum = lRunningSum + rs!Duration
   rs.MoveNext
Loop

rs.Close

End Sub

This problem can be solved using a nested SELECT statement (that is, a subquery). Here is the code:

Private Sub OverlappingIntervals(  )

Dim db As Database
Set db = CurrentDb

Dim qry As QueryDef
Dim sSQL As String

On Error Resume Next
db.QueryDefs.Delete "temp"
On Error GoTo 0

sSQL = "SELECT Hours.Hour," & _
" (SELECT Count(Interval) AS CountOfIntervals" & _
" FROM Overlap" & _
" WHERE (StartTime <= Hours.Hour) And" & _
" (Hours.Hour < EndTime))" & _
" FROM Hours"

Set qry = db.CreateQueryDef("temp", sSQL)

DoCmd.OpenQuery qry.Name

End Sub

We invite you to create a DAO solution. The problem in the next section illustrates the overlapping intervals technique.

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.

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.

The complete code for this solution is in Example 19-3.

Private Sub SupervisorLoad(  )

Dim db As Database
Set db = CurrentDb

Dim qry1 As QueryDef
Dim qry2 As QueryDef
Dim sSQL1 As String
Dim sSQL2 As String

On Error Resume Next
db.QueryDefs.Delete "temp1"
db.QueryDefs.Delete "temp2"
On Error GoTo 0

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

Set qry1 = db.CreateQueryDef("temp1", sSQL1)

' Uncomment to see how this step looks
'DoCmd.OpenQuery qry1.Name

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'"

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

DoCmd.OpenQuery qry2.Name

End Sub

This problem can be solved in a variety of ways, one of which provides a nice use of both subqueries and the UNION statement. First, consider the SQL statement:

sSQL1 = "SELECT Room FROM Assignment WHERE (Name = [Enter Name])"

Recall that [Enter name] is a parameter. When the query is run, the user will be prompted for a name, which will be substituted for [Enter name]. This statement will return the record associated with a given name if it is in the table; otherwise, it will return the empty recordset.

Now consider the statement:

sSQL2 = "SELECT Room FROM Assignment
         WHERE (Name = '_default') AND
         ([Enter Name] NOT IN (SELECT Name FROM Assignment))"

The clause:

[Enter Name] NOT IN (SELECT Name FROM Assignment)

returns TRUE if and only if the name entered by the user is not in the table. Hence, the clause sSQL2 can be rewritten based on two cases, name in table:

sSQL2 = "SELECT Room FROM Assignment WHERE (Name = '_default') AND FALSE"

and name not in table:

sSQL1 = "SELECT Room FROM Assignment WHERE (Name = '_default') AND TRUE"

This simplifies further to name in table:

sSQL2 = "SELECT Room FROM Assignment WHERE FALSE"

and name not in table:

sSQL1 = "SELECT Room FROM Assignment WHERE (Name = '_default')"

Thus, this statement returns the empty recordset if the name is in the table and the default records if the name is not in the table.

Now we take the union:

sSQL3 = sSQL1 & " UNION " & sSQL2

This SQL statement will return the room number for a name if the name is in the table; otherwise, it returns the default records.

Now, all we need to do is return a random record! Note that this will work in either case, because if the name is in the table, there is only one record, so a randomly chosen record must be that record.

The complete code is shown in Example 19-4.

Private Sub AssignmentWithDefault(  )

Dim db As Database
Set db = CurrentDb

Dim sName As String
Dim qry1 As QueryDef
Dim rs As Recordset
Dim sSQL1 As String
Dim sSQL2 As String
Dim sSQL3 As String

Dim lRandom As Long
Dim lcRecords As Long

On Error Resume Next
db.QueryDefs.Delete "temp1"
On Error GoTo 0

sSQL1 = "SELECT Room FROM Assignment" & _
" WHERE (Name = [Enter Name])"

sSQL2 = "SELECT Room FROM Assignment" & _
" WHERE (Name = '_default') AND ([Enter Name] NOT IN (SELECT Name FROM Assignment))"

sSQL3 = sSQL1 & " UNION " & sSQL2

Set qry1 = db.CreateQueryDef("temp1", sSQL3)

sName = InputBox("Enter name")
qry1.Parameters(0) = sName

' To see the results
''DoCmd.OpenQuery qry1.Name

Set rs = qry1.OpenRecordset

' Populate and get recordcount
rs.MoveLast
lcRecords = rs.RecordCount

' Random record
Randomize Timer
' lRandom is between 0 and lcRecords-1
lRandom = Int(lcRecords * Rnd)

rs.MoveFirst
rs.Move lRandom

MsgBox "Room for " & sName & " is " & rs!Room

End Sub

The next SQL statement does the job. Note the use of the NOT IN form of subquery, which is discussed in Section 6.7.10 in Chapter 6.

SELECT DISTINCT WidgetID FROM Widgets As W1
WHERE 0 NOT IN
(SELECT TimeToCompletion FROM Widgets As W2
WHERE W2.WidgetID = W1.WidgetID)

Consider the following SQL statement:

SELECT WidgetID 
FROM Widgets AS W1
WHERE (TimeToCompletion = 0) AND
0 NOT IN
(SELECT TimeToCompletion FROM Widgets AS W2 
WHERE (W2.WidgetID=W1.WidgetID) AND (W2.ModuleID <> 1))

The subquery selects, for a particular widget, all TimeToCompletions for all modules except the first module. We can then test to see if that set of TimeToCompletions contains a zero.

Example 19-5 shows the complete code.

Private Sub TimeToCompletion(  )

Dim db As Database
Set db = CurrentDb

Dim qry1 As QueryDef
Dim sSQL1 As String

On Error Resume Next
db.QueryDefs.Delete "temp1"
On Error GoTo 0

sSQL1 = "SELECT WidgetID FROM Widgets AS W1" & _
" WHERE (TimeToCompletion = 0) AND" & _
" 0 NOT IN" & _
" (SELECT TimeToCompletion FROM Widgets AS W2" & _
" WHERE (W2.WidgetID=W1.WidgetID) AND (W2.ModuleID <> 1))"

Set qry1 = db.CreateQueryDef("temp1", sSQL1)

DoCmd.OpenQuery qry1.Name

End Sub

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)" & _
" 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.

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
DoCmd.OpenQuery qry2.Name

This query results in Table 19-12.

Finally, we can compute the time to completion for each widget:

' Time to finish each Widget
sSQL4 = "SELECT WidgetID," & _
" Max(TimeToFinishModule) AS TimeToFinishWidget FROM " & qry2.Name & _
" GROUP BY WidgetID"

Set qry3 = db.CreateQueryDef("temp3", sSQL4)

This results in Table 19-13.

It is possible to get the time to completion in a single SQL statement, although I definitely do not recommend doing so. The result may be more elegant, but it is also harder to read. For instance, for modules, we have:

sSQL1 = "SELECT DISTINCT WidgetID, ModuleID," & _
" TimeToCompletion FROM Widgets2 AS W1" & _
" WHERE TimeToCompletion =" & _
"    (SELECT MAX(TimeToCompletion) FROM Widgets2 As W2" & _
"     WHERE TimeToCompletion =" & _
"       (SELECT MIN(TimeToCompletion) FROM Widgets2 AS W3" & _
"       WHERE (W3.WidgetID = W2.WidgetID)" & _
"              And (W3.ModuleID = W2.ModuleID)" & _
"              And (W3.PartID = W2.PartID)" & _
"       Group BY W3.WidgetID, W3.ModuleID, W3.PartID)" & _
"     AND (W2.WidgetID = W1.WidgetID) And (W2.ModuleID = W1.ModuleID)" & _
"     GROUP BY W2.WidgetID, W2.ModuleID)"

Digesting this SQL statement will probably take time, and I hope it will make you reconsider using such a statement in your own applications.

One solution is given by the following SQL statement:

SELECT DISTINCT Composers.EmpID, Name,
 (SELECT Value FROM ComposerData As T2 WHERE
   (T2.StatType='Age') And (T2.EmpID=Composers.EmpID)) As Age,
 (SELECT Value FROM ComposerData As T2 WHERE
   (T2.StatType='Height') And (T2.EmpID=Composers.EmpID)) As Height,
 (SELECT Value FROM ComposerData As T2 WHERE
   (T2.StatType='Weight') And (T2.EmpID=Composers.EmpID)) As Weight
FROM (Composers INNER JOIN ComposerData
ON Composers.EmpID=ComposerData.EmpID)

Here, we have multiple SELECT subquery statements within the main SELECT clause. For instance, the clause:

(SELECT Value FROM ComposerData As T2 WHERE
   (T2.StatType='Age') And (T2.EmpID=Composers.EmpID)) As Age,

selects the age for the person selected by the main SELECT clause.

As the number of statistics grows, this SQL statement becomes more complex. Example 19-6 shows an alternative solution using DAO that does not require adjusting when additional statistics are added.

Private Sub VerticalToHorizontal2(  )

Dim db As Database
Set db = CurrentDb

Dim rsEmp As Recordset
Dim rsData As Recordset
Dim rsHor As Recordset

Set rsEmp = db.OpenRecordset("Composers")
Set rsHor = db.OpenRecordset("ComposersOutput")

Do While Not rsEmp.EOF

   Set rsData = db.OpenRecordset( _
      "SELECT * FROM ComposerData WHERE EmpID = " & rsEmp!EmpID)
   rsHor.AddNew
   rsHor!EmpID = rsEmp!EmpID
   rsHor!Name = rsEmp!Name
   Do While Not rsData.EOF
      rsHor.Fields(rsData!StatType).Value = rsData!Value
      rsData.MoveNext
   Loop
   rsHor.Update

   rsEmp.MoveNext
Loop

rsEmp.Close
rsData.Close
rsHor.Close

End Sub

One solution is given by the following SQL statement:

SELECT ProgrammingJobs.JobID, Programmers.Name
FROM Programmers INNER JOIN ProgrammingJobs
ON Programmers.Language = ProgrammingJobs.Language
GROUP BY ProgrammingJobs.JobID, Programmers.Name
HAVING Count(Programmers.Language)=
(SELECT Count([Language]) FROM ProgrammingJobs AS PJ
WHERE PJ.JobID=ProgrammingJobs.JobID)

We begin with an INNER JOIN of the two tables on the Language field. For each job/programmer pair, this INNER JOIN creates a set of records of the form:

JobID X - Language 1 - ProgrammerName Y
JobID X - Language 2 - ProgrammerName Y
JobID X - Language 3 - ProgrammerName Y 
. . .

where the job requires the language, and the programmer is skilled in that language.

Now, for each job/programmer pair, we need to ensure that the number of such records is the same as the number of languages required by that job. This is accomplished by grouping the records by job/programmer pair and then using a HAVING clause that compares a count of those records with the count of languages for that job. The resulting table is Table 19-19.

This problem has an elegant solution using a single SQL statement. While, in general, SQL does not permit us to compare two sets directly, as in:

(SELECT Members FROM Equality WHERE Set=1) = (SELECT Members FROM Equality WHERE _
  Set=2)

it will accept such clauses if the two SELECT statements return a single value.

Consider now the SQL statement:

SELECT Equality.Set, E2.Set
FROM Equality INNER JOIN Equality AS E2 ON
 (Equality.Member = E2.Member) And (Equality.Set < E2.Set)
GROUP BY Equality.Set, E2.Set
HAVING 
((SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=Equality.Set) = 
 (SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=E2.Set))
AND
(Count(Equality.Set) = 
 (SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=E2.Set))

The INNER JOIN is on the clause:

(Equality.Member = E2.Member) And (Equality.Set < E2.Set)

The important part of this clause is the first part. It states that we want all set pairs that have a common member. The second part prevents returning duplicate set pairs. For instance, if sets 1 and 2 both contain the number 3, we don’t want to return both pairs [(1,2) and (2,1)].

To illustrate further, since the number 3 is in sets 1, 2, 3, and 4, the records returned for the member 3 are as follows:

(1,2) (from member 3)
(1,3) (from member 3)
(1,4) (from member 3)
(2,3) (from member 3)
(2,4) (from member 3)
(3,4) (from member 3)

If it were not for the clause Equality.Set < E2.Set, we would also be getting (1,1), (2,2), ... (4,4), as well as (2,1), (3,1), and so on.

Now we ask the question, “How many times will a given set pair appear?” A given set pair, say (1,2), will appear as many times as there are common elements between the two sets. That is, it will appear as many times as the size of the intersection of the two sets.

So if we GROUP BY set pair, we can examine these intersections and restrict the returns using a HAVING clause. The HAVING clause we want says that the two sets are equal. But two sets A and B are equal if the sizes of A, B, and the intersection of A and B are all the same! The clause:

(SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=Equality.Set) = 
 (SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=E2.Set)

says that, for a given set pair (Equality.Set, E2.Set) from the main SELECT clause, the size of Equality.Set is equal to the size of E2.Set. The clause:

Count(Equality.Set) = 
 (SELECT Count(Member) FROM Equality As E3 WHERE E3.Set=E2.Set)

says that the size of the intersection of Equality.Set and E2.Set is the same as the size of E2.Set. That’s it.