FUNCTIONS
AVG
COUNT
FIRST
LAST
MAX
MIN
STDEV
STDEVP
SUM
VAR
VARP
STATEMENTS
ALTER TABLE
CREATE INDEX
CREATE TABLE
DELETE
DROP
INSERT INTO
SELECT
SELECT INTO
SELECT SUBQUERY
TRANSFORM
UPDATE
CLAUSES
CONSTRAINT
GROUP BY
HAVING
ORDER BY
WHERE
OPERATORS
BETWEEN ... AND ...
INNER JOIN
LEFT JOIN
LIKE
RIGHT JOIN
UNION
DECLARATIONS
PARAMETERS
WITH OWNERACCESS OPTION
OPERATION:
INNER JOIN
SELECT * | list FROM table1
INNER JOIN table2 ON table1.field1 compoperator
table2.field2
The INNER JOIN operation can be used in any FROM clause to
combine records from two tables. It is, in fact, the most common type of join.
There must be a matching value in a field common to both tables.
An INNER JOIN cannot be nested inside a LEFT JOIN or RIGHT
JOIN.
The following example returns a list of all employees who live in
SELECT Employee.Username
FROM Employee INNER JOIN Project
ON Employee.EmployeeID = Project.EmployeeID
WHERE Employee.City = '
AND Project.ProjectName = 'Hardwork';
Note that you can join any two numeric fields as long as they are of like type
(such as AutoNumber and Long). However, with non-numeric data, the fields must
be of the same type and contain the same kind of data, though they can have
different names.
With the INNER JOIN operation any relational comparison operator can be
used in the ON clause: =, <, >, <=, >=, or <>. The
following example returns all cases where the value in the EmployeeID field of
Employee matches that in the EmployeeID field of Project (i.e. it returns the
names of those employees working on each of the projects).
SELECT Employee.username, Project.ProjectName
FROM Employee INNER JOIN Project
ON Employee.EmployeeID = Project.EmployeeID;
...whereas this example returns all employees not working on each project:
SELECT Employee.Username, Project.ProjectName
FROM Employee INNER JOIN Project
ON Employee.EmployeeID <> Project.EmployeeID;
You can also link several clauses in a INNER JOIN statement: the
following example returns all employees working on each project who live in the
same city as where the project is taking place:
SELECT Employee.Username, Project.ProjectName,
Project.Location
FROM Employee INNER JOIN Project
ON (Employee.EmployeeID = Project.EmployeeID)
AND (Employee.City = Project.Location);
And you can also nest statements as in the following example which returns all
tunes recorded by musicians who are members of duos:
SELECT Tunes.Name, Musicians.Name, Duos.Name
FROM Tunes INNER JOIN (Musicians INNER JOIN Duos
ON (Musicians.Name = Duos.Member1)
OR (Musicians.Name = Duos.Member2))
ON Tunes.Musician = Musicians.Name;
An inner join can also be achieved by using the WHERE clause. The
following query returns the same set of records as the previous example:
SELECT Tunes.Name, Musicians.Name, Duos.Name
FROM Tunes, Musicians, Duos
WHERE ((Musicians.Name = Duos.Member1)
OR (Musicians.Name = Duos.Member2))
AND (Tunes.Musician = Musicians.Name);
Microsoft warns, "If you try to join fields containing Memo or OLE Object
data, an error will occur."
=========================================
OPERATION:
LEFT JOIN
FROM table1 LEFT JOIN table2
ON table1.field1 compopr table2.field2
The LEFT JOIN and the similar RIGHT JOIN operations can be used
in any FROM clause to combine records from two tables. The LEFT JOIN
operation is used to create a left outer join and includes all of the records
from the first (left) of the two tables, even if there are no matching values
for records in the second. The RIGHT JOIN operation is used to create a
right outer join and includes all of the records from the second (right) of the
two tables, even if there are no matching values for records in the first.
Although a LEFT JOIN or RIGHT JOIN operation can be nested inside
an INNER JOIN, the converse is not true. An INNER JOIN operation
cannot be nested inside a LEFT JOIN or RIGHT JOIN.
Using the tables 'Employee' and 'Project', the LEFT JOIN operation would
return the names of all employees whether or not they were currently engaged in
any project, but with project names where applicable:
SELECT Employee.username, Project.ProjectName
FROM Employee LEFT JOIN Project
ON Employee.EmployeeID = Project.EmployeeID;
Using the same tables, the RIGHT JOIN operation would return the names
of all projects, whether or not any of the employees were currently working on
them, but with employee names where applicable:
SELECT Employee.username, Project.ProjectName
FROM Employee RIGHT JOIN Project
ON Employee.EmployeeID = Project.EmployeeID;
The above two examples are in contrast to the INNER JOIN example which
only returns those records in which the data in the joined fields is the same. i.e. only records for employees currently engaged in
projects.
Microsoft warns, "If you try to join fields containing Memo or OLE Object
data, an error will occur."
OPERATION:
RIGHT JOIN
FROM table1 RIGHT JOIN table2
ON table1.field1 compopr table2.field2
The RIGHT JOIN and the similar LEFT JOIN operations can be used
in any FROM clause to combine records from two tables. The LEFT JOIN
operation is used to create a left outer join and includes all of the records
from the first (left) of the two tables, even if there are no matching values
for records in the second. The RIGHT JOIN operation is used to create a
right outer join and includes all of the records from the second (right) of the
two tables, even if there are no matching values for records in the first.
Although a LEFT JOIN or RIGHT JOIN operation can be nested inside
an INNER JOIN, the converse is not true. An INNER JOIN operation
cannot be nested inside a LEFT JOIN or RIGHT JOIN.
Using the tables 'Employee' and 'Project', the LEFT JOIN operation would
return the names of all employees whether or not they were currently engaged in
any project, but with project names where applicable:
SELECT Employee.username, Project.ProjectName
FROM Employee LEFT JOIN Project
ON Employee.EmployeeID = Project.EmployeeID;
Using the same tables, the RIGHT JOIN operation would return the names
of all projects, whether or not any of the employees were currently working on
them, but with employee names where applicable:
SELECT Employee.username, Project.ProjectName
FROM Employee RIGHT JOIN Project
ON Employee.EmployeeID = Project.EmployeeID;
The above two examples are in contrast to the INNER JOIN example which
only returns those records in which the data in the joined fields is the same. i.e. only records for employees currently engaged in
projects.
Microsoft warns, "If you try to join fields containing Memo or OLE Object
data, an error will occur."
OPERATION:
[TABLE] query1
[TABLE] query2
[
[TABLE] queryn [ ...
]]
[GROUP BY grouplist, [...]]
You use the UNION operation to merge the results from any combination of
two or more queries, or SELECT statements, or tables, into a single
table. The following example merges two queries that selected jigs and reels
respectively from a Tunes table, and tags the second onto the end of the first,
thus listing jigs and reels together:
TABLE Jigs
UNION ALL TABLE Reels;
All queries in a UNION operation must request the same number of fields,
though they don't have to be of the same size or data type. By default, the UNION
operation only returns unique records, but by using the ALL predicate
you ensure that all records are returned. This also makes the query run faster.
The next example takes certain selected fields (as opposed to table, which
takes all of them) from the Customers table and joins them onto the end of the
same number of selected fields from the Suppliers table:
SELECT Name,
City FROM Suppliers
WHERE Country = 'Mexico'
UNION SELECT Name, City FROM Customers
WHERE Country = 'Mexico';
You can use an ORDER BY clause at the end of the last query to specify
the order of the returned data:
SELECT Name,
City, 'Supplier' AS Source
FROM Suppliers
WHERE Country = 'Canada'
UNION SELECT Name, City, 'Customer'
FROM Customers
WHERE Country = 'Canada'
ORDER BY City, Source;
STATEMENT:
SELECT
SELECT [predicate] { *
| table.* |
[table.]field1 [AS alias1] [, table.]field2 [AS alias2] [, ...] ] }
FROM tableexpression [, ...] [IN
externaldatabase]
[WHERE... ] [NOT] [IN] (value1,[value2,[...]
] )
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]
[subqueryclause
[subqueryclause [...] ] ]
The SELECT statement returns information from a database as a set of
records without altering the database. It searches the database, extracts the
chosen columns and selects those records that meet the criteria, sorting and
grouping the results into a specified order.
A SELECT statement can be nested inside of another SELECT
statement which is nested inside of another SELECT and so on. When a SELECT
is nested it is refered to as a subquery clause.
At its simplest, you can use an asterisk ( * ) to select all of the
fields in a table (in this case the table 'Musicians' ):
SELECT * FROM Musicians;
Or you can be more selective and choose just one or a few of the fields in a
table, and they will be returned in the order listed:
SELECT MusicianName, Instrument FROM Musicians;
You can also have a query display a field under a different heading than the
one used in the table by establishing an "alias" with the reserved
word AS:
SELECT MusicianName AS Musician, Instrument
FROM Musicians;
...and you can even have a query combine two or more fields from a table into
one field in the returned list under a new heading using the ampersand
character:
SELECT Name, City & ", " & Country AS
Location
FROM Customers;
WHERE
You can use the WHERE clause to further focus your selection by
specifying certain criteria to be met by the values. The following example
returns the names of all musicians who play the flute:
SELECT MusicianName AS Musician FROM Musicians
WHERE Instrument = 'flute';
...and this example returns the names of all jigs in a 'Tunes' table:
SELECT TuneName AS Name, Source1 AS Recording FROM Tunes
WHERE TuneType = 'jig';
You can combine more than one criterion in a WHERE clause using any of
the logical operators. Here the query returns a list of all items which are
blue and cost less than $100:
SELECT Item, UnitPrice AS Price FROM Products
WHERE Color = 'blue' AND UnitPrice < 100;
The optional, reserved word IN can be used either as a clause or as an
operator.
If you want to get fields from a database other than the one you are currently
working in, you use the IN as a clause:
SELECT Name, Address
FROM PianoTuners IN USAPiano.mdb
WHERE state='TN';
If the database is a non-Microsoft Jet database, you must append a semicolon ( ; ) to the database file name and enclose it
within a pair of single ( ' ) or double quotes ( " ).
SELECT Name, Address
FROM PianoTuners IN "InstrumentTuners.xls;"
WHERE state='TN';
You can also specify a path and designate the type of
file. Note the position of the ( ; ):
SELECT Name, Address
FROM PianoTuners IN
"C:\Music\InstrumentTuners.xls" "Excel 5.0;"
WHERE state='TN';
When used as an operator, IN can determine if the values of a specified
expression matches any values in a specified list. This example determines if
any piano tuners live in '
SELECT * FROM
You can also add a NOT. This causes the query to select all values other
than those listed:
SELECT * FROM
The SELECT statement can optionally be followed by one of these four
predicates: ALL, DISTINCT, DISTINCTROW, TOP. These limit the number of
records returned.
The ALL predicate is the default, but it is rarely used. Note that the
following two code examples yield the exact same results:
SELECT *
FROM RockAndRoll
WHERE Artist = 'Elvis';
SELECT ALL *
FROM RockAndRoll
WHERE Artist = 'Elvis';
The DISTINCT predicate is used to omit duplicate values just in a field.
Consider a table of names, where you have the last name, "Jones",
repeated numerous times. This code returns only one "Jones":
SELECT DISTINCT LastName
FROM SongWriters;
The DISTINCTROW predicate is used to omit
duplicate values in an entire record of fields. This can be very useful when
you use a INNER JOIN to join two tables
together and you do not want any duplication. This code
create a table that does not repeat any of the last names:
SELECT DISTINCTROW LastName
FROM SongWriters INNER JOIN Performers
ORDER BY LastName;
The TOP predicate returns the specified number of records from the top
of the specified table. The following example returns the first 3 records:
SELECT TOP 3 MusicianName AS Musician, Instrument
FROM Musicians;
You can also carry out calculations on fields containing numeric values using
the aggregate functions:
AVG -
average
COUNT
- count how many items
MAX -
maximum value
MIN -
minimum value
STDEV
- sample standard deviation
STDEVP
- standard deviation
SUM -
add the values
VAR -
sample variance
VARP -
variance
This next example uses the COUNT function to count the number of items
that have an entry in the SalePrice field (i.e. they are on sale) and returns
that number under the heading 'ReducedCount':
SELECT COUNT(SalePrice) AS ReducedCount
FROM Products;
...and this next one returns current prices along with what the prices would be
after a 10% increase:
SELECT Item, UnitPrice AS CurrentPrice, UnitPrice * 1.1 AS
IncreasedPrice
FROM Products;
...and this one lists all items that are reduced along with the price and the
amount of the reduction:
SELECT Item, SalePrice AS Price, UnitPrice - SalePrice AS
Reduction
FROM Products
WHERE SalePrice <> Null;
Of course, you may want to select fields from more than one table, and you can
do that as well. In this case it is best to precede a field name with the name
of the table from which it comes, followed by the dot operator ( . ). You must do this for fields of the same
name, but from different tables that are used in the SELECT statement.
The following example uses two tables, Task and Assignment, and returns the
names of all Tasks belonging to Assignments that are incomplete:
SELECT Task.Name, Task.TaskID
FROM Task INNER JOIN Assignment
ON Task.TaskID = Assignment.TaskID
WHERE Assignment.CompletionDate Is Null;
As an alternative to using the explicit INNER JOIN syntax, columns from
multiple tables can be combined in a single query by specifying the appropriate
table list, and applying the filter condition in the WHERE clause. This
is illustrated in the following query, which returns the same recordset as the
previous example:
SELECT Task.Name, Task.TaskID
FROM Task, Assignment
WHERE Task.TaskID = Assignment.TaskID
AND Assignment.CompletionDate Is Null;
GROUP BY
The optional GROUP BY clause groups into a single record all records
that have identical values in a particular field or combination of fields. The
following example returns a list of the different products in the Product field
of Suppliers.
SELECT Product FROM Suppliers GROUP BY Product;
HAVING
The HAVING clause is optional and qualifies a GROUP
BY clause. It is similar to the WHERE clause but determines which
records are displayed after they have been grouped. The following example
displays a list of different items, along with their count, but only where
there are more than one.
SELECT Item, Count(Item) AS Tally FROM Products
GROUP BY Item HAVING Count(Item) > 1;
ORDER BY
The ORDER BY clause can be used to dictate the order of the records
returned. The following example returns records listed primarily in order of
tune type (jigs then reels), and then for each type the relevant names are also
listed in alphabetical order.
SELECT TuneType AS Type, Name FROM Tunes WHERE TuneType =
'jig' OR TuneType = 'reel'
ORDER BY TuneType, Name;
WITH OWNERACCESS
OPTION
In a multi-user environment utilizing secure workgroups, the WITH
OWNERACCESS OPTION declaration allows the query to be executed with the
same permissions as the owner of the query.
=========================================
CLAUSE:
GROUP BY
SELECT fieldlist FROM table WHERE criteria [GROUP
BY groupfieldlist] [HAVING search_criteria]
The optional GROUP BY clause combines into a single record all records
that have identical values in a particular field or combination of fields. You
can use up to 10 fields to group records, with the order of field names
determining the group levels from highest to lowest. A HAVING clause may
also be used in conjunction with a GROUP-BY clause to further restrict the search criteria. All fields containing a NULL
are considered to have a value and will be grouped along with the fields
containing non-NULL values. The following example returns a list of the
different products in the Product field of Suppliers:
SELECT Product FROM Suppliers GROUP BY Product;
You can also use any of the nine aggregate functions (AVG, COUNT, MIN, MAX,
STDEV, STDEVP, SUM, VAR, VARP) to include statisical values for each
record. Any field containing a NULL value will be ignored in the
statistical calculations performed by the aggregate functions. Expanding on the
previous example, the following returns a list of different products and a
field called ProdCount that counts the number of times each product occurs in
the Product field of Suppliers (i.e. how many suppliers supply it):
SELECT Product, COUNT(Product) AS
ProdCount FROM Suppliers
GROUP BY Product;
You can also include a WHERE clause to apply certain criteria before
values are grouped. The next example returns a list of all different products
that are blue, and the sum cost for one of each:
SELECT Item, Sum(UnitPrice) AS
TotalCost FROM Products
WHERE Color = 'blue'
GROUP BY Item;
If a field appears in the SELECT field list, it must appear in either
the GROUP BY clause or as an argument to one of the SQL aggregate
functions.
=======================================
STATEMENT:
CREATE TABLE
CREATE TABLE table (field1 type [(size)] [NOT
NULL] [index1]
[, field2 type [(size)]
[NOT NULL] [index2] [, ...]]
[, CONSTRAINT
multifieldindex [, ...]])
The CREATE TABLE statement is used to create a new table and its fields.
At its simplest you can create a table containing only a single field by
specifying the name you want to give the table, the field name and the type of
data you want the field to contain:
CREATE TABLE Names (Name TEXT);
You can, of course, include more than one field, and also limit the size of
those fields (Text and Binary fields only) by stating the size in parentheses
after the data type declaration:
CREATE TABLE Names (FirstName TEXT (20), LastName TEXT (20)
);
If you require that a particular field must always have valid data entered into
it, you can include the expression NOT NULL at the end of the
declaration for that field. If you do not enter the required data, you will get
a warning message:
CREATE TABLE Names (FirstName TEXT (20), LastName TEXT (20)
NOT NULL);
More often than not, you'll want to place some sort of restriction on the data,
or combinations of data, that are entered into fields.
You can do this by using the CONSTRAINT clause. The following example
expands on the previous ones by adding a Date of Birth field and requiring that
the combination of data in all three fields be unique:
CREATE TABLE Names (FirstName TEXT (20), LastName TEXT (20),
DateOfBirth DATETIME, CONSTRAINT MultiConstraint UNIQUE(FirstName,
LastName, DateOfBirth) );
Microsoft warns, "The Microsoft Jet database engine doesn't support the
use of any DDL statements with databases produced by any other database engine.
Use the DAO (Data Access Objects) Create methods instead."
=============================
STATEMENT:
ALTER TABLE
ALTER TABLE table
{ADD {COLUMN field type[(size)] [NOT NULL]
[CONSTRAINT index] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field | CONSTRAINT indexname} }
The ALTER TABLE statement can be used to alter an existing table by
adding or dropping columns and indexes.
You can use ADD COLUMN to add a single field to a table by specifying
the field name, data type and, optionally, a size for the Text and Binary
fields:
ALTER TABLE Sales ADD COLUMN UnitPrice CURRENCY;
You can add the reserved words NOT NULL to require valid data to be
added to that field:
ALTER TABLE Sales ADD COLUMN UnitPrice CURRENCY NOT NULL;
...and you can use DROP COLUMN to delete a single field :
ALTER TABLE Sales DROP COLUMN UnitPrice;
You can also define an index for a new field by using the CONSTRAINT
clause:
ALTER TABLE Sales ADD COLUMN Item TEXT CONSTRAINT
UniqueConstraint UNIQUE;
You can use ADD CONSTRAINT to add a multi-field index:
ALTER TABLE Names ADD CONSTRAINT UniqueValues UNIQUE
(FirstName, LastName);
...and DROP CONSTRAINT to remove a multi-field index:
ALTER TABLE Names DROP CONSTRAINT UniqueValues;
Microsoft warns, "The Microsoft Jet database engine doesn't support the
use of any DDL statements with databases produced by any other database engine.
Use the DAO (Data Access Objects) Create methods instead."
=================================
STATEMENT:
SELECT SUBQUERY
SELECT selectstatement
(SELECT selectstatement
(SELECT selectstatement
( ... ) ) )
)
When a SELECT statement is nested inside of a DELETE, INSERT ...
INTO, SELECT, SELECT ... INTO, or a UPDATE statement, it is refered
to as a subquery clause.
In this example we create a table of Medicare patients who also have dental
insurance. The social security number, ssn, is used as a key to compare the two
tables, MediCarePatients and DentalIns.
SELECT * FROM MediCarePatients
WHERE ssn IN
(SELECT ssn FROM DentalIns);
If we use a NOT, we create a table of Medicare patients who do not have
dental insurance:
SELECT * FROM MediCarePatients
WHERE ssn NOT IN
(SELECT ssn FROM DentalIns);
There are three reserved words, ALL, ANY, and SOME, that can be
used in subqueries to make comparisons. However, Microsoft states that ALL
and SOME are synonymous.
Consider the two following examples. The "< ALL" comparison
will create a list of AvgEggSize from BillsBirdNestList that contains only
those entries that are smaller than the smallest AvgEggSize value in
LindasBirdNestList.
In contrast, the "< ANY" comparison will create a list of
AvgEggSize from BillsBirdNestList that contains only those entries that are
smaller than the largest AvgEggSize value in LindasBirdNestList.
SELECT AvgEggSize FROM BillsBirdNestList WHERE AvgEggSize
< ALL (SELECT AvgEggSize FROM LindasBirdNestList);
SELECT AvgEggSize FROM BillsBirdNestList WHERE AvgEggSize
< ANY (SELECT AvgEggSize FROM LindasBirdNestList);
===================================
STATEMENT:
TRANSFORM
PARAMETERS expression]
TRANSFORM aggregate function
SELECT anyselectstatement
PIVOT pivotfield [IN (value1 [, value2
[, ...] ] ) ]
The optional TRANSFORM statement applies an aggregate function to a SELECT
statement and creates a crosstab query to display the results.
There are eight aggregate functions: AVG, COUNT, MAX, MIN, STDEV, STDEVP,
SUM, VAR, VARP. They are used to calculate
statistical information about a specific numeric field in a query.
A crosstab query is table that has both a row header and a column header. The
data generated by using the aggregate function is listed in the table under a
cross-reference between these row and column header. This is a convenient way
to display data in a compact, summarized format.
The PIVOT portion of the statement is used to determine the column
headers. You can use the returned values from pivotfield as headers. The
term "pivoting" signifies that a result table generates headers based
upon the selected values. For example, if you selected a specific period of
years over which to generate data, then your column headers would have a column
for each year. Or you can used the optional IN clause to create fixed
headers by declaring value1, value2, ...
The code example can be divided into four parts:
First, using an optional PARAMETERS declaration, the example displays a
dialog box asking the user to choose the year for which to return figures.
Next the TRANSFORM statement applies the SUM aggregate function.
Note that the TRANSFORM statement must always appear after any PARAMETER
statement and before the SELECT statement.
Third, comes the mandatory SELECT statement.
Finally, the query returns for the selected year a list of different
instruments with sales figures for each month (i.e., The PIVOT is
pivoting over months, therefore each month, for which sales occurred, has an
individual column):
PARAMETERS [Year?] Long;
TRANSFORM Sum([Shipping].Quantity *
[Shipping].UnitPrice) AS GrossIncome
SELECT PurchaseOrder.InstrumentName FROM PurchaseOrder
INNER JOIN (InstrumentCataloq INNER JOIN [Shipping]
ON InstrumentCatalog.CatNum = [Shipping].CatNum)
ON PurchaseOrder.OrderNum = [Shipping].OrderNum
WHERE DatePart("yyyy", PurchaseDate) = [Year?]
GROUP BY PurchaseOrder.InstrumentName
ORDER BY PurchaseOrder.InstrumentName
PIVOT DatePart("m",PurchaseDate);
A possible result table for the above example:
|
|
JAN |
MAR |
MAY |
JUN |
AUG |
SEP |
OCT |
NOV |
DEC |
|
drum |
87.50 |
0.00 |
0.00 |
175.00 |
0.00 |
350.00 |
350.00 |
175.00 |
350.00 |
|
cornet |
114.00 |
228.00 |
114.00 |
0.00 |
114.00 |
0.00 |
228.00 |
556.00 |
228.00 |
|
flute |
179.00 |
179.00 |
0.00 |
0.00 |
179.00 |
0.00 |
358.00 |
716.00 |
358.00 |
|
trumpet |
326.00 |
0.00 |
326.00 |
326.00 |
0.00 |
0.00 |
652.00 |
652.00 |
978.00 |
Note: For information about using "wildcard" characters, such as ?, with SQL, please read the Knowledge Base Article
A100219: SQL Wild Cards,
ADO and Microsoft Access.
===============================================
STATEMENT:
INSERT INTO
Single-record:
INSERT INTO target [(field1[, field2[, ...]])] VALUES
(value1[, value2 [, ...])
Multiple-record:
INSERT INTO target [IN externaldatabase] [(field1[, field2[,
...]])]
SELECT [source.] field1[, field2[, ...] FROM tableexpression
To add a specific record to a table, the INSERT INTO statement is
followed by the name of the table and the names of the various fields. If you
don't specify each field, a default value or NULL will be inserted for
missing columns. Each value in the VALUES clause is inserted into the
field that corresponds to the value's position in the list: the first value is
inserted into field 1, the second into field 2 etc.
INSERT INTO Musicians ( Name,
Instrument )
VALUES ('Bobby Lee', 'fiddle');
Note that if you omit the field list, you must include a value for every field
in the table, otherwise the operation will fail. The values must also be
seperated by commas, and text fields enclosed in single quotation marks (' ').
When using the multiple-record syntax the INSERT INTO statement precedes
a SELECT statement and adds a single or multiple records to a table.
This is referred to as an append query, as it copies records from one or more
tables to another. The tables that contain the records being appended are not
affected by the operation:
INSERT INTO Duos ( Member1 )
SELECT Name FROM Musicians;
An AutoNumber field (also refered to as a Counter field) is a data type that
automatically creates and stores a number for each item added to a table. If
you append records to a table with an AutoNumber field and you do not want the
AutoNumbered field to be renumbered, you must include the AutoNumber field
number in the query. If you do not include the AutoNumber field, the appended
items will be assigned a new AutoNumber field value.
If the destination table contains a PRIMARY KEY, you can only append
unique non-NULL values to the PRIMARY KEY field.
The following example would append to the Dous table, only those records that
had a unique primary key:
INSERT INTO Duos ( Member1 )
SELECT Name FROM Musicians
WHERE MusicianID > 3;
You can also append records to a table in another database using the IN clause:
INSERT INTO Residents (Name, Occupation) IN Residents.mdb
SELECT Name, Occupation FROM Immigration
WHERE Residency = 'granted';
As with a straight-forward SELECT statement, the FROM clause may
include more than one table linked by a JOIN operation. This is
illustrated in the following example which appends to the 'Insurance' table the
names of all those employees involved in the 'Hardwork' project:
INSERT INTO Insurance (Name)
SELECT Employee.Username FROM Employee INNER JOIN Project
ON Employee.EmployeeID = Project.EmployeeID
WHERE Project.ProjectName = 'Hardwork';
==================================