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
Boston and who are working on the Hardwork project:
 
SELECT Employee.Username
FROM Employee INNER JOIN Project
ON Employee.EmployeeID = Project.EmployeeID
WHERE Employee.City = 'Boston'
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:  UNION


[TABLE] query1
UNION [ALL]
[TABLE] query2
[UNION [ALL]
[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 '
Knoxville', 'Nashville', or 'Memphis'. A pair of single quotes must enclose each value and commas must separate each value:
 
SELECT * FROM TennPianoTuners
WHERE City
IN ( 'Knoxville', 'Nashville', 'Memphis' );
 
You can also add a NOT. This causes the query to select all values other than those listed:
 
SELECT * FROM TennPianoTuners
WHERE City
NOT IN ( 'Knoxville', 'Nashville', 'Memphis' );
 
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';

 

==================================