In this chapter, I discuss a number of problems that you may encounter when dealing with data, along with possible solutions. I suggest that you try to find a solution before reading the solution in the text. Also, I should mention that there are usually many different ways of solving a given problem. In fact, you may very well be able to find a more efficient solution than the one given. The main purpose of these problems and solutions is to give you some food for thought.
Before beginning, let us note that many of the upcoming solutions involve the use of subqueries. We discussed subqueries in Chapter 6, but let us review quickly here.
Access SQL permits the use of
SELECT statements within other
SELECT statements (as well as in other
statements, such as
INTO statements). The internal, or nested,
SELECT statement is referred to as a
Note that you may include a nested
SELECT statement within a main
SELECT statement only if the internal
SELECT statement returns at most one
record. To illustrate, consider the main SQL
SELECT Hour, (SELECT Count(Interval) FROM StartTimes WHERE (StartTime <= Hour)) FROM Hours
Here, the internal SQL statement:
SELECT Count(Interval) FROM StartTimes WHERE (StartTime <= Hour)
returns at most a single record, because it returns a Count. Note
also that the
WHERE clause in the
internal SQL statement refers to the Hour field that is part of the main SQL, thus linking the return value ...