Access Database Design & Programming, 3rd Edition

Chapter 19. Some Common Data Manipulation Problems

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 INSERT INTO statements). The internal, or nested, SELECT statement is referred to as a subquery.

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 statement:

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 ...