Some Notes on Conventions Used in this Document

Syntax rules for using SQL statements in Microsoft Access 97® are given in this document in table form, where the background of the table is white. These rules are important. And, if you violate one or more of them, you can be assured that your query will fail to execute. This section is concerned with helping you understand how to translate the syntax of an SQL query into executable code. The following table illustrates the method employed in these notes to define the syntax of SQL as implemented within Access 97®

ALTER TABLE -- Syntax

ALTER TABLE table           {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |                CONSTRAINT multifieldindex} |            DROP {COLUMN field | CONSTRAINTindexname} }

1. Upper case items in BOLDFACE are required reserved words. If they are misspelled, this will result in the generation of a syntax error whenever you try to execute a query.

2. Lower case items in italics stand for elements whose names you must provide. Needless to say, these items must exist within the database you are using.

3. Blank Spaces matter. If you attempt to execute a query that starts with the 10 characters ALTERTABLE, this will generate an error message.

4. Extra blank spaces are okay and may be beneficial if they make it easier for someone to understand your query.

5. Carriage Return/Line Feeds can also be used to improve understanding of your code.

6. Combining extra blank spaces with Carriage Return/Line Feeds is strongly recommended. For example, the following two queries will produce identical results. However, the second one is probably easier for someone to understand.

            SELECT * FROM employee WHERE position IN ("12", "22", "30");

            SELECT *
                FROM employee
                WHERE position IN ("12", "22", "30");

7. Items enclosed within the characters [   ] (square brackets) are optional. However, if you choose to use them, they must be used exactly as shown. For example, should you choose to include the size of a field type, then it must be enclosed within the characters (   ). If you omit the parentheses, this will generate an execution error.

8. Whenever you encounter items enclosed within the characters {    } (braces), you are expected to choose exactly one of several alternatives. The alternatives are delineated by the character | -- the "pipe" character. Thus, in the above table, the syntax allows you to select one of four alternatives when you are altering a table:

   1. You may add a column to a table, or

   2. You may add a multi field constraint to a table, or

   3. You may drop a column from a table, or

   4. You may drop a constraint from a table.

Putting this all together, the outermost pair of {    } requires that you select either the key word ADD or the key word DROP. (Remember, when given a choice, you must select exactly one of the alternatives provided.) Assuming you choose ADD, then the next item must be either COLUMN or CONSTRAINT. Which you choose depends on what you wish to accomplish with your query.

The following are examples of syntactically correct ALTER TABLE queries.

         ALTER TABLE employees
             & nbsp; ADD COLUMN teamplayer YESNO;

         ALTER TABLE employees
             & nbsp; ADD COLUMN middleinitial TEXT (1) NOT NULL;

         ALTER TABLE employees
             & nbsp; ADD CONSTRAINT one_key UNIQUE (lastname, firstname, employee_id);

  Back to Table of Contents

Access 97® Idiosyncracies

In reading the following, you should be aware of some idiosyncracies peculiar to ACCESS 97®.

• The not equal to operator in SQL is <>. Using != results in a syntax error even though != is acceptable under the formal SQL definition and is found in numerous references.
• ACCESS 97® allows you to create tables and fields whose names include the blank space character. While you are unlikely to create names with embedded blank spaces if you use the SQL statement CREATE TABLE to create a table, it is very easy to do in the Design View. For example, assume you have created a table named ORDERS with the following structure.

ORDERS table
Part Id	Customer Id	Date Ordered	Quantity	Date Shipped

where several fields contain a blank space in their names. The following SELECT will generate a syntax error because of the blank space in the field name Part Id.

          SELECT Orders.Part Id
            FROM Orders;

It is necessary to enclose names containing blank spaces in the characters [ and ] to avoid this syntax error. Thus,

          SELECT Orders.[Part Id]
            FROM Orders;

is syntactically correct and will execute as you would expect.

• To create an SQL query in ACCESS 97®, Click on the database tab Queries. Select the New button. Click on OK to go to Design View. Click Close on the Show Table form. Select the menu command View and then finally SQL View. This puts you into a full screen text editor where you may compose SQL queries. Once you have composed the query, you may select the menu command View and choose the option Datasheet View. You will either see the result of executing the query or be told there is a syntax error in your SQL statement, whichever is appropriate. When you attempt to close the query, you will be asked whether you wish to save it. And, if you do, what to name the query. From now on, if you click on the named query in the Queries page of your database and then select the Design button, you will start out in the SQL editor View with the saved version of your query displayed.

  Back to Table of Contents
 

AGGREGATE FUNCTIONS

The following table presents some of the aggregate functions supported by Microsoft ACCESS 97®. When you use these, be aware that not every function is defined on every data type supported by ACCESS 97®. (i.e. Pay attention to the data type restrictions shown in the third column of the table.)

Function	Purpose	Applicable Data Types
Sum	Total of the values in a field.	Number, Date/Time, Currency, and AutoNumber
Avg	Average of the values in a field.	Number, Date/Time, Currency, and AutoNumber
Min	Lowest value in a field. Text,	Number, Date/Time, Currency, and AutoNumber
Max	Highest value in a field.	Text, Number, Date/Time, Currency, and AutoNumber
Count	Number of values in a field, not counting Null (blank) values.	Text, Memo, Number, Date/Time, Currency, AutoNumber, Yes/No, and OLE Object
StDev	Standard deviation of the values in a field.	Number, Date/Time, Currency, and AutoNumber
Var	Variance of the values in a field.	Number, Date/Time, Currency, and AutoNumber

For example, you could use the following SELECT statement to determine the highest paid employee in the organization:

      SELECT MAX(salary)
          FROM employees

Or, to find the highest and average salaries in each department, you could use:

       SELECT department, MAX(salary), AVG(Salary)
          FROM employees
          GROUP BY department

Back to Table of Contents
 

ALTER TABLE

The ALTER TABLE Statement Modifies the design of a table after it has been created with the SQL CREATE TABLE statement or using the ACCESS 97® Design View.

ALTER TABLE -- Syntax

ALTER TABLE table           {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |                CONSTRAINT multifieldindex} |            DROP {COLUMN field | CONSTRAINTindexname} }

The ALTER TABLE statement has these parts:

Part	Description
table	The name of the table to be altered.
field	The name of the field to be added to or deleted from table.
type	The data type of field.
size	The field size in characters (Text and Binary fields only).
index	The index for field. See the CONSTRAINT clause topic for more information on how to construct this index.
multifieldindex	The definition of a multiple-field index to be added to table. See the CONSTRAINT clause topic for more information on how to construct this clause.
indexname	The name of the multiple-field index to be removed.

Remarks:

Using the ALTER TABLE statement, you can alter an existing table in several ways. You can:

Use ADD COLUMN to add a new field to the table. You specify the field name, data type, and (for Text and Binary fields) an optional size. For example, the following statement adds a 25-character Text field called Notes to the Employees table and requires that it never be assigned a null value:

       ALTER TABLE Employees
            ADD COLUMN Notes TEXT(25) NOT NULL

You can also define an index on that field. For more information on single-field indexes, see the CONSTRAINT clause topic.

Use ADD CONSTRAINT to add a multiple-field index. For more information on multiple-field indexes, see the CONSTRAINT clause topic.

Use DROP COLUMN to delete a field. You specify only the name of the field.

Use DROP CONSTRAINT to delete a multiple-field index. You specify only the index name following the CONSTRAINT reserved word.

Notes:

• You can't add or delete more than one field or index at a time.. You can use the CREATE INDEX statement to add a single- or multiple-field index to a table, and you can use ALTER TABLE or the DROP statement to delete an index created with ALTER TABLE or CREATE INDEX.
• You can use NOT NULL on a single field, or within a named CONSTRAINT clause that applies to either a single field or to a multiple-field named CONSTRAINT. However, you can apply the NOT NULL restriction only once to a field, or a run-time error occurs.

Back to Table of Contents

Between...And Operator

The Between...And operator is used to determine whether the value of an expression falls within a specified range of values. You can use this operator within SQL statements.

Between ... And -- Syntax

expr [Not] Between value1 And value2

The Between...And operator syntax has these parts:

Part	Description
expr	Expression identifying the field that contains the data you want to evaluate.
value1, value2	Expressions against which you want to evaluate expr.

Remarks:

If the value of expr is between value1 and value2 (inclusive), the Between...And operator returns True; otherwise, it returns False. You can include the Not logical operator to evaluate the opposite condition (that is, whether expr lies outside the range defined by value1 and value2).

You might use Between...And to determine whether the value of a field falls within a specified numeric range. The following example determines whether an order was shipped to a location within a range of postal codes. If the postal code is between 98101 and 98199, the IIF function returns "Local". Otherwise, it returns "Nonlocal".

       SELECT IIF(PostalCode Between 98101 And 98199, "Local", "Nonlocal")
            FROM Publishers

If expr, value1, or value2 is Null, Between...And returns a Null value.

Because wildcard characters, such as *, are treated as literals, you cannot use them with the Between...And operator. For example, you cannot use 980* and 989* to find all postal codes that start with 980 to 989. Instead, you have two alternatives for accomplishing this. You can add an expression to the query that takes the left three characters of the text field and use Between...And on those characters. Or you can pad the high and low values with extra characters _ in this case, 98000 to 98999, or 98000 to 98999 _ 9999 if using extended postal codes. (You must omit the _ 0000 from the low values because otherwise 98000 is dropped if some postal codes have extended sections and others do not.)
 

Back to Table of Contents
 

CONSTRAINT Clause

A constraint is similar to an index, although it can also be used to establish a relationship with another table. You use the CONSTRAINT clause in ALTER TABLE and CREATE TABLE statements to create or delete constraints. There are two types of CONSTRAINT clauses: one for creating a constraint on a single field and one for creating a constraint on more than one field.
 

Single-field constraint -- Syntax

CONSTRAINT name         {PRIMARY KEY |            UNIQUE            NOT NULL |            REFERENCES foreigntable [(foreignfield1, foreignfield2)]}

Multiple-field constraint -- Syntax

CONSTRAINT name          {PRIMARY KEY (primary1[, primary2 [, ...]]) |            UNIQUE (unique1[, unique2 [, ...]]) |             NOT NULL (notnull1[, notnull2 [, ...]]) |             FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES foreigntable                    [(foreignfield1 [, foreignfield2 [, ...]])]}

The CONSTRAINT clause has these parts:

Part	Description
name	The name of the constraint to be created.
primary1, primary2	The name of the field or fields to be designated the PRIMARY KEY.
unique1, unique2	The name of the field or fields to be designated as a unique key.
notnull1, notnull2	The name of the field or fields that are restricted to non-Null values.
ref1, ref2	The name of a FOREIGN KEY field or fields that refer to fields in another table.
foreigntable	The name of the foreign table containing the field or fields specified by foreignfield.
foreignfield1, foreignfield2	The name of the field or fields in foreigntable specified by ref1, ref2. You can omit this clause if the referenced field is the PRIMARY KEY of foreigntable.

Remarks:

You use the syntax for a single-field constraint in the field-definition clause of an ALTER TABLE or CREATE TABLE statement immediately following the specification of the field's data type. You use the syntax for a multiple-field constraint whenever you use the reserved word CONSTRAINT outside a field-definition clause in an ALTER TABLE or CREATE TABLE statement.

Using CONSTRAINT, you can designate a field as one of the following types of constraints:

• You can use the UNIQUE reserved word to designate a field as a unique key. This means that no two records in the table can have the same value in this field.
• You can constrain any field or list of fields as unique. If a multiple-field constraint is designated as a unique key, the combined values of all fields in the index must be unique, even if two or more records have the same value in just one of the fields.
• You can use the PRIMARY KEY reserved words to designate one field or set of fields in a table as a PRIMARY KEY. All values in the PRIMARY KEY must be unique and not Null, and there can be only one PRIMARY KEY for a table.

Note: Don't set a PRIMARY KEY constraint on a table that already has a PRIMARY KEY; if you do, an error occurs. You can use the FOREIGN KEY reserved words to designate a field as a FOREIGN KEY. If the foreign table's PRIMARY KEY consists of more than one field, you must use a multiple-field constraint definition, listing all of the referencing fields, the name of the foreign table, and the names of the referenced fields in the foreign table in the same order that the referencing fields are listed. If the referenced field or fields are the foreign table's PRIMARY KEY, you don't have to specify the referenced fields _ by default, the database engine behaves as if the foreign table's PRIMARY KEY is the referenced fields.

Back to Table of Contents
 

CREATE INDEX Statement

The CREATE INDEX Statement Creates a new index on an existing table.  

CREATE INDEX -- Syntax

CREATE [ UNIQUE ] INDEX index            ON table (field [ASC|DESC][, field [ASC|DESC] ...])            [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

The CREATE INDEX statement has these parts:

Part	Description
index	The name of the index to be created.
table	The name of the existing table that will contain the index.
field	The name of the field or fields to be indexed. To create a single-field index, list the field name in parentheses following the table name. To create a multiple-field index, list the name of each field to be included in the index. To create descending indexes, use the DESC reserved word; otherwise, indexes are assumed to be ascending.

Remarks:

To prohibit duplicate values in the indexed field or fields of different records, use the UNIQUE reserved word. In the optional WITH clause, you can enforce data validation rules. You can:

• Prohibit Null entries in the indexed field or fields of new records by using the DISALLOW NULL option.
• Prevent records with Null values in the indexed field or fields from being included in the index by using the IGNORE NULL option.
• Designate the indexed field or fields as the primary key by using the PRIMARY reserved word. This implies that the key is unique, so you can omit the UNIQUE reserved word.

You can use CREATE INDEX to create a pseudo index on a linked table in an ODBC data source, such as SQL Server, that does not already have an index. You don't need permission or access to the remote server to create a pseudo index, and the remote database is unaware of and unaffected by the pseudo index. You use the same syntax for both linked and native tables. This can be especially useful to create an index on a table that would ordinarily be read-only due to lack of an index.

You can also use the ALTER TABLE statement to add a single- or multiple-field index to a table, and you can use the ALTER TABLE statement or the DROP statement to remove an index created with ALTER TABLE or CREATE INDEX.

Note Don't use the PRIMARY reserved word when you create a new index on a table that already has a primary key; if you do, an error occurs.

Back to Table of Contents
 

CREATE TABLE Statement

The CREATE TABLE statement creates a new table.

CREATE TABLE -- Syntax

CREATE TABLE table               (field1 type [(size)] [NOT NULL] [index1]                [, field2 type [(size)] [NOT NULL] [index2] [, ...]]                [, CONSTRAINT multifieldindex [, ...]])

The CREATE TABLE statement has these parts:

Part	Description
table	The name of the table to be created.
field1, field2	The name of field or fields to be created in the new table. You must create at least one field.
type	The data type of field in the new table.
size	The field size in characters (Text and Binary fields only).
index1, index2	A CONSTRAINT clause defining a single-field index. See the CONSTRAINT clause topic for more information on how to create this index.
multifieldindex	A CONSTRAINT clause defining a multiple-field index. See the CONSTRAINT clause topic for more information on how to create this index.

Remarks:

Use the CREATE TABLE statement to define a new table and its fields and field constraints. If NOT NULL is specified for a field, then new records are required to have valid data in that field. A CONSTRAINT clause establishes various restrictions on a field, and can be used to establish the primary key. You can also use the CREATE INDEX statement to create a primary key or additional indexes on existing tables. You can use NOT NULL on a single field, or within a named CONSTRAINT clause that applies to either a single field or to a multiple-field named CONSTRAINT. However, you can apply the NOT NULL restriction only once to a field, or a run-time error occurs.

Back to Table of Contents
 

Data Types

The following table lists some (but not all) of the data types that can be used with either the CREATE TABLE or the ALTER TABLE SQL statements in ACCESS 97®.

Data Type	Meaning
yesno	This is two valued data type. In most contexts, the key words logical or boolean would be employed to define the type.  The value of a yesno field is either the value 0 (zero) or -1, where 0 represents the value No and -1 represents the value Yes. At the keyboard, you enter the value No by typing the digit 0 (zero). The value Yes is entered by typing any numeric value except for 0 (zero).  Internally, a yesno field is stored as a single bit.  The keywords bit and logical may be used instead of yesno.
byte	One byte of storage that may take any integer value between 0 and 255.
counter	The is called AutoNumber in the table Design screen. The underlying data type is integer (see below). By default, defining a field as counter implies selecting the increment method of generating values for this field. (i.e., the first record added to the table will be number 1. The second will be number 2, etc.)
Currency	Internally, data of type currency is stored as a 19 digit value with up to 15 digits to the left of the decimal point and 4 digits to the right of the decimal point. This storage scheme allows for a high deal of accuracy and lessens the likelihood of computational errors.
Datetime	A date and/or time value.  Valid dates run from Friday January 1, 100 through Friday December 31, 9999.  Valid times are based on a 24 hour clock.  When entering dates, note that, when you do not explicitly specify a century, ACCESS 97® assumes 19xx. That is, if you type 9/10/98, ACCESS 97® will treat this as September 9, 1998.  If you enter the value 2:00 this will be interpreted as 2:00 am. To enter 2:00 pm, you must specify either 14:00 or 2:00p or 2:00pm Valid substitutions for the keyword datetime include date and time.
single	A four byte floating point value between -3.402823E38 and 3.40283E38.
double	An 8 byte floating point value between -1.79769313486232E308 and 1.79769313486232E308.
short	A two byte integer value between -32,768 and 32,767.
integer	A four byte integer value between -2,147,483,648 and 2,147,483,647.  Valid substitutions for the keyword integer include long and int.
number	In the design view, number is a general term for all numeric data types. When using the SQL CREATE TABLE and ALTER TABLE statements, number is synonomous with the type double.
Text	A variable length string of at most 255 characters in length.  If the optional size for a text variable is declared, then this overrides the default maximum length of 255 characters.
Memo	A character string with a maximum length of 1.2 Gigabytes!!!

 

A field in an ACCESS 97® database is declared to be of a specific type by using the name shown in the left most column of the table. For example, this statement:

      CREATE TABLE order_shipped
          ( ship_date date,
              order_num short NOT NULL,
              address1 text,
              address2 text (40),
              city text (20),
              state text (2));

creates the table order_shipped with 5 columns. ship_date is of type date which allows it to contain date values between December 30, 100 and December 30, 9999 inclusive. order_num is a required integer value which may range between -32,768 and 32,767. The final four fields are all text. The first of these can be as long as 255 characters, while the remaining four are constrained to fewer characters in length.

Back to Table of Contents
 

DELETE Statement

The DELETE statement creates a delete query that removes records from one or more of the tables listed in the FROM clause that satisfy the WHERE clause.

DELETE -- Syntax

DELETE [table.*]        FROM table        WHERE criteria

The DELETE statement has these parts:

Part	Description
table	The optional name of the table from which records are deleted.
table	The name of the table from which records are deleted.
criteria	An expression that determines which records to delete.

Remarks:

DELETE is used when you want to delete one or more records. To drop an entire table from the database, you can use a DROP statement. If you drop the table, however, both the structure and data values are lost. In contrast, when you use DELETE, only the data is deleted; the table structure and all of the table properties, such as field attributes and indexes, remain intact.

You can use DELETE to remove records from tables that are in a one-to-many relationship with other tables. Cascade delete operations cause the records in tables that are on the many side of the relationship to be deleted when the corresponding record in the one side of the relationship is deleted in the query. For example, in the relationship between the Customers and Orders tables, the Customers table is on the one side and the Orders table is on the many side of the relationship. Deleting a record from Customers results in the corresponding Orders records being deleted if the cascade delete option is specified. A delete query deletes entire records, not just data in specific fields. If you want to delete values in a specific field, create an update query that changes the values to Null.

Important

After you remove records using a delete query, you can't undo the operation. If you want to know which records were deleted, first execute a select query that uses the same criteria. Once you are certain that these are the records you wish to delete, then run the delete query.

Back to Table of Contents
 

DROP Statement

The DROP statement deletes an existing table from a database or deletes an existing index from a table.

DROP -- Statement

DROP {[TABLE table | INDEX   index] ON table}

The DROP statement has these parts:

Part	Description
table	The name of the table to be deleted or the table from which an index is to be deleted.
index	The name of the index to be deleted from table.

Remarks:

You must close the table before you can delete it or remove an index from it.

You can also use ALTER TABLE to delete an index from a table. You can use CREATE TABLE to create a table and CREATE INDEX or ALTER TABLE to create an index. To modify a table, use ALTER TABLE.

Back to Table of Contents
 

FROM Clause

The FROM clause specifies the tables or queries that contain the fields listed in the SELECT statement.

FROM -- Syntax

SELECT fieldlist        FROM tableexpression        [INexternaldatabase]

A SELECT statement containing a FROM clause has these parts:

Part	Description
fieldlist	The name of the field or fields to be retrieved along with any field-name aliases, SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
tableexpression	An expression that identifies one or more tables from which data is retrieved. The expression can be a single table name, a saved query name, or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN.
externaldatabase	The full path of an external database containing all the tables in tableexpression.

Remarks:

FROM is required and follows any SELECT statement. The order of the table names in tableexpression isn't important. The following example shows how you can retrieve data from the Employees table:

      SELECT LastName, FirstName
           FROM Employees;

Back to Table of Contents
 

GROUP BY Clause

Combines records with identical values in the specified field list into a single record. A summary value is created for each record if you include an SQL aggregate function, such as Sum or Count, in the SELECT statement.

GROUP BY Clause -- Syntax

SELECT fieldlist           FROM table           WHERE criteria           [GROUP BY groupfieldlist]

A SELECT statement containing a GROUP BY clause has these parts:

Part	Description
fieldlist	The name of the field or fields to be retrieved along with any field-name aliases, SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
table	The name of the table from which records are retrieved. For more information, see the FROM clause.
criteria	Selection criteria. If the statement includes a WHERE clause, the Microsoft Jet database engine groups values after applying the WHERE conditions to the records.
groupfieldlist	The names of up to 10 fields used to group records. The order of the field names in groupfieldlist determines the grouping levels from the highest to the lowest level of grouping.

 

Remarks:

GROUP BY is optional.

Summary values are omitted if there is no SQL aggregate function in the SELECT statement.

Null values in GROUP BY fields are grouped and aren't omitted. However, Null values aren't evaluated in any SQL aggregate function.

Use the WHERE clause to exclude rows you don't want grouped, and use the HAVING clause to filter records after they've been grouped.

Unless it contains Memo or OLE Object data, a field in the GROUP BY field list can refer to any field in any table listed in the FROM clause, even if the field isn't included in the SELECT statement, provided the SELECT statement includes at least one SQL aggregate function. The Microsoft Jet database engine can't group on Memo or OLE Object fields. All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.

Back to Table of Contents

---

HAVING Clause

The HAVING clause specifies which grouped records are displayed in a SELECT statement with a GROUP BY clause. After GROUP BY combines records, HAVING displays any records grouped by the GROUP BY clause that satisfy the conditions of the HAVING clause.

HAVING Clause -- Syntax

SELECT fieldlist             FROM table             WHERE selectcriteria             GROUP BY groupfieldlist                  [HAVING groupcriteria]

A SELECT statement containing a HAVING clause has these parts:

Part	Description
fieldlist	The name of the field or fields to be retrieved along with any field-name aliases, SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
table	The name of the table from which records are retrieved. For more information, see the FROM clause.
selectcriteria	Selection criteria. If the statement includes a WHERE clause, the Microsoft Jet database engine groups values after applying the WHERE conditions to the records.
groupfieldlist	The names of up to 10 fields used to group records. The order of the field names in groupfieldlist determines the grouping levels from the highest to the lowest level of grouping.
groupcriteria	An expression that determines which grouped records to display.

 

Remarks:

HAVING is optional.

HAVING is similar to WHERE, which determines which records are selected. After records are grouped with GROUP BY, HAVING determines which records are displayed: 

       SELECT CategoryID, Sum(UnitsInStock)
           FROM Products
           GROUP BY CategoryID
           HAVING Sum(UnitsInStock) > 100;

A HAVING clause can contain up to 40 expressions linked by logical operators, such as And and Or.

Back to Table of Contents

---

IN Clause

The IN CLAUSE identifies tables in any external database to which the Microsoft Jet database engine can connect, such as a dBASE or Paradox database or an external Microsoft Jet database. There are two formats for the IN CLAUSE depending on whether the external database is to be written to - destination - or read from - source.

To identify a destination table:

IN Clause -- Syntax

[SELECT | INSERT]           INTO destination           IN {path |["path" "type"] | ["" [type; DATABASE = path]]}

To identify a source table:

IN Clause -- Syntax

SELECT FROM tableexpression          IN {path | ["path" "type"] | ["" [type; DATABASE = path]]}

A SELECT statement containing an IN clause has these parts:

Part	Description
destination	The name of the external table into which data is inserted.
tableexpression	The name of the table or tables from which data is retrieved. This argument can be a single table name, a saved query, or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN.
path	The full path for the directory or file containing table.
type	The name of the database type used to create table if a database isn't a Microsoft Jet database (for example, dBASE III, dBASE IV, Paradox 3.x, or Paradox 4.x).

 

Remarks:

You can use IN to connect to only one external database at a time.

In some cases, the path argument refers to the directory containing the database files. For example, when working with dBASE, FoxPro, or Paradox database tables, the path argument specifies the directory containing .dbf or .db files. The table file name is derived from the destination or tableexpression argument.

To specify a non-Microsoft Jet database, append a semicolon (;) to the name, and enclose it in single (' ') or double (" ") quotation marks. For example, either 'dBASE IV;' or "dBASE IV;" is acceptable.

You can also use the DATABASE reserved word to specify the external database. For example, the following lines specify the same table:

        ... FROM Table IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];
        ... FROM Table IN "C:\DBASE\DATA\SALES" "dBASE IV;"

Notes

• For improved performance and ease of use, use a linked table instead of IN.
• You can also use the IN reserved word as a comparison operator in an expression. For more information, see the In operator.

Back to Table of Contents

---

In Operator

The IN operator is used to determine whether the value of an expression is equal to any of several values in a specified list.

IN Operator -- Syntax

expr [Not] In(value1, value2,  . . .)

The In operator syntax has these parts:

Part	Description
expr	Expression identifying the field that contains the data you want to evaluate.
value1,value2	Expression or list of expressions against which you want to evaluate expr.

Remarks:

If expr is found in the list of values, the In operator returns True; otherwise, it returns False. You can include the Not logical operator to evaluate the opposite condition (that is, whether expr is not in the list of values). For example, you can use In to determine which orders are shipped to a set of specified regions:

      SELECT *
         FROM Orders
         WHERE ShipRegion In ('Avon','Glos','Som')

Back to Table of Contents

---

INNER JOIN Operation

INNER JOIN combines records from two tables whenever there are matching values in a common field.

INNER JOIN -- Syntax

FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2

The INNER JOIN operation has these parts:

Part	Description
table1, table2	The names of the tables from which records are combined.
field1, field2	The names of the fields that are joined. If they aren't numeric, the fields must be of the same data type and contain the same kind of data, but they don't have to have the same name.
compopr	Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>."

Remarks:

You can use an INNER JOIN operation in any FROM clause. This is the most common type of join. Inner joins combine records from two tables whenever there are matching values in a field common to both tables.

You can use INNER JOIN with the Departments and Employees tables to select all the employees in each department. In contrast, to select all departments (even if some have no employees assigned to them) or all employees (even if some aren't assigned to a department), you can use a LEFT JOIN or RIGHT JOIN operation to create an outer join. If you try to join fields containing Memo or OLE Object data, an error occurs.

You can join any two numeric fields of like types. For example, you can join on AutoNumber and Long fields because they are like types. However, you cannot join Single and Double types of fields.

The following example shows how you could join the Categories and Products tables on the CategoryID field:

  SELECT CategoryName, ProductName
         FROM Categories
        INNER JOIN Products ON Categories.CategoryID = Products.CategoryID;

In the preceding example, CategoryID is the joined field, but it isn't included in the query output because it isn't included in the SELECT statement. To include the joined field, include the field name in the SELECT statement -- in this case, Categories.CategoryID.

You can also link several ON clauses in a JOIN statement, using the following syntax:

  SELECT fields
          FROM table1 INNER JOIN table2
          ON table1.field1 compopr table2.field1 AND
          ON table1.field2 compopr table2.field2) OR
          ON table1.field3 compopr table2.field3)];

You can also nest JOIN statements using the following syntax:

        SELECT fields
           FROM table1 INNER JOIN
           (table2 INNER JOIN [( ]table3
           [INNER JOIN [( ]tablex [INNER JOIN ...)]
           ON table3.field3 compopr tablex.fieldx)]
           ON table2.field2 compopr table3.field3)
           ON table1.field1 compopr table2.field2;

A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN, but an INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN.

Back to Table of Contents

---

INSERT INTO Statement

The INSERT INTO statement adds a record or multiple records to a table. This is referred to as an append query.

Multiple-record append query:

INSERT INTO -- Syntax Multiple Record

INSERT INTO target        [IN externaldatabase] [(field1[, field2[,...]])]            SELECT [source.]field1[, field2[, ...]        FROM tableexpression

Single-record append query:

INSERT INTO -- Syntax Single Record Append

INSERT INTO target [(field1[, field2[, ...]])]                 VALUES (value1[, value2[, ...])

The INSERT INTO statement has these parts:

Part	Description
target	The name of the table or query to append records to.
externaldatabase	The path to an external database. For a description of the path, see the IN clause.
source	The name of the table or query to copy records from.
field1, field2	Names of the fields to append data to, if following a target argument, or the names of fields to obtain data from, if following a source argument.
tableexpression	The name of the table or tables from which records are inserted. This argument can be a single table name or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN operation or a saved query.
value1, value2	The values to insert into the specific fields of the new record. Each value is inserted into the field that corresponds to the value's position in the list: value1 is inserted into field1 of the new record, value2 into field2, and so on. You must separate values with a comma, and enclose text fields in quotation marks (' ').

Remarks

You can use the INSERT INTO statement to add a single record to a table using the single-record append query syntax as shown above. In this case, your code specifies the name and value for each field of the record. You must specify each of the fields of the record that a value is to be assigned to and a value for that field. The default value or Null is inserted for all unspecified fields. Records are added to the end of the table.

You can also use INSERT INTO to append a set of records from another table or query by using the SELECT ... FROM clause as shown above in the multiple-record append query syntax. In this case, the SELECT clause specifies the fields to append to the specified target table. The source or target table may specify a table or a query. If a query is specified, the Microsoft Jet database engine appends records to any and all tables specified by the query. INSERT INTO is optional but when included, precedes the SELECT statement. If your destination table contains a PRIMARY KEY, make sure you append unique, non-Null values to the PRIMARY KEY field or fields; if you don't, the Microsoft Jet database engine won't append the records. If you append records to a table with an AutoNumber field and you want to renumber the appended records, don't include the AutoNumber field in your query. Do include the AutoNumber field in the query if you want to retain the original values from the field. Use the IN clause to append records to a table in another database. To create a new table, use the SELECT... INTO statement instead to create a make-table query. To find out which records will be appended before you run the append query, first execute and view the results of a select query that uses the same selection criteria. An append query copies records from one or more tables to another. The tables that contain the records you append aren't affected by the append query. Instead of appending existing records from another table, you can specify the value for each field in a single new record using the VALUES clause. If you omit the field list, the VALUES clause must include a value for every field in the table; otherwise, the INSERT operation will fail. Use an additional INSERT INTO statement with a VALUES clause for each additional record you want to create.

Back to Table of Contents

---

LEFT JOIN, RIGHT JOIN Operations

Combines source-table records when used in any FROM clause.

LEFT JOIN RIGHT JOIN -- Syntax

FROM           table1 [ LEFT | RIGHT ] JOIN table2          ON table1.field1 compopr table2.field2

The LEFT JOIN and RIGHT JOIN operations have these parts:

Part	Description
table1, table2	The names of the tables from which records are combined.
field1, field2	The names of the fields that are joined. The fields must be of the same data type and contain the same kind of data, but they don't need to have the same name.
compopr	Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>."

Remarks:

Use a LEFT JOIN operation to create a left outer join. Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table.

Use a RIGHT JOIN operation to create a right outer join. Right outer joins include all of the records from the second (right) of two tables, even if there are no matching values for records in the first (left) table.

For example, you could use LEFT JOIN with the Departments (left) and Employees (right) tables to select all departments, including those that have no employees assigned to them. To select all employees, including those who aren't assigned to a department, you would use RIGHT JOIN.

The following example shows how you could join the Categories and Products tables on the CategoryID field. The query produces a list of all categories, including those that contain no products:

       SELECT CategoryName, ProductName
          FROM Categories LEFT JOIN Products
          ON Categories.CategoryID = Products.CategoryID;

In this example, CategoryID is the joined field, but it isn't included in the query results because it isn't included in the SELECT statement. To include the joined field, enter the field name in the SELECT statement, in this case, Categories.CategoryID.

Notes:

• To create a query that includes only records in which the data in the joined fields is the same, use an INNER JOIN operation.
• A LEFT JOIN or a RIGHT JOIN can be nested inside an INNER JOIN, but an INNER JOIN cannot be nested inside a LEFT JOIN or a RIGHT JOIN. See the discussion of nesting in the INNER JOIN topic to see how to nest joins within other joins.
• You can link multiple ON clauses. See the discussion of clause linking in the INNER JOIN topic to see how this is done.
• If you try to join fields containing Memo or OLE Object data, an error occurs.

Back to Table of Contents

---

Like Operator

The LIKE operator compares a string expression to a pattern in an SQL expression.

LIKE Operator -- Syntax

expression Like "pattern"

The Like operator syntax has these parts:

Part	Description
expression	SQL expression used in a WHERE clause.
pattern	String or character string literal against which expression is compared.

Remarks:

You can use the Like operator to find values in a field that match the pattern you specify. For pattern, you can specify the complete value (for example, Like "Smith"), or you can use wildcard characters to find a range of values (for example, Like "Sm*").

In an expression, you can use the Like operator to compare a field value to a string expression. For example, if you enter Like "C*" in an SQL query, the query returns all field values beginning with the letter C. In a parameter query, you can prompt the user for a pattern to search for. The following example returns data that begins with the letter P followed by any letter between A and F and three digits:

         Like "P[A-F]###"

The following table shows how you can use Like to test expressions for different patterns.

Kind of Match	Pattern	Match returns (True)	No Match (returns False)
Multiple characters	a*a	aa, aBa,aBBBa	aBC
Multiple characters	*ab*	abc, AABB, Xab	aZb, bac
Special character	a[*]a	a*a	aaa
Multiple characters	ab*	abcdefg, abc	cab, aab
Single character	a?a	aaa, a3a, aBa	aBBBa
Single digit	a#a	a0a, a1a, a2a	aaa, a10a
Range of characters	[a-z]	f, p, j	2, &
Outside a range	[!a-z]	9, &, %	b, a
Not a digit	[!0-9]	A, a, &, ~	0, 1, 9
Combined	a[!b-m]#	An9, az0, a99	abc, aj0, an0

Back to Table of Contents

---

ORDER BY Clause

The ORDER BY claues sorts a query's resulting records on a specified field or fields in ascending or descending order.

ORDER BY Clause -- Syntax

SELECT fieldlist              FROM table              WHERE selectcriteria              [ORDER BY field1 [ASC | DESC ][, field2 [ASC | DESC ]][,...]]

A SELECT statement containing an ORDER BY clause has these parts:

Part	Description
fieldlist	The name of the field or fields to be retrieved along with any field-name aliases, SQL aggregate functions, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
table	The name of the table from which records are retrieved. For more information, see the FROM clause.
selectcriteria	Selection criteria. If the statement includes a WHERE clause, the Microsoft Jet database engine orders values after applying the WHERE conditions to the records.
field1, field2	The names of the fields on which to sort records.

Remarks:

ORDER BY is optional. However, if you want your data displayed in sorted order, then you must use ORDER BY.

The default sort order is ascending (A to Z, 0 to 9). Both of the following examples sort employee names in last name order:

       SELECT LastName, FirstName
           FROM Employees
           ORDER BY LastName;

       SELECT LastName, FirstName
           FROM Employees
           ORDER BY LastName ASC;

To sort in descending order (Z to A, 9 to 0), add the DESC reserved word to the end of each field you want to sort in descending order. The following example selects salaries and sorts them in descending order:

       SELECT LastName, Salary
           FROM Employees
           ORDER BY Salary DESC, LastName;

If you specify a field containing Memo or OLE Object data in the ORDER BY clause, an error occurs. ORDER BY is usually the last item in an SQL statement. You can include additional fields in the ORDER BY clause. Records are sorted first by the first field listed after ORDER BY. Records that have equal values in that field are then sorted by the value in the second field listed, and so on.

Back to Table of Contents

---

Predicates

The ALL, DISTINCT, DISTICTROW, TOP Predicates specify the records selected with SQL queries.

Predicates -- Syntax

SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]                  FROM table

A SELECT statement containing these predicates has the following parts:

Part	Description
ALL	Assumed if you don't include one of the predicates. The following two examples are equivalent and return all records from the Employees table:     SELECT ALL *       FROM Employees       ORDER BY EmployeeID;    SELECT *       FROM Employees       ORDER BY EmployeeID;
DISTINCT	Omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. For example, several employees listed in an Employees table may have the same last name. If two records contain Smith in the LastName field, the following SQL statement returns only one record that contains Smith:     SELECT DISTINCT LastName         FROM Employees; If you omit DISTINCT, this query returns both Smith records. If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results. The output of a query that uses DISTINCT isn't updatable and doesn't reflect subsequent changes made by other users.
DISTINCTROW	Omits data based on entire duplicate records, not just duplicate fields. For example, you could create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders:     SELECT DISTINCTROW CompanyName       FROM Customers, Orders       WHERE Customers.CustomerID =  Orders.CustomerID       ORDER BY CompanyName; If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order. DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table, or if you output fields from all tables.
TOP n [PERCENT]	Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994:     SELECT TOP 25 FirstName, LastName        FROM Students        WHERE GraduationYear = 1994        ORDER BY GradePointAverage DESC; If you don't include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause. The TOP predicate doesn't choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records. You can also use the PERCENT reserved word to return a certain percentage of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose that, instead of the top 25 students, you want the bottom 10 percent of the class:      SELECT TOP 10 PERCENT FirstName, LastName         FROM Students         WHERE GraduationYear = 1994         ORDER BY GradePointAverage ASC; The ASC predicate specifies a return of bottom values. The value that follows TOP must be an unsigned Integer. TOP doesn't affect whether or not the query is updatable.
table	The name of the table from which records are retrieved.

Back to Table of Contents

---

SELECT Statement

Retrieves data from an ACCESS 97® database

SELECT -- Syntax

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1]             [, [table.]field2 [AS alias2] [, ...]]}         FROM tableexpression [, ...]         [IN externaldatabase]         [WHERE... ]         [GROUP BY... ]         [HAVING... ]         [ORDER BY... ]

The SELECT statement has these parts:

Part	Description
predicate	One of the following predicates: ALL, DISTINCT, DISTINCTROW, or TOP. You use the predicate to restrict the number of records returned. If none is specified, the default is ALL.
*	Specifies that all fields from the specified table or tables are selected.
table	The name of the table containing the fields from which records are selected.
field1, field2	The names of the fields containing the data you want to retrieve. If you include more than one field, they are retrieved in the order listed.
alias1, alias2	The names to use as column headers instead of the original column names in table.
tableexpression	The name of the table or tables containing the data you want to retrieve.
externaldatabase	The name of the database containing the tables in tableexpression if they are not in the current database.

Remarks:

To perform this operation, the Microsoft Jet database engine searches the specified table or tables, extracts the chosen columns, selects rows that meet the criterion, and sorts or groups the resulting rows into the order specified. SELECT statements don't change data in the database. SELECT is usually the first word in an SQL statement. Most SQL statements are either SELECT or SELECT...INTO statements. The minimum syntax for a SELECT statement is:

        SELECT fields
            FROM table

You can use an asterisk (*) to select all fields in a table. The following example selects all of the fields in the Employees table:

        SELECT *
          FROM Employees;

If a field name is included in more than one table in the FROM clause, qualify it with the table name and the . (dot) operator. In the following example, the Department field is in both the Employees table and the Supervisors table. The SQL statement selects departments from the Employees table and supervisor names from the Supervisors table:

            SELECT Employees.Department, Supervisors.SupvName
                  FROM Employees,  Supervisors
                  WHERE Employees.Department = Supervisors.Department;

When a Recordset object is created, the Microsoft Jet database engine uses the table's field name as the Field object name in the Recordset object. If you want a different field name or a name isn't implied by the expression used to generate the field, use the AS reserved word. The following example uses the title Birth to name the returned Field object in the resulting Recordset object:

        SELECT BirthDate AS Birth
            FROM Employees;

Whenever you use aggregate functions or queries that return ambiguous or duplicate Field object names, you must use the AS clause to provide an alternate name for the Field object. The following example uses the title HeadCount to name the returned Field object in the resulting Recordset object:

        SELECT COUNT(EmployeeID) AS HeadCount
            FROM Employees;

You can use the other clauses in a SELECT statement to further restrict and organize your returned data. For more information, see the Help topic for the clause you're using.

Back to Table of Contents

---

SELECT...INTO Statement

The SELECT INTO statement creates a make-table query.

SELECT ... INTO -- Syntax

SELECT field1[, field2[, ...]]                  INTO newtable             [IN externaldatabase]                 FROM source

The SELECT...INTO statement has these parts:

Part	Description
field1, field2	The name of the fields to be copied into the new table.
newtable	The name of the table to be created. It must conform to standard naming conventions. If newtable is the same as the name of an existing table, a trappable error occurs.
externaldatabase	The path to an external database. For a description of the path, see the IN clause.
source	The name of the existing table from which records are selected. This can be single or multiple tables or a query.

 Remarks:

You can use make-table queries to archive records, make backup copies of your tables, or make copies to export to another database or to use as a basis for reports that display data for a particular time period. For example, you could produce a Monthly Sales by Region report by running the same make-table query each month.

Notes:

You may want to define a PRIMARY KEY for the new table. When you create the table, the fields in the new table inherit the data type and field size of each field in the query's underlying tables, but no other field or table properties are transferred.. To add data to an existing table, use the INSERT INTO statement instead to create an append query.. To find out which records will be selected before you run the make-table query, first examine the results of a SELECT statement that uses the same selection criteria.

Back to Table of Contents

---

UPDATE Statement

The UPDATE statement creates an update query that changes values in fields in a specified table based on specified criteria.

UPDATE -- Syntax

UPDATE table             SET newvalue             WHERE criteria;

The UPDATE statement has these parts:

Part	Description
table	The name of the table containing the data you want to modify.
newvalue	An expression that determines the value to be inserted into a particular
field	in the updated records.
criteria	An expression that determines which records will be updated. Only records that satisfy the expression are updated.

Remarks:

UPDATE is especially useful when you want to change many records or when the records that you want to change are in multiple tables. You can change several fields at the same time. The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent for shippers in the United Kingdom:

        UPDATE Orders
            SET OrderAmount = OrderAmount * 1.1, Freight = Freight * 1.03
            WHERE ShipCountry = 'UK';

Important

UPDATE doesn't generate a result set. Also, after you update records using an update query, you can't undo the operation. If you want to know which records were updated, first examine the results of a select query that uses the same criteria, and then run the update query.

Back to Table of Contents

---

WHERE Clause

The WHERE clause specifies which records from the tables listed in the FROM clause are affected by a SELECT, UPDATE, or DELETE statement.

WHERE Clause -- Syntax

SELECT fieldlist          FROM tableexpression               WHERE criteria

A SELECT statement containing a WHERE clause has these parts:

Part	Description
fieldlist	The name of the field or fields to be retrieved along with any field-name aliases, selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
tableexpression	The name of the table or tables from which data is retrieved.
criteria	An expression that records must satisfy to be included in the query results.

Remarks:

If you don't specify a WHERE clause, your query returns all rows from the table. If you specify more than one table in your query and you haven't included a WHERE clause or a JOIN clause, your query generates a Cartesian product of the tables.

WHERE is optional, but when included, follows FROM. For example, you can select all employees in the sales department (WHERE Dept = 'Sales') or all customers between the ages of 18 and 30 (WHERE Age Between 18 And 30).

If you don't use a JOIN clause to perform SQL join operations on multiple tables, the resulting Recordset object won't be updatable.

WHERE is similar to HAVING. WHERE determines which records are selected. Similarly, once records are grouped with GROUP BY, HAVING determines which records are displayed.

Use the WHERE clause to eliminate records you don't want grouped by a GROUP BY clause.

Use various expressions to determine which records the SQL statement returns. For example, the following SQL statement selects all employees whose salaries are more than $21,000:

   SELECT LastName, Salary
        FROM Employees
        WHERE Salary > 21000;

A WHERE clause can contain up to 40 expressions linked by logical operators, such as And and Or. When you enter a field name that contains a space or punctuation, surround the name with brackets ([ ]). For example, a customer information table might include information about specific customers :

    SELECT [Customer's Favorite Restarant]

When you specify the criteria argument, date literals must be in U.S. format, even if you're not using the U.S. version of the Microsoft Jet database engine. For example, May 10, 1996, is written 10/5/96 in the United Kingdom and 5/10/96 in the United States. Be sure to enclose your date literals with the number sign (#) as shown in the following examples. To find records dated May 10, 1996 in a United Kingdom database, you should use the following SQL statement:

     SELECT *
        FROM Orders
        WHERE ShippedDate = #5/10/96#;

You can also use the DateValue function which is aware of the international settings established by Microsoft Windows. For example, use this code for the United States:

    SELECT *
       FROM Orders
       WHERE ShippedDate = DateValue('5/10/96');

And use this code for the United Kingdom:

    SELECT *
       FROM Orders
       WHERE ShippedDate = DateValue('10/5/96');

Note If the column referenced in the criteria string is of type GUID, the criteria expression uses a slightly different syntax: WHERE ReplicaID = {GUID {12345678-90AB-CDEF-1234-567890ABCDEF}} Be sure to include the nested braces and hyphens as shown.

Back to Table of Contents