Databases Reference
In-Depth Information
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 of the internal statement to the current record in the HOURS
table.
19.1 Running Sums
The computation of
running sums
is a common operation. To illustrate, consider Table
19-1, which contains the duration (in hours, say) for various events.
Tab
l
e 19-1. A running sum
Event
Duration
1
1
2
5
3
6
4
3
