Access Database Design and Programming, Second Edition

The DML Component of Access SQL

We now turn to the DML component of SQL. The commands we will consider are:

• SELECT

• UNION

• UPDATE

• DELETE

• INSERT INTO

• SELECT INTO

• TRANSFORM

• PARAMETER

Before getting to these statements, however, we must discuss a few relevant points.

Updatable Queries

In many situations, a query is updatable , meaning that we may edit the values in the result table and the changes are automatically reflected in the underlying tables. The details of when this is permitted are fairly involved, but they are completely detailed in the Access Help facility. (This information is not easy to find, however. You can locate it by entering “updatable query” in the Access Answer Wizard and choosing “Determine when I can update data from a query.”)

Joins

Let’s begin with a brief discussion of how Access SQL denotes joins. Note that a join clause is not an SQL statement by itself, but must be placed within an SQL statement.

Inner joins

The INNER JOIN clause in Access SQL actually denotes a θ-join on one or more columns. (See the discussion of joins in Chapter 5.) In particular, the syntax is:

                     Table1 INNER JOIN Table2 
 ON Table1.Column1 
                     θ
                     1 
                     Table2.Column1
 [{AND|OR ON Table1.Column2 
                     θ
                     2 
                     Table2.Column2},...]

where each θ is one of =, <, >, <=, >=, <> (not equal to).

Outer joins

The syntax for an outer join clause is:

                     Table1 {LEFT [OUTER]} | {RIGHT [OUTER]} JOIN Table2 
 ON Table1.Column1 
                     θ
                     1 
                     Table2.Column1
 [{AND|OR ON Table1.Column2 
                     θ
                     2 
                     Table2.Column2},...]

where is one of =, <, >, <=, >=, or < >. ...