action query <definition>
action query

A query that moves or changes data.  Action queries include append, delete, make-table, and update queries.  Delete and update queries change existing data; append and make-table queries move existing data.  In contrast, select queries return data records.  A SQL pass-through query may also be an action query.


action QueryDef <definition>
action QueryDef

A data access object that contains the definition of an action query.  An action QueryDef object is used only with the Execute method, and it moves or changes data instead of returning rows.


aggregate function <definition>
aggregate function

A function, such as Count, Avg, and Var, used in creating a query that calculates totals.  In writing expressions and in programming, you can use SQL aggregate functions (including the three listed above) and domain aggregate functions to determine various statistics.


alias <definition>
alias

In Visual Basic, an alternate name you give to an external procedure to avoid conflict with a Visual Basic keyword, global variable, constant, or a name not allowed by the standard naming conventions.

In SQL, an alternate name you give to a field or expression in a SELECT statement (using the AS clause), to make it shorter or more meaningful or to prevent name conflicts when performing SQL queries using expressions that don't return names or in a query that uses a self-join.


ANSI Character Set <definition>
ANSI Character Set

American National Standards Institute (ANSI) 8-bit character set used by Microsoft Windows that you can use to represent up to 256 characters (0 [image] 255) using your keyboard.  The first 128 characters (0 [image] 127) correspond to the letters and symbols on a standard U.S. keyboard.  The second 128 characters (128 [image] 255) represent special characters, such as letters in international alphabets, accents, currency symbols, and fractions.


append query <definition>
append query

An action query that adds new records to the end of an existing table or query.  Append queries don't return records (rows).


argument <definition>
argument

A constant, variable, or expression that supplies additional information to an action, procedure, or method. You use an argument with an operand in an expression to supply additional information required by some action.


ASCII Character Set <definition>
ASCII Character Set

American Standard Code for Information Interchange (ASCII) 7-bit character set widely used to represent letters and symbols found on a standard U.S. keyboard.  The ASCII character set is the same as the first 128 characters (0 [image] 127) in the ANSI character set.


asterisk <definition>
asterisk

A character (*) used as a wildcard in SQL LIKE expressions to accept any string of characters.  For example, the expression LIKE "*town*" accepts any name containing the string "town."

When you create a query or enter an SQL statement, use the asterisk to include all the fields that currently exist in the underlying table or query.  For example, SELECT * FROM MyTable returns all fields (columns) from the table.


attached table <definition>
attached table

A table in another database linked to a Microsoft Jet database.  Data for attached tables remains in the external database where it can be manipulated by other applications. (Also known as linked tables.)


autonumber field <definition>
autonumber field

See counter field.


base table <definition>
base table

A table in a Microsoft Jet database.  You can manipulate the structure of a base table using the data access objects or data definition SQL statements, and you can modify data in a base table using Recordset objects or action queries.


bookmark <definition>
bookmark

A property of the Recordset object or a form that contains a system-generated string identifying the current record. If you assign the Bookmark value to a variable and then move to another record, you can make the earlier record current again by setting the Bookmark property to that string variable.


Boolean data type <definition>
Boolean data type

A True/False or yes/no value.  Boolean values are usually stored in Bit fields in a Microsoft Jet database; however, some databases don't support this data type directly.


Boolean expression <definition>
Boolean expression

An expression that evaluates to either True or False.


bound control <definition>
bound control

A data-aware control that can provide access to a specific field in a database through a Data control.  A data-aware control can be bound to a Data control through its DataSource and DataField properties.  When a Data control moves from one record to the next, all bound controls connected to the Data control change to display data from fields in the current record.  When users change data in a bound control and then move to a different record, the changes are automatically saved in the database.


Byte data type <definition>
Byte data type

A fundamental data type used to hold small positive integer numbers ranging from 0 to 255.


Cartesian product <definition>
Cartesian product

The result of joining two relational tables, producing all possible ordered combinations of rows from the first table with all rows from the second table.

Generally, a cartesian product results from executing a SQL SELECT statement referencing two or more tables in the FROM clause and not including a WHERE or JOIN clause that indicates how the tables are to be joined.


cascade delete <definition>
cascade delete

For relationships that enforce referential integrity between tables, an option that causes the deletion of a record from the primary table to automatically delete all related records in the related foreign table or tables.

For example, suppose you establish a relationship between a Customers (Primary) table and an Orders (Foreign) table with the cascade delete option enabled.  When a record in the Customers table is deleted, all orders associated with that customer would also be deleted in the Foreign Orders table.


cascade update <definition>
cascade update

For relationships enforcing referential integrity between tables, an option that causes a change to the primary key in a record in the Primary table to automatically update the foreign key in all related records in the related Foreign table or tables.

For example, suppose you establish a relationship between a Customers (Primary) table and an Orders (Foreign) table with the cascade update option enabled.  When the primary key in a record in the Customers table is changed, all orders associated with that customer would also be changed in the Foreign Orders table.


cascade <definition>
cascade

The process of one action triggering another action.  For example, when a cascading update relationship is defined for two (or more) tables, an update to the primary key in the primary table automatically triggers (cascades) changes to the foreign table.


case-sensitive <definition>
case-sensitive

Capable of distinguishing between uppercase and lowercase letters.  A case-sensitive search finds only text that is an exact match of uppercase and lowercase letters.  Such a search would, for instance, treat “ZeroLengthStr” and “zerolengthstr” as different.  Microsoft Jet database operations are not case-sensitive.  However, case sensitivity is a feature of some other database management systems.


class <definition>
class

The formal definition of an object.  The class acts as the template from which an instance of an object is created at run time.  The class defines the properties of the object and the methods used to control the object's behavior.


CLSID (class ID) <definition>
CLSID (class ID)

A unique identifier of an application that is stored in the Windows Registry.  OLE uses this value to determine where the .exe or .dll code for this class resides.  Because the locations of CLSIDs never change and CLSIDs always point to the correct version of files, users are insulated from file and path name changes. A CLSID is a GUID data type in string format.


collection <definition>
collection

An object that contains a set of related objects.  An object's position in the collection can change whenever a change occurs in the collection; therefore, the position of any specific object in the collection may vary.


column <definition>
column

In a relational database, a column is synonymous with a field in a nonrelational database.  A column defines the data type, size, and other attributes of one field of a row (record) of data.  All columns taken as a set define a row (record) in the database.  An individual column contains data related in type and purpose throughout the table; that is, a column's definition doesn’t change from row to row.


commit <definition>
commit

To accept a pending transaction.  If you use transaction processing and begin a transaction using data access, none of the changes made in the transaction are written to the database until you commit or accept the transaction.


compile error <definition>
compile error

An error that occurs during compile time as the result of incorrectly constructed code.


consistent <definition>
consistent

The state of a multiple-table Recordset object that allows you to perform only updates that result in a consistent view of the data.  For example, in a Recordset that is a join of two or more tables (a one-to-many relationship), a consistent query would not allow you to set the many-side key to a value that isn’t in the one-side table.


control <definition>
control

An object you can place on a form that has its own set of recognized properties and events.  You use controls to receive user input, display output, and trigger event procedures.  You can manipulate most controls using methods.  Some controls are interactive (responsive to user actions), while others are static (accessible only through code).


copy buffer <definition>
copy buffer

A location created by the Microsoft Jet database engine for the contents of a record that is open for editing.  The Edit method copies the current record to the copy buffer; the AddNew method clears the buffer for a new record and sets the default values; and the Update method saves the data from the copy buffer to the database, replacing the current record or inserting the new record.  Any statement that resets or moves the current record pointer discards the copy buffer.  For example, using the MoveNext method or changing the Index property of a table would discard the contents of the copy buffer.


connect string <definition>
connect string

A string used to define the source of data for an external database.  The connect string is usually assigned to the Connect property of a QueryDef object or Data control or as an argument to the OpenDatabase method.


Counter data type <definition>
Counter data type

A Long data type field in a table where the field Attributes property has the dbAutoIncrement option bit set.  A  counter field is automatically incremented by 1 when a new record is added to the table.  Counter fields are not intended to be updated by the user.


counter field <definition>
counter field

A field that assigns the next consecutive number for each successive record added in the same field.  A counter field always uses the Long data type.  (Also known as an autonumber field.)


criteria <definition>
criteria

A set of limiting conditions, such as = "Denmark" (meaning equal to Denmark) or > 30000, used in creating a query or filter to show a specific set of records.


crosstab query <definition>
crosstab query

A query that returns data in a spreadsheet type format using fields you specify as row and column headings, and that can return summary data.


Currency data type <definition>
Currency data type

A data type that is useful for calculations involving money or for fixed-point calculations in which accuracy is extremely important.  This data type is used to store numbers with up to 11 digits to the left of the decimal point and 4 digits to the right.  The type-declaration character in Visual Basic is @.  Because the Currency data type uses discrete values for all amounts, binary round-off isn’t a factor when calculating totals.


current database <definition>
current database

The Database object returned by the CurrentDB( ) function.  A reference of DBEngine.Workspaces(0).Databases(0) returns the first database opened.  Not all Microsoft Jet database engine clients support the concept of a "current" database.  This concept isn’t supported in Visual Basic version 4.0.


current index <definition>
current index

For an indexed table-type Recordset object, the index most recently set with the Index property.  This index is the basis for ordering records in a table-type Recordset, and is used by the Seek method to locate records.  A Recordset object can have more than one index but can use only one index at a time (although a TableDef object may have several indexes defined on it).  The Microsoft Jet database engine may use more than one index to evaluate a query.


current record <definition>
current record

The record in a Recordset object that you can use to modify or examine data.  Use the Move methods to reposition the current record in a recordset.  Use the Find methods (with a dynaset- or snapshot-type Recordset object) or the Seek method (with a table-type Recordset object) to change the current record position according to specific criteria.

Only one record in a Recordset can be the current record; however, a Recordset may have no current record.  For example, after a dynaset-type Recordset record has been deleted, or when a Recordset has no records, the current record is undefined.  In this case, operations that refer to the current record result in a trappable error.


current transaction <definition>
current transaction

All changes made to a Recordset object after you use the last BeginTrans method and before you use the Rollback or CommitTrans method.


data access object <definition>
data access object

An object that is defined by the Microsoft Jet database engine.  You use data access objects, such as the Database, TableDef, Recordset and QueryDef objects, to represent objects that are used to organize and manipulate data, such as tables and queries, in code.


data-definition query <definition>
data-definition query

An SQL-specific query that can create, alter, or delete a table, or create or delete an index in a database.


data page <definition>
data page

A portion of the database file in which record data is stored.  Depending on the size of the records, a data page may contain more than one record.  A data page in a Microsoft Jet database is 2K bytes.


data source <definition>
data source

A named Open DataBase Connectivity (ODBC) resource that specifies the location, driver type, and other parameters needed by an ODBC driver to access an ODBC database.  A data source can be any source of database information.


data type <definition>
data type

The characteristics of a variable that determine what kind of data the variable can hold. Data types include Byte, Boolean, Currency, Date, Double, Integer, Long, OLE Object, Single, String, Variant (default), user-defined types (created with the Type statement), and object data types, which include host-defined object data types and data access object types.


database <definition>
database

A set of data related to a particular topic or purpose.  A database contains tables and can also contain queries and indexes as well as table relationships, table and field validation criteria, and linkages to external data sources.


database administration <definition>
database administration

Activities required to preserve the integrity and security of a database, such as maintaining user permissions and backing up, compacting, and repairing the database.


database engine <definition>
database engine

That part of the database system that retrieves data from and stores data in user and system databases.


database management system (DBMS) <definition>
database management system (DBMS)

Software used to organize, analyze, and modify information stored in a database. The Microsoft SQL Server and the Microsoft Jet database engine are examples of database management systems.


database objects <definition>
database objects

The database objects are Recordset, Field, Index, QueryDef, TableDef, Relation, User, Group, Property, and Document.


Database object <definition>
Database object

A Database object is a logical representation of a physical database.  A database is a set of data related to a specific topic or purpose.  A database contains tables and can also contain queries and indexes as well as table relationships, table and field validation criteria, and linkages to external data sources.


date literal <definition>
date literal

Any sequence of characters with a valid format that is surrounded by number signs (#).  Valid formats include the date format specified by the locale settings for your code or the universal date format.

For example, #12/31/92# is the date literal that represents December 31, 1992 where English-U.S. is the locale setting for your application.  Use date literals to maximize portability across national languages.


dates and times <definition>
dates and times

Dates and times are stored internally as different parts of a real number.

The value to the left of the decimal represents a date between December 30, 1899 and December 30, 9999, inclusive.  Negative values represent dates prior to December 30, 1899.

The value to the right of the decimal represents a time between 0:00:00 and 23:59:59, inclusive.  Midday is represented by .5.


DDL (Data Definition Language) <definition>
DDL (Data Definition Language)

The language used to describe attributes of a database, especially tables, fields, indexes, and storage strategy.


default workspace <definition>
default workspace

The Workspace object that the Microsoft Jet database engine automatically establishes when your application first references any data access object.  This Workspace is referenced by DBEngine.Workspaces(0) or simply Workspaces(0).


delete query <definition>
delete query

An action query that deletes a set of rows that match the criteria you specify.  A delete query doesn’t return rows.


design time <definition>
design time

The time during which you build an application in the development environment by adding controls, setting control or form properties, and so on.  In contrast, during run time, you interact with the application as a user would.


Double data type <definition>
Double data type

A fundamental data type that holds double-precision floating-point numbers in IEEE format.  A Double variable is stored as a 64-bit (8-byte) number ranging in value from -1.79769313486232E308 to -4.94065645841247E-324 for negative values, from 4.94065645841247E-324 to 1.797693134862325E308 for positive values, and 0.  The type-declaration character is #.


domain <definition>
domain

A set of records defined by a table, query, or SQL expression. Aggregate functions, such as Max and StDev, return statistical information about a specific domain.


dynamic-link library (DLL) <definition>
dynamic-link library (DLL)

A library of routines loaded and linked into applications at run time.  DLLs are created with other programming languages such as C, MASM, or FORTRAN.


dynaset <definition>
dynaset

A type of Recordset object that returns a dynamic set of pointers to live database data.  Like a table- or a snapshot-type Recordset, a dynaset returns data in records (rows) and fields (columns).  Unlike a table-type Recordset, a dynaset-type Recordset can be the result of a query that joins two or more tables.  The records in dynaset-type Recordset objects are updatable if their Updatable property is True, the Field being changed is updatable, and the data page containing the current record isn’t locked.


equi-join <definition>
equi-join

A join in which records from two tables are combined and added to the Recordset object only when there are equal values in the joined fields.  In Visual Basic, equi-joins are referred to as inner joins.


expression  <definition>
expression

Any combination of operators, constants, literal values, functions, and names of fields, controls, and properties that evaluates to a single value.  You can use expressions as settings for many properties and action arguments, to set criteria, or define calculated fields in queries.


external table <definition>
external table

A table in a database outside the open Microsoft Jet database.  An external table may take the form of another file or it may be a table in another relational database.


exclusive <definition>
exclusive

Indicates whether a database or a table can be shared by other users in a multiuser environment.  If the database or table is opened for exclusive use, it can’t be shared by other users.


external database <definition>
external database

Either an ODBC database such as SQL Server that resides on a remote server, or one of the external databases such as Paradox, dBASE, FoxPro, Microsoft Excel, or Microsoft Access.


field <definition>
field

A category of information stored in a table in a database [image] a column of data.  An element of a database table that contains a specific item of information, such as last name.


field data types <definition>
field data types

The following table lists the field data types and their equivalent Visual Basic data types.

Field data type	Visual Basic data type

Boolean	Boolean

Byte	Byte

Integer	Integer

Long	Long

Currency	Double

Single	Single

Double	Double

Date/Time	Variant

Text	String

Long Binary	String or Variant; can be up to 1.2 gigabytes.

Memo	String or Variant; can be up to 1.2 gigabytes.

Note that any kind of value can be stored in a Variant data type.


field properties <definition>
field properties

Attributes of a field that determine what kind of data it contains.  Size and Type are examples.


filter <definition>
filter

A set of criteria applied to records in order to create a subset of the records.


foreign key <definition>
foreign key

One or more table fields that refer to the primary key field or fields in another table.  For example, a list of valid part numbers would contain a foreign key to an inventory table containing references to valid part numbers.  Used when establishing referential integrity for a database.


foreign table <definition>
foreign table

A database table used to contain foreign keys.  Generally, you use a foreign table to establish or enforce referential integrity.  The foreign table is usually on the many side of a one-to-many relationship.  An example of a foreign table is a table of state codes or customer orders.


form <definition>
form

A window or dialog box.  Forms are containers for controls.  A multiple-document interface (MDI) form can also act as a container for child forms and some controls.


forward scroll <definition>
forward scroll

Move toward the end (EOF) of a Recordset object.


forward-only scrolling snapshot <definition>
forward-only scrolling snapshot

A snapshot-type Recordset object in which records can be searched only from beginning to end; the current record position can't be moved back to the first record.  Forward-scrolling snapshots reduce processing overhead on remote databases. For example, you can use a forward-only snapshot on an attached remote table to quickly process data in one pass, such as when you’re building a customized report.


FROM clause <definition>
FROM clause

The part of an SQL statement that indicates the location of the data to be examined by the query by specifying which database tables are to be included in the search for the data you want.  The location specified by a FROM clause is sometimes called the domain.


Function procedure <definition>
Function procedure

A procedure that performs a specific task within a Visual Basic program and returns a value.  A Function procedure begins with a Function statement and ends with an End Function statement.


group <definition>
group

A collection of user accounts in a Workgroup object, identified by group name and personal identifier (PID).  Permissions assigned to a group apply to all users in the group.


GUID data type <definition>
GUID data type

Globally Unique Identifier/Universally Unique Identifier.  A unique identification string used with remote procedure calls. Every interface and object class uses a GUID for identification. A GUID is a 128-bit value. For example 12345678-1234-1234-1234-123456789ABC is a syntactically correct GUID. The GUIDs on the client and server must match for the client and server to bind. Vendors of OLE objects can request that Microsoft allocate one or more sets of 256 GUIDs for their exclusive use. Alternatively, if you have a network card, you can run a tool named UUIDGEN.EXE, which provides a set of 256 GUIDs based on the time of day, the date, and a unique number contained in your network card.


identifier <definition>
identifier

An element of an expression that refers to the value of a field or property.


inconsistent <definition>
inconsistent

The state of a multiple-table Recordset object that enables you to update all fields (columns).  For example, in a Recordset created by joining two tables in a one-to-many join (as in a Customers and Orders table), you can update Orders.CustomerID so that it doesn’t match Customers.CustomerID.


index  <definition>
index

A dynamic cross-reference of one or more table data fields (columns) that permits faster retrieval of specific records from a table.  As records are added, changed, or deleted, the database management system automatically updates the index to reflect the changes.

When used with a table-type Recordset object, the current index determines the order in which data records are returned to the Recordset.  A table may have several indexes defined for its data.

A data access object that represents an index for a TableDef object.


index specification

See pseudo index.


initialization file <definition>
initialization file

An ASCII text file used to contain parameters for configuring Windows-based applications, the Microsoft Jet database engine, or Microsoft Windows itself.  Generally, an initialization file uses the extension .INI and is named after the executable program that uses it.  For example, a program named TESTING.EXE would expect an initialization file called TESTING.INI.


inner join <definition>
inner join

A join in which records from two tables are combined and added to a Recordset only if the values of the joined fields meet a specified condition.  For instance, an equi-join is an inner join in which the values of the joined fields must be equal.


installable ISAM <definition>
installable ISAM

A driver you can specify that allows access to external database formats such as dBASE, Microsoft Excel, and Paradox.  ISAM is an acronym for Indexed Sequential Access Method.  The Microsoft Jet database engine installs (loads) these ISAM drivers when referenced by your application.  The location of these drivers is maintained in the Microsoft Windows registration database.


instance <definition>
instance

Any one of a set of objects sharing the same class.  For example, multiple instances of a Form class share the same code and are loaded with the same controls with which the Form class was designed.  During run time, the individual properties of controls on each instance can be set to different values.


Integer data type <definition>
Integer data type

A fundamental data type that holds integer numbers.  An Integer variable is stored as a 16-bit (2-byte) number ranging in value from -32,768 to 32,767.  The type-declaration character is % (ANSI character 37).


intrinsic constant <definition>
intrinsic constant

A constant provided by an application.  For example, Visual Basic constants are listed in the Visual Basic (VB) object library and can be viewed using the Object Browser.


join <definition>
join

A database operation that combines some or all records from two or more tables, such as an equi-join, outer join, or self-join. Generally, a join refers to an association between a field in one table and a field of the same data type in another table.  You create a join with an SQL statement.

When you define a relationship between two tables, you create a join by specifying the primary and foreign table fields.  When you add a table to a query, you need to create a join between appropriate fields in the SQL statement that defines the query.


left outer join <definition>
left outer join

An outer join in which all the records from the left-hand side of the LEFT JOIN operation in the query's SQL statement are added to the Recordset object, even if there are no matching values in the joined field from the right-hand table.  Records from the right-hand table are combined with those from the left-hand table only when there are matching values in the joined fields.  When a left-side record has no match, a row of NULL values is joined on the right side.


left join <definition>
left join

A left outer join includes 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.


Legend

 Applies only to object.

 Applies only to collection.

 Applies to both object and collection.


linked object <definition>
linked object

An object that is created in another application and linked to a Visual Basic application.  When you link an object, you insert a placeholder in your application.  Unlike an embedded object, a linked object's data is actually stored in and managed by the application that created it.


linked table <definition>
linked table

See attached table.


locked <definition>
locked

The condition of a data page, Recordset object, or Database object that makes it read-only to all users except the one who is currently entering data in it.


Long Binary <definition>
Long Binary

A fundamental data type that holds long-integer values.  A Long variable is stored as a 32-bit (4-byte) number ranging in value from -2,147,483,648 to 2,147,483,647.  The type-declaration character is & (ANSI character 38).


Long data type <definition>
Long data type

A four-byte integer (a whole number between -2,147,483,648 and 2,147,483,647, inclusive).


make table query <definition>
make table query

An action query that creates a new table from the Recordset object of an existing query.


many-only table <definition>
many-only table

A table that doesn’t appear as the "one" side of any join in a query.


Memo object <definition>
Memo object

An object you can use to hold the contents of a table field that has a Memo field data type.  Memo objects can contain up to 1.2GB of data.


Memo data type <definition>
Memo data type

A field data type.  Such fields can contain up to1.2GB of data.


message <definition>
message

A packet of information passed from one application to another.


method <definition>
method

A reserved word similar to a statement or function that operates on specific objects.


Microsoft Jet database engine <definition>
Microsoft Jet database engine

A database management system that retrieves data from and stores data in user and system databases.  The Jet engine can be thought of as a data manager component with which other data access systems, such as Microsoft Access and Visual Basic, are built.


multiuser database <definition>
multiuser database

A database that permits more than one user to access and modify the same set of data at the same time.  In some cases, the additional "user" may be another instance of your application or another application running on your system that accesses the same data as some other application.


normalize <definition>
normalize

To minimize the duplication of information in a relational database through effective table design.


Null <definition>
Null

A value that indicates missing or unknown data.  Null values can be entered in fields for which information is unknown and in expressions and queries.  In Visual Basic, the Null keyword indicates a Null value.  Some fields, such as those defined as containing the primary key, can’t contain null values.


null field <definition>
null field

A field containing no characters or values.  A null field isn't the same as a zero-length string ("") or a field with a value of 0.  A field is set to null when the content of the field is unknown.  For example, a Date Completed field in a task table would be left null until a task is completed.


Null propagation <definition>
Null propagation

The ability of Null expressions, when used with most operators, to take precedence over other values in expressions.


numeric expression <definition>
numeric expression

Any expression that can be evaluated as a number.  Elements of the expression can include any combination of keywords, variables, constants, and operators that result in a number.


Object Browser <definition>
Object Browser

A dialog box you can use to examine the contents of an object library to get information about the objects provided.


object data type <definition>
object data type

A collective term for objects defined by Visual Basic or by the Microsoft Jet database engine.  An object data type may represent objects that are stored in a field in the database, for example.   Object data types include any of the OLE data types or other long binary fields.


object expression <definition>
object expression

An expression that specifies a particular object.  This expression can include any of the object's containers.  For example, if your application has an Application object that contains a Document object that contains a Text object, the following are valid object expressions:

Application.Document.Text

Application.Text

Document.Text

Text


object library <definition>
object library

A file with the .OLB extension that provides information to OLE Automation controllers (like Visual Basic) about available OLE Automation objects.  You can use the Object Browser to examine the contents of an object library to get information about the objects provided.


object variable <definition>
object variable

A variable that contains a reference to an object.


ODBC (Open Database Connectivity) <definition>
ODBC (Open Database Connectivity)

A standard protocol that permits applications to connect to a variety of external database servers or files.  ODBC drivers used by the Microsoft Jet database engine permit access to Microsoft SQL Server and several other external databases.

The ODBC applications programming interface (API) may also be used to access ODBC drivers and the databases they connect to without using the Jet engine.


ODBC data source <definition>
ODBC data source

A term used to refer to a database or database server used as a source of data. ODBC data sources are referred to by their Data Source Name.  Data sources can be created using the Windows control panel or the RegisterDatabase method.


ODBC driver <definition>
ODBC driver

A dynamic-link library (DLL) used to connect a specific Open Database Connectivity data source with another (client) application.


OLE <definition>
OLE

Object linking and embedding: The technology that enables you to create applications that contain components from various other applications.


OLE Automation object <definition>
OLE Automation object

An object in an application that can be accessed and manipulated by another application, provided both applications support OLE version 2.0.  For example, Microsoft Access can create and manipulate OLE Automation objects within Microsoft Word or Microsoft Excel.


OLE Object data type <definition>
OLE Object data type

A field data type you use for objects created in other applications that can be linked or embedded in a Microsoft Jet database.


one-to-many relationship <definition>
one-to-many relationship

An association between two tables in which:

·	The primary key value of each record in the primary table corresponds to the value in the matching field or fields of a number of records (or no record) in the related table.

·	The primary key value of each record in the related table is null or corresponds to the value in the matching field or fields of one and only one record in the primary table.


one-to-one relationship <definition>
one-to-one relationship

An association between two tables in which:

·	The primary key value of each record in the primary table corresponds to the value in the matching field or fields of no more than one record in the related table.

·	The primary key value of each record in the related table is null or corresponds to the value in the matching field of one and only one record in the primary table.


optimistic <definition>
optimistic

A type of locking in which the data page containing one or more records, including the record being edited, is unavailable to other users only while the record is being updated by the Update method, but is available between the Edit and Update methods.  Optimistic locking is used when accessing ODBC databases or when the LockEdits property of the Recordset object is set to False.


outer join <definition>
outer join

A join in which all the records from one table are added to the Recordset object, even if there are no matching values in the joined field from the second table.  Records from the second table are combined with those from the first table only when there are matching values in the joined fields.  Null records are joined when there is no match.


page <definition>
page

A portion of the database in which record data is stored.  Depending on the size of the records, a page may contain more than one record.  In Microsoft Jet databases (.mdb), a page is 2048 (2K) bytes in length.


parameter <definition>
parameter

An element containing a value that you can change to affect the results of the query.  For example, a query returning data about an employee might have a parameter for the employee's name.  You can then use one QueryDef object to find data about any employee by setting the parameter to a specific name before running the query.


parameter query <definition>
parameter query

A query that requires you to provide one or more criteria values, such as Redmond for City, before the query is run.  A parameter query isn’t, strictly speaking, a separate kind of query; rather, it extends the flexibility of other queries.


parse <definition>
parse

To identify the parts of a statement or expression and then validate those parts against the appropriate language rules.


pass-through query <definition>
pass-through query

An SQL-specific query you use to send commands directly to an SQL database server (such as Microsoft SQL Server).  With pass-through queries, you work with the tables on the server instead of attaching them.  Pass-through queries are used to execute SQL queries and system-specific commands written using SQL dialects known only to the server.

A pass-through query may or may not return records.  If it does, they are always returned in a snapshot.


permission <definition>
permission

One or more attributes that specify what kind of access a user has to data or objects in a database.  For example, a table or query with Read Data permission permits a user to retrieve but not edit data in the table or query.


persistent object <definition>
persistent object

An object stored in the database; for example, a database table or QueryDef object.  Dynaset-type or snapshot-type Recordset objects are not considered persistent objects because they are created in memory as needed.


personal identifier (PID) <definition>
personal identifier (PID)

A case-sensitive alphanumeric string 4-20 characters long that the Microsoft Jet database engine uses in combination with the account name to identify a user or group in a Workgroup object.  You provide the PID and the account name when you create a new user or group.


pessimistic <definition>
pessimistic

A type of locking in which the page containing one or more records, including the record being edited, is unavailable to other users when you use the Edit method and remains unavailable until you use the Update method.  Pessimistic locking is enabled when the Lockedits property of the Recordset object is set to True.


primary key <definition>
primary key

One or more fields whose value or values uniquely identify each record in a table.  You can have only one primary key.  An Employees table, for example, could use the social security number for the primary key.


primary table <definition>
primary table

A database table used to contain primary keys.  Generally, a primary key table is used to establish or enforce referential integrity.  The primary table is usually on the one side of a one-to-many relationship with a foreign table.


property <definition>
property

A named attribute of an object.  Properties define object characteristics such as size, color, and screen location, or the state of an object, such as enabled or disabled.


pseudo index <definition>
pseudo index

A dynamic cross-reference of one or more table data fields (columns) that permits an ODBC table (server table) without a unique index to be edited. A pseudo index is not an actual index on the server table, but a cross-reference that the Microsoft Jet database engine can use to support table updates. A pseudo index is necessary only if an ODBC table doesn’t have a unique index, which allows the table to be updated. You don’t need a pseudo index if the table already has a unique index or if there is no need to update the table. (Tables cannot have a unique index if they were intended to be read-only.) In previous versions, pseudo indexes were called index specifications.


query <definition>
query

A formalized instruction to a database to either return a set of records or perform a specified action on a set of records as specified in the query.  For example, the following SQL query statement returns records:

SELECT CompanyName FROM Publishers WHERE State = 'NY'

You can create and run select, action, crosstab, parameter, and SQL-specific queries:


query parameter data types <definition>
query parameter data types

The set of data types for a field in a parameter query.  The Microsoft Jet database engine has 13 query parameter data types:

Category	Data type

Table fields	Currency, Date/Time, Memo, OLE Object, Text, and Yes/No correspond to the same data types in table fields.

Number	Byte, Single, Double, Integer, and Long Integer correspond to the five Field Size options of the Number data type in table fields.

Generic 	Value is a Generic data type that doesn't accept any type of data.

Binary	You can use the Binary data type in parameter queries directed to attached tables that recognize it.


QueryDef object <definition>
QueryDef object

A data access object that contains the SQL statement that describes a query and its properties, such as DateCreated and ODBCTimeout.  Querydef objects with an ODBC Connect property may contain an SQL statement that can only be executed on an external database like Microsoft SQL Server.  This is called an SQL pass-through query.


read-only <definition>
read-only

A type of access to data whereby information can be retrieved but not modified.


record <definition>
record

A set of related data about a person, place, event, or some other item.  Table data is stored in records (rows) in the database.  Each record is composed of a set of related fields (columns) [image] each field defining one attribute of information for the record.  Taken together, a record defines one specific unit of retrievable information in a database.


recordset <definition>
recordset

A logical set of records.  The three types of Recordset objects are dynaset, snapshot, and table.


recordset population <definition>
recordset population

The process of loading Recordset records into memory.

When a dynaset- or snapshot-type Recordset is first opened, it usually contains only one record, even if the query returns additional records.  As your code moves the current record forward (toward EOF) through the Recordset, additional records are fetched and become members of either the keyset (dynaset) or the set of records in memory (snapshot).  This process is called recordset population.  Executing the MoveLast or FindLast methods or setting the PercentPosition property to 100 completely populates the Recordset.

In Visual Basic version 4.0, when using SQL Passthrough against ODBC data sources, the Recordset isn’t fully populated when first opened (as it was in Visual Basic version 3.0).


referential integrity <definition>
referential integrity

Rules that you set to establish and preserve relationships between tables when you add, change, or delete records.  Enforcing referential integrity prohibits users from adding records to a related table for which there is no primary key, changing values in a primary table that would result in orphaned records in a related table, and deleting records from a primary table when there are matching related records.

If you select the Cascade Update Related Fields or Cascade Delete Related Records option for a relationship, the Microsoft Jet database engine allows changes and deletions but changes or deletes related records to make sure the rules are still enforced.


relationship <definition>
relationship

An association established between common fields (columns) in two tables.  A relationship can be one-to-one or one-to-many.


requery <definition>
requery

To rerun a query to reflect changes to the records, retrieve newly added records, and eliminate deleted records.


reserved word <definition>
reserved word

A word that is part of a language, such as the Microsoft Jet database engine SQL language.  Reserved words include the names of statements, predefined functions and data types, methods, operators, and objects.  Examples include SELECT, UPDATE, BETWEEN, SET and INSERT.  Don’t use reserved words for variable or object names.


right join <definition>
right join

A right outer join includes 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.


right outer join <definition>
right outer join

An outer join in which all the records from the right side of the RIGHT JOIN operation in the query's SQL statement are added to the Recordset object, even if there are no matching values in the joined field from the left table.  Records from the left table are combined with those from the right table only when there are matching values in the joined fields.


row <definition>
row

A set of related columns or fields used to hold data.  A row is synonymous with a record in the Microsoft Jet database engine.  A table is composed of zero or more rows of data.


run time <definition>
run time

The time when code is running.  During run time, you interact with the code as a user would.


run-time error <definition>
run-time error

An error that occurs when code is running.  A run-time error results when a statement attempts an invalid operation.


scope <definition>
scope

The attribute of a variable or procedure that determines which sections of which modules recognize it.  There are three levels of scope: global, module, and procedure.  Variables that you declare globally can be accessed by any module, while variables that you declare in a specific module can be used only within that module.  Also, variables that you declare in a Sub or Function procedure can be used only in that particular procedure.


secure workgroup <definition>
secure workgroup

A Microsoft Jet database Workgroup object to which users log on with a user name and password and in which access to database objects is restricted according to permissions specified for user accounts and groups.


security <definition>
security

A set of features used to specify or restrict the access that specified users or user groups have to data and objects in a database.


select query <definition>
select query

A query that asks a question about the data stored in your tables and returns a Recordset object without changing the data.  Once the Recordset data is retrieved, you can examine and make changes to the data in the underlying tables.  In contrast, action queries can make changes to your data, but they don't return data records.


self-join <definition>
self-join

A join in which records from a table are combined with other records for the same table where there are matching values in the joined fields.  A self-join can be an equi-join or an outer join.  A self-join is useful when you ask hierarchical questions, such as a query that shows all the managers and the people who report to them.


server <definition>
server

The database management system designed to share data with client applications; servers and clients are often connected over a network.  A database server usually contains and manages a central repository of data that remote client applications can retrieve and manipulate.


session <definition>
session

A session delineates a sequence of operations performed by the Microsoft Jet database engine.  A session begins when a user logs in and ends when a user logs off.  All operations performed during a session form one transaction scope and are subject to permissions determined by the login user name and password.  Sessions are implemented as Workspace objects by the Jet engine.


Short data type <definition>
Short data type

A Microsoft Jet database engine fundamental data type that corresponds to the Integer data type defined by Visual Basic for Applications.  A Short variable is stored as a 16-bit (2-byte) number ranging in value from -32,768 to 32,767.  The type-declaration character is % (ANSI character 37).


Single data type <definition>
Single data type

A fundamental data type that holds single-precision floating-point numbers in IEEE format.  A Single variable is stored as a 32-bit (4-byte) number ranging in value from -3.402823E38 to -1.401298E-45 for negative values, from 1.401298E-45 to 3.402823E38 for positive values, and 0.  The type-declaration character is ! (ANSI character 33).


snapshot <definition>
snapshot

A static copy of a set of records retrieved from the database and copied into memory.  Snapshot-type Recordset objects can be created from a base table, a query, or another recordset.  Snapshots are not updatable.  All SQL pass-through queries return snapshots.


sort order <definition>
sort order

A sequencing principle used to order data; for example, alphabetic, numeric, ascending, descending, and so on.


SQL database <definition>
SQL database

A database that can be accessed through use of Open Database Connectivity (ODBC) data sources.


SQL-specific query <definition>
SQL-specific query

A query that can be created only by writing an SQL statement.  Subqueries and pass-through, union, and data-definition queries are SQL-specific queries.


SQL statement/string <definition>
SQL statement/string

An expression that defines a Structured Query Language (SQL) command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY.  SQL strings and statements are typically used in queries, Recordset objects, and aggregate functions but can also be used to create or modify a database structure.


standard deviation <definition>
standard deviation

A parameter that indicates the way in which a probability function is centered around its mean and that is equal to the square root of the moment in which the deviation from the mean is squared.  Used to describe sets of values to determine how far the value varies from the arithmetic mean or average.


standard naming conventions <definition>
standard naming conventions

A set of specific rules for naming Microsoft Jet database engine objects.  Names can be up to 64-characters long and can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation mark (!), a backquote character (`), and brackets ([ ]).  You also can't use leading spaces or control characters (ASCII values 0 through 31).


string comparison <definition>
string comparison

The use of an operator to determine whether one string is greater than or equal to another string.  If you use Option Compare Text in the Declarations section of a module, string comparisons are not case-sensitive.  If you use Option Compare Binary, comparisons are case-sensitive.  If you use Option Compare Database, the comparison method is set by the current database.


String data type <definition>
String data type

A fundamental data type that holds character information.  A String variable can contain approximately 65,535 bytes (64K), is either fixed-length or variable-length, and contains one character per byte.  Fixed-length strings are declared to be a specific length.  Variable-length strings can be any length up to 64K, less a small amount of storage overhead.

The type-declaration character for the String data type is $.


string expression <definition>
string expression

Any expression that evaluates to a sequence of contiguous characters.  Elements of the expression can include a function that returns a string, a string literal, a string constant, a string variable, a string Variant, or a function that returns a string Variant (VarType 8).


Structured Query Language (SQL) <definition>
Structured Query Language (SQL)

A language used in querying, updating, and managing relational databases.  SQL can be used to retrieve, sort, and filter specific data to be extracted from the database.

You can use SQL SELECT statements anywhere a table name, query name, or field name is accepted.  For example, you can use an SQL statement in place of a table name in the OpenRecordset method.


syntax error <definition>
syntax error

An error that occurs when you enter a line of code that Visual Basic doesn't recognize.

Note   Syntax rules for individual keywords are defined in the Syntax section of the associated Help topic.  To get Help on a keyword from within the development environment, select the keyword and press F1.


system object <definition>
system object

A database object defined by the Microsoft Jet database engine, such as the table MSysIndexes.


table <definition>
table

The basic unit of data storage in a relational database.  A table stores data in records (rows) and fields (columns) and is usually about a particular category of things, such as employees or parts. Also called a base table.

A table-type Recordset is a logical representation of a base table in a database.


TableDef object <definition>
TableDef object

An object in a database that defines the structure of a table.  In contrast, a table is the physical container of the data in a database.


temporary disk <definition>
temporary disk

The disk or directory, identified by the TEMP operating system environment variable, where the Microsoft Jet database engine stores temporary files and result sets.  Also known as a temporary drive.  Although the TEMP environment variable can point to a RAM disk, this isn’t recommended.


TEMP <definition>
TEMP

A TEMP environment variable is set by your AUTOEXEC.BAT file when you start your system.  Generally, TEMP points to an area on your hard disk used by Microsoft Windows and other programs, like the Microsoft Jet database engine, to store information that doesn't need to be saved after you shut down your system.  For example, the following line in your AUTOEXEC.BAT file points the TEMP environment variable to the D:\TEMPAREA directory:

SET TEMP=D:\TEMPAREA


Text data type <definition>
Text data type

A field data type.  Text fields can contain up to 255 characters or the number of characters specified by the Size property of the Field object, whichever is less.


transaction <definition>
transaction

A series of changes made to a database's data and schema.  Mark the beginning of a transaction with the BeginTrans statement, commit the transaction using the CommitTrans statement, and undo all your changes since BeginTrans using the Rollback statement.

There is an implicit transaction while action queries are running.  If a query doesn’t complete for any reason, it is automatically rolled back.

Transactions are optional and can be nested up to five levels.  Transactions increase the speed of data changing operations and enable changes to be reversed easily.

Transactions are global to the referenced database object's Workspace.


two's complement <definition>
two's complement

A type of base-2 notation used to represent positive and negative in which negative values are formed by complementing all bits and adding 1 to the result.  Complementing is done by changing all 1s to 0, and all 0s to 1.


union query <definition>
union query

A SQL-specific select query that creates a snapshot-type Recordset object containing data from all specified records in two or more tables with any duplicate records removed.  To include the duplicates, add the keyword ALL.

For instance, a union query of the Customers table and the Suppliers table results in a snapshot-type Recordset that contains all suppliers that are also customers.


update <definition>
update

The process that saves changes to data in a record.  Until the record is saved, changes are stored in a temporary record called the copy buffer.  The UPDATE clause in an SQL Statement changes data values in one or more records (rows) in a database table.


update query <definition>
update query

An action query that changes a set of records according to criteria you specify.  An update query doesn’t return any records.


user account <definition>
user account

An account identified by a user name and personal identifier (PID) that is created to manage access to database objects in a Microsoft Jet database Workgroup object.


validation <definition>
validation

The process of checking whether entered data meets certain conditions or limitations.


validation properties <definition>
validation properties

Properties used to set conditions on table fields and records.  Validation properties include ValidationRule, Required, and AllowZeroLength.


validation rule <definition>
validation rule

A rule that sets limits or conditions on what can be entered in one or more fields.  Validation rules can be set for a Field or a TableDef object.  Validation rules are checked when you update a record containing fields requiring validation.  If the rule is violated, a trappable error results.


variable <definition>
variable

A named storage location that can contain data that can be modified during program execution.  Each variable has a name that uniquely identifies it within its level of scope.  A data type can be specified or not.

Variable names:

Must begin with an alphabetic character.

Can't contain an embedded period or type-declaration character.

Must be unique within the same scope.

Must be no longer than 255 characters.


Variant data type <definition>
Variant data type

A special data type that can contain numeric, string, or date data as well as the special values Empty and Null.  The VarType function defines how the data in a Variant is treated.  All variables become variant types if not explicitly declared as some other type.


variant expression <definition>
variant expression

Any expression that can evaluate to numeric, string, or date data as well as the special values Empty and Null.


VarType <definition>
VarType

The VarType function returns a value that provides information about the type of data stored in a variable.  The following table shows the equivalent constant, the return value, and a description of the variable:

Constant	Value	Description

vbEmpty	0	Uninitialized

vbNull	1	Contains no valid data

vbInteger	2	Integer

vbLong	3	Long integer

vbSingle	4	Single-precision floating-point number

vbDouble	5	Double-precision floating-point number

vbCurrency	6	Currency

vbDate	7	Date

vbString	8	String

vbObject	9	OLE Automation object

vbError	10	Error

vbBoolean	11	Boolean

vbVariant	12	Variant (used only for arrays of Variants)

vbDataObject	13	Non-OLE Automation object

vbByte	17	Byte

vbArray	8192	Array

Note   The constant vbArray combined with any other constant indicates an array of that type of variable.


WHERE clause <definition>
WHERE clause

The part of an SQL statement that specifies which records to retrieve.  The WHERE clause limits the scope of the query and specifies which columns are used to join multiple tables.  When using the Microsoft Jet database engine to perform the query, use the JOIN clause to join multiple tables.


wildcard characters <definition>
wildcard characters

The asterisk (*), question mark (?), number sign (#), exclamation point (!), hyphen (-), and brackets ([ ]) are wildcard characters.  You can use these characters in queries and expressions to include all records, filenames, or other items that begin with specific characters or match a certain pattern.  You can also use wildcard characters and matching characters to further refine a search when using an SQL statement.

Symbol	Example	Usage
*	wh* finds what, white, and why *at finds cat, bat, and what	Like the MS-DOS asterisk (*) wildcard character, this asterisk matches any number of characters.  But unlike MS-DOS, it can be used anywhere in the character string.
?	b?ll finds ball, bell, and bill	Like the MS-DOS ? wildcard character, this symbol matches any single character.
#	1#3 finds 103, 113, 123	Matches any single digit.
[ ]	b[ae]ll finds ball and bell but not bill	Matches any single character within the brackets.
!	b[!ae]ll finds bill and bull but not bell or ball	Matches any character not in the list.
-	b[a-c]d finds bad, bbd, and bcd	Matches any one of a range of characters.
Note   The wildcard characters * (asterisk), ? (question mark), # (number sign), and [ (opening bracket) can match themselves only if enclosed in brackets.


workgroup <definition>
workgroup

A group of users in a multiuser environment who share data and the same system database (typically the SYSTEM.mdw file).


workgroup administrator <definition>
workgroup administrator

A member of the group Admins in a Microsoft Jet database Workgroup object.  A workgroup administrator can always obtain full permissions for any database object created in the Workgroup.


workspace <definition>
workspace

A Workspace object defines a session for a specific user.  When Visual Basic starts, a default Workspace is created with a password of "" and username of "Admin".


Yes/No data type <definition>
Yes/No data type

A field data type that contains a Boolean (True/False or yes/no) value.


zero-length string <definition>
zero-length string

A string containing no characters.  The Len function of a zero-length string returns 0.


ALTER TABLE Statement (SQL)

Modifies the design of a table after it has been created with the CREATE TABLE statement.

Note   The Microsoft Jet database engine doesn't support the use of ALTER TABLE, or any of the data definition language (DDL) statements, with non-Jet databases. Use the data access object Create methods instead.

Syntax

ALTER TABLE table {ADD {COLUMN field type[(size)] [CONSTRAINT index] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }

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:

ALTER TABLE Employees ADD COLUMN Notes TEXT(25)

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

Use the 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 the 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.


CONSTRAINT Clause (SQL)

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.

Note   The Microsoft Jet database engine doesn't support the use of CONSTRAINT, or any of the data definition language (DDL) statements, with non-Jet databases. Use the data access object Create methods instead.

Syntax

Single-field index:

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

Multiple-field index:

CONSTRAINT name
{PRIMARY KEY (primary1[, primary2 [, ...]]) |
UNIQUE (unique1[, unique2 [, ...]]) |
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.
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 [image] by default, the database engine behaves as if the foreign table's primary key is the referenced fields.


CREATE INDEX Statement (SQL)

Creates a new index on an existing table.

Note   For non-Jet databases, the Microsoft Jet database engine doesn't support the use of CREATE INDEX (except to create a pseudo index on an ODBC attached table) or any of the data definition language (DDL) statements. Use the data access object Create methods instead. For more information, see the Remarks section.

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 the index will be created on.

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 an attached 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 attached 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.


CREATE TABLE Statement (SQL)

Creates a new table.

Note   The Microsoft Jet database engine doesn't support the use of CREATE TABLE, or any of the data definition language (DDL) statements, with non-Jet database engine databases. Use the data access object Create methods instead.

Syntax

CREATE TABLE table (field1 type [(size)] [index1] [, field2 type [(size)] [index2] [, ...]] [, 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

You can use the CREATE INDEX statement to create indexes on existing tables.


DROP Statement (SQL)

Deletes an existing table from a database or deletes an existing index from a table.

Note   The Microsoft Jet database engine doesn't support the use of DROP, or any of the data definition language (DDL) statements, with non-Jet databases. Use the data access object Create methods instead.

Syntax

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.


SELECT Statement (SQL)

Instructs the Microsoft Jet database engine to return information from the database as a set of records.

Syntax

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

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 to retrieve data from. 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 not in the current database.

Remarks

To perform this operation, the 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, precede 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 Department from the Employees table and SupvName from the Supervisors table:

SELECT Employees.Department, Supervisors.SupvName

FROM Employees INNER JOIN Supervisors

WHERE Employees.Department = Supervisors.Department;

When a Recordset object is created, the 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 Head Count 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 working with.


FROM Clause (SQL)

Specifies the tables or queries that contain the fields listed in the SELECT statement.

Syntax

SELECT fieldlist
FROM tableexpression [IN externaldatabase]

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 pathname 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.

For improved performance and ease of use, it's recommended that you use an attached table instead of an IN clause to retrieve data from an external database.

The following example shows how you can retrieve data from the Employees table:

SELECT LastName, FirstName
FROM Employees;


IN Clause (SQL)

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 Jet database.

Syntax

To identify a destination table:

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

To identify a source table:

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 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 filename is derived from the destination or tableexpression arguments.

To specify a non-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 an attached 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.


WHERE Clause (SQL)

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

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

The Microsoft Jet database engine selects the records that meet the conditions listed in the WHERE clause. 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 ([ ]):

SELECT ProductID, UnitsInStock
FROM Products
WHERE UnitsInStock <= ReorderLevel;

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 Jet database engine. For example, May 10, 1994, is written 10/5/94 in the United Kingdom and 5/10/94 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, 1994 in a United Kingdom database, you must use the following SQL statement:

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

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

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

For code in the United Kingdom:

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

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.


GROUP BY Clause (SQL)

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.

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 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.


HAVING Clause (SQL)

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.

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(UnitsIn Stock)
FROM Products
GROUP BY CategoryID
HAVING Sum(UnitsIn Stock) > 100 AND LIKE "BOS*";

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


ORDER BY Clause (SQL)

Sorts a query's resulting records on a specified field or fields in ascending or descending order.

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. The Jet database engine doesn't sort on fields of these types.

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.


ALL, DISTINCT, DISTINCTROW, TOP Predicates (SQL)

Specifies records selected with SQL queries.

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 Microsoft Jet database engine selects all of the records that meet the conditions in the SQL statement. 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 Last Name 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 Customer ID field. The Customers table contains no duplicate Customer ID 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 INNER JOIN Orders ON 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	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 the query's updatability.
table	The name of the table from which records are retrieved.


DELETE Statement (SQL)

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

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 especially useful when you want to delete many records.

To drop an entire table from the database, you can use the Execute method with a DROP  statement. If you delete the table, however, the structure is 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 in 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 examine the results of a select query that uses the same criteria, and then run the delete query.

Maintain backup copies of your data at all times. If you delete the wrong records, you can retrieve them from your backup copies.


INNER JOIN Operation (SQL)

Combines records from two tables whenever there are matching values in a common field.

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. These are the most common type of join. They 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 Counter and Long Integer 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 Category ID field:

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

In the preceding example, Category ID 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 [image] 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.


INSERT INTO Statement (SQL)

Adds a record or multiple records to a table. This is referred as an append query.

Syntax

Multiple-record append query:

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

Single-record append query:

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 double 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. When you don't specify each field, the default value or Null is inserted for missing columns. 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 target table specified.

The source or target table may specify a table or a query. If a query is specified, the Microsoft Jet database engine appends 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 Jet database engine won't append the records.

If you append records to a table with a Counter field and you want to renumber the appended records, don't include the Counter field in your query. Do include the Counter 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 will fail. Use an additional INSERT INTO statement with a VALUES clause for each additional record you want to create.


LEFT JOIN, RIGHT JOIN Operations (SQL)

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

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 Category ID 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, Category ID 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 [image] 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.

multiple 	If you try to join fields containing Memo or OLE Object data, an error occurs.


PARAMETERS Declaration (SQL)

Declares the name and data type of each parameter in a parameter query.

Syntax

PARAMETERS name datatype [, name datatype [, ...]]

The PARAMETERS declaration has these parts:

Part	Description

name	The name of the parameter. Assigned to the Name property of the Parameter object and used to identify this parameter in the Parameters collection. You can use name as a string that is displayed in a dialog box while your application runs the query. Use brackets ([ ]) to enclose text that contains spaces or punctuation. For example, [Low price] and [Begin report with which month?] are valid name arguments.

datatype	One of the primary Microsoft Jet SQL data types or their synonyms.

Remarks

For queries that you run regularly, you can use a PARAMETERS declaration to create a parameter query. A parameter query can help automate the process of changing query criteria. With a parameter query, your code will need to provide the parameters each time the query is run.

The PARAMETERS declaration is optional but when included precedes any other statement, including SELECT.

If the declaration includes more than one parameter, separate them with commas. The following example includes two parameters:

PARAMETERS [Low price] Currency, [Beginning date] DateTime;

You can use name but not datatype in a WHERE or HAVING clause. The following example expects two parameters to be provided and then applies the criteria to records in the Orders table:

PARAMETERS [Low price] Currency,
[Beginning date] DateTime;
SELECT OrderID, OrderAmount
FROM Orders
WHERE OrderAmount > [Low price]
AND OrderDate >= [Beginning date];


PROCEDURE Clause (SQL)

Defines a name and optional parameters for a query.

Syntax

PROCEDURE name [param1 datatype[, param2 datatype[, ...]]

The PROCEDURE clause has these parts:

Part	Description

name	A name for the procedure. It must follow standard naming conventions.

param1, param2	One or more field names or parameters. For example:

PROCEDURE Sales_By_Country [Beginning Date] DateTime, [Ending Date] DateTime;

For more information on parameters, see PARAMETERS.

datatype	One of the primary Microsoft Jet SQL data types or their synonyms.

Remarks

An SQL procedure consists of a PROCEDURE clause (which specifies the name of the procedure), an optional list of parameter definitions, and a single SQL statement. For example, the procedure Get_Part_Number might run a query that retrieves a specified part number.

Notes

If the clause includes more than one field definition (that is, param-datatype pairs), separate them with commas.

The PROCEDURE clause must be followed by an SQL statement (for example, a SELECT or UPDATE statement).


SELECT...INTO Statement (SQL)

Creates a make-table query.

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 will result.

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.


SQL Subqueries

A subquery is a SELECT statement nested inside a SELECT, SELECT...INTO, INSERT...INTO, DELETE, or UPDATE statement or inside another subquery.

Syntax

You can use three forms of syntax to create a subquery:

comparison [ANY | ALL | SOME] (sqlstatement)

expression [NOT] IN (sqlstatement)

 [NOT] EXISTS (sqlstatement)

A subquery has these parts:

Part	Description

comparison	An expression and a comparison operator that compares the expression with the results of the subquery.

expression	An expression for which the result set of the subquery is searched.

sqlstatement	A SELECT statement, following the same format and rules as any other SELECT statement. It must be enclosed in parentheses.

Remarks

You can use a subquery instead of an expression in the field list of a SELECT statement or in a WHERE or HAVING clause. In a subquery, you use a SELECT statement to provide a set of one or more specific values to evaluate in the WHERE or HAVING clause expression.

Use the ANY or SOME predicate, which are synonymous, to retrieve records in the main query that satisfy the comparison with any records retrieved in the subquery. The following example returns all products whose unit price is greater than that of any product sold at a discount of 25 percent or more:

SELECT * FROM Products
WHERE UnitPrice > ANY
(SELECT UnitPrice FROM OrderDetails
WHERE Discount >= .25);

Use the ALL predicate to retrieve only those records in the main query that satisfy the comparison with all records retrieved in the subquery. If you changed ANY to ALL in the above example, the query would return only those products whose unit price is greater than that of all products sold at a discount of 25 percent or more. This is much more restrictive.

Use the IN predicate to retrieve only those records in the main query for which some record in the subquery contains an equal value. The following example returns all products with a discount of 25 percent or more:

SELECT * FROM Products
WHERE ProductID IN
(SELECT ProductID FROM OrderDetails
WHERE Discount >= .25);

Conversely, you can use NOT IN to retrieve only those records in the main query for which no record in the subquery contains an equal value.

Use the EXISTS predicate (with the optional NOT reserved word) in true/false comparisons to determine whether the subquery returns any records.

You can also use table name aliases in a subquery to refer to tables listed in a FROM clause outside the subquery. The following example returns the names of employees whose salaries are equal to or greater than the average salary of all employees having the same job title. The Employees table is given the alias "T1":

SELECT LastName,
FirstName, Title, Salary
FROM Employees AS T1
WHERE Salary >=
(SELECT Avg(Salary)
FROM Employees
WHERE T1.Title = Employees.Title) Order by Title;

In the preceding example, the AS reserved word is optional.

Some subqueries are allowed in crosstab queries [image] specifically, as predicates (those in the WHERE clause). Subqueries as output (those in the SELECT list) are not allowed in crosstabs.


TRANSFORM Statement (SQL)

Creates a crosstab query.

Syntax

TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]

The TRANSFORM statement has these parts:

Part	Description

aggfunction	An SQL aggregate function that operates on the selected data.

selectstatement	A SELECT statement.

pivotfield	The field or expression you want to use to create column headings in the query's result set.

value1, value2	Fixed values used to create column headings.

Remarks

When you summarize data using a crosstab query, you select values from specified fields or expressions as column headings so you can view data in a more compact format than with a select query.

TRANSFORM is optional but when included is the first statement in an SQL string. It precedes a SELECT statement that specifies the fields used as row headings and a GROUP BY clause that specifies row grouping. Optionally, you can include other clauses, such as WHERE, that specify additional selection or sorting criteria. You can also use subqueries as predicates [image] specifically, those in the WHERE clause [image] in a crosstab query.

The values returned in pivotfield are used as column headings in the query's result set. For example, pivoting the sales figures on the month of the sale in a crosstab query would create 12 columns. You can restrict pivotfield to create headings from fixed values (value1, value2 ) listed in the optional IN clause. You can also include fixed values for which no data exists to create additional columns.


UNION Operation (SQL)

Creates a union query, which combines the results of two or more independent queries or tables.

Syntax

[TABLE] query1 UNION [ALL] [TABLE] query2 [UNION [ALL] [TABLE] queryn [ ... ]]

The UNION operation has these parts:

Part	Description

query1-n	A SELECT statement, the name of a stored query, or the name of a stored table preceded by the TABLE keyword.

Remarks

You can merge the results of two or more queries, tables, and SELECT statements, in any combination, in a single UNION operation. The following example merges an existing table named New Accounts and a SELECT statement:

TABLE [New Accounts] UNION ALL
SELECT *
FROM Customers
WHERE OrderAmount > 1000;

By default, no duplicate records are returned when you use a UNION operation; however, you can include the ALL predicate to ensure that all records are returned. This also makes the query run faster.

All queries in a UNION operation must request the same number of fields; however, the fields don't have to be of the same size or data type.

Use aliases only in the first SELECT clause because they are ignored in any others. In the ORDER BY clause, refer to fields by what they are called in the first SELECT clause.

Notes

You can use a GROUP BY and/or HAVING clause in each query argument to group the returned data.

You can use an ORDER BY clause at the end of the last query argument to display the returned data in a specified order.


UPDATE Statement (SQL)

Creates an update query that changes values in fields in a specified table based on specified criteria.

Syntax

UPDATE table
SET newvalue
WHERE criteria;

The UPDATE statement has these parts:

Part	Description

table	The name of the table whose 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';

UPDATE doesn't generate a result set. If you want to know which records will be changed, first examine the results of a select query that uses the same criteria, and then run the update query.


WITH OWNERACCESS OPTION Declaration (SQL)

In a multiuser environment with a secure workgroup, use this declaration with a query to give the user who runs the query the same permissions as the query's owner.

Syntax

sqlstatement
WITH OWNERACCESS OPTION

Remarks

The WITH OWNERACCESS OPTION declaration is optional.

The following example enables the user to view salary information (even if the user doesn't otherwise have permission to view the Payroll table), provided that the query's owner does have that permission:

SELECT LastName,
FirstName, Salary
FROM Employees
ORDER BY LastName
WITH OWNERACCESS OPTION;

If a user is otherwise prevented from creating or adding to a table, you can use WITH OWNERACCESS OPTION to enable the user to run a make-table or append query.

If you want to enforce workgroup security settings and users' permissions, don't include the WITH OWNERACCESS OPTION declaration.

This option requires you to have access to the System.mda file associated with the database. It's really useful only in secured multiuser implementations.


SQL Aggregate Functions (SQL)

Using the SQL aggregate functions, you can determine various statistics on sets of values. You can use these functions in a query and aggregate expressions in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query.

Avg Function

Count Function

Min, Max Functions

StDev, StDevP Functions

Sum Function

Var, VarP Functions


Avg Function (SQL)

Calculates the arithmetic mean of a set of values contained in a specified field on a query.

Syntax

Avg(expr)

The expr placeholder represents a string expression identifying the field that contains the numeric data you want to average or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate or domain aggregate functions).

Remarks

The average calculated by Avg is the arithmetic mean (the sum of the values divided by the number of values). You could use Avg, for example, to calculate average freight cost.

The Avg function doesn't include any Null fields in the calculation.

You can use Avg in a query expression and in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query.


Count Function (SQL)

Calculates the number of records returned by a query.

Syntax

Count(expr)

The expr placeholder represents a string expression identifying the field that contains the data you want to count or an expression that performs a calculation using the data in the field. Operands in expr can include the name of a table field or function (which can be either intrinsic or user-defined but not other SQL aggregate functions). You can count any kind of data, including text.

Remarks

You can use Count to count the number of records in an underlying query. For example, you could use Count to count the number of orders shipped to a particular country.

Although expr can perform a calculation on a field, Count simply tallies the number of records. It doesn't matter what values are stored in the records.

The Count function doesn't count records that have null fields unless expr is the asterisk (*) wildcard character. If you use an asterisk, Count calculates the total number of records, including those that contain null fields. Count(*) is considerably faster than Count([Column Name]). Don't enclose the asterisk in quotation marks (" "). The following example calculates the number of records in the Orders table:

SELECT Count(*)
AS TotalOrders FROM Orders;

If expr identifies multiple fields, the Count function counts a record only if at least one of the fields is not Null. If all of the specified fields are Null, the record isn't counted. Separate the field names with an ampersand (&). The following example shows how you can limit the count to records in which either Shipped Date or Freight isn't Null:

SELECT
Count('ShippedDate & Freight')
AS [Not Null] FROM Orders;

You can use Count in a query expression. You can also use this expression in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query.


First, Last Functions (SQL)

Return a field value from the first or last record in the result set returned by a query.

Syntax

First(expr)
Last(expr)

The expr placeholder represents a string expression identifying the field that contains the data you want to use or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate or domain aggregate functions).

Remarks

The First and Last functions can be thought of as analogous to the MoveFirst and MoveLast methods of a Recordset object. They simply return the value of a specified field in the first or last record, respectively, of the result set returned by a query. Since records are normally returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary.


Min, Max Functions (SQL)

Return the minimum or maximum of a set of values contained in a specified field on a query.

Syntax

Min(expr)

Max(expr)

The Min and Max functions have this part:

The expr placeholder represents a string expression identifying the field that contains the data you want to evaluate or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate or domain aggregate functions).

Remarks

You can use Min and Max to determine the smallest and largest values in a field based on the specified aggregation, or grouping. For example, you could use these functions to return the lowest and highest freight cost. If there is no aggregation specified, then the entire table is used.

You can use Min and Max in a query expression and in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query.


StDev, StDevP Functions (SQL)

Return estimates of the standard deviation for a population or a population sample represented as a set of values contained in a specified field on a query.

Syntax

StDev(expr)

StDevP(expr)

The expr placeholder represents a string expression identifying the field that contains the numeric data you want to evaluate or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate or domain aggregate functions).

Remarks

The StDevP function evaluates a population, and the StDev function evaluates a population sample.

If the underlying query contains fewer than two records (or no records, for the StDevP function), these functions return a Null (which indicates that a standard deviation can't be calculated).

You can use the StDev and StDevP functions in a query expression. You can also use this expression in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query.


Sum Function (SQL)

Returns the sum of a set of values contained in a specified field on a query.

Syntax

Sum(expr)

The expr placeholder represents a string expression identifying the field that contains the numeric data you want to add or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate or domain aggregate functions).

Remarks

The Sum function totals the values in a field. For example, you could use the Sum function to determine the total cost of freight charges.

The Sum function ignores records that contain Null fields. The following example shows how you can calculate the sum of the products of Unit Price and Quantity fields:

SELECT
Sum(UnitPrice * Quantity)
AS [Total Revenue] FROM [Order Details];

You can use the Sum function in a query expression. You can also use this expression in the SQL property of a QueryDef object or when creating a Recordset based on an SQL query.


Var, VarP Functions (SQL)

Return estimates of the variance for a population or a population sample represented as a set of values contained in a specified field on a query.

Syntax

Var(expr)
VarP(expr)

The VarP function evaluates a population, and the Var function evaluates a population sample.

The expr placeholder represents a string expression identifying the field that contains the numeric data you want to evaluate or an expression that performs a calculation using the data in that field. Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate or domain aggregate functions).

Remarks

If the underlying query contains fewer than two records, the Var and VarP functions return a Null (which indicates that a variance can't be calculated).

You can use the Var and VarP functions in a query expression or in an SQL statement.


Calculating Fields in SQL Functions

You can use the string expression argument in an SQL aggregate function to perform a calculation on values in a field. For example, you could calculate a percentage (such as a surcharge or sales tax) by multiplying a field value by a fraction.

The following table provides examples of calculations on fields from the Orders and Order Details tables in the Northwind.mdb database.

Calculation	Example
Add a number to a field	Freight + 5
Subtract a number from a field	Freight - 5
Multiply a field by a number	UnitPrice * 2
Divide a field by a number	Freight / 2
Add one field to another	UnitsInStock + UnitsOnOrder
Subtract one field from another	ReorderLevel - UnitsInStock
The following example calculates the average discount amount of all orders in the Northwind.mdb database. It multiplies the values in the Unit Price and Discount fields to determine the discount amount of each order and then calculates the average. You can use this expression in an SQL statement in Visual Basic code:

SELECT Avg(UnitPrice * Discount) AS [Average Discount] FROM [Order Details];


Between...And Operator (SQL)

Determines whether the value of an expression lies within a specified range of values.  This operator can be used within SQL statements.

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 ZIP codes.  If the ZIP Code is between 98101 and 98199, the IIf function returns "Local".  Otherwise, it returns "Nonlocal".

SELECT IIf(ZIP Between 98101 And 98199, "Local", "Nonlocal")
FROM Publishers

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

Because wildcards, 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 [image] in this case, 98000 to 98999, or 98000 to 98999 [image] 9999 if using extended ZIP codes. (You must omit the [image] 0000 off the low values because otherwise 98000 is dropped if some ZIP codes have extended sections and others do not.)


Comparison of Microsoft Jet Database Engine SQL and ANSI SQL

Microsoft Jet database engine SQL is generally ANSI-89 Level 1 compliant.  However, certain ANSI SQL features aren't implemented in Jet database engine SQL.  Conversely, Jet database engine SQL includes reserved words and features not supported in ANSI SQL.

Major Differences

Jet database engine SQL and ANSI SQL each have different reserved words and data types.  For more information, see Microsoft Jet Database Engine SQL Reserved Words and Equivalent ANSI SQL Data Types.

Different rules apply to the Between...And construct, which has the following syntax:

expression1 [NOT] BETWEEN expression2 AND expression3

In Jet database engine SQL, expression2 can be greater than expression3; in ANSI SQL, expression2 must be equal to or less than expression3.

Different wildcard characters are used with the Like predicate.

Matching character	Jet database engine SQL	ANSI SQL
Any single character	?	_ (underscore)
Zero or more characters	*	%
Jet database engine SQL is generally less restrictive.  For example, it permits grouping and ordering on expressions.

Jet database engine SQL supports more powerful expressions.

Enhanced Features of Jet Database Engine SQL

The TRANSFORM statement, which provides support for crosstab queries

Additional aggregate functions, such as StDev and VarP

The PARAMETERS declaration for defining parameter queries

ANSI SQL Features Not Supported in Jet Database Engine SQL

Security statements, such as COMMIT, GRANT, and LOCK.

DISTINCT aggregate function references.  For example, Jet database engine SQL doesn't allow SUM(DISTINCT columnname).

The LIMIT TO nn ROWS clause used to limit the number of rows returned by a query. Only the WHERE clause can be used to limit the scope of a query.


Equivalent ANSI SQL Data Types

The following table lists ANSI SQL data types and the equivalent Microsoft Jet database engine SQL data types and their valid synonyms.

ANSI SQL  data type	Jet database engine  SQL data type	Synonym
BIT, BIT VARYING	BINARY (See Notes)	VARBINARY
Not supported	BIT (See Notes)	BOOLEAN, LOGICAL, LOGICAL1, YESNO
Not supported	BYTE	INTEGER1
Not supported	COUNTER	AUTOINCREMENT
Not supported	CURRENCY	MONEY
DATE, TIME, TIMESTAMP	DATETIME	DATE, TIME, TIMESTAMP
Not supported	GUID
DECIMAL	Not supported
REAL	SINGLE	FLOAT4, IEEESINGLE, REAL
DOUBLE PRECISION, FLOAT	DOUBLE	FLOAT, FLOAT8, IEEEDOUBLE, NUMBER, NUMERIC
SMALLINT	SHORT	INTEGER2, SMALLINT
INTEGER	LONG	INT, INTEGER, INTEGER4
INTERVAL	Not supported
Not supported	LONGBINARY	GENERAL, OLEOBJECT
Not supported	LONGTEXT	LONGCHAR, MEMO, NOTE
CHARACTER, CHARACTER VARYING	TEXT	ALPHANUMERIC, CHAR, CHARACTER, STRING, VARCHAR
Not supported	VALUE (See Notes)
Notes

The ANSI SQL BIT data type doesn't correspond to the Jet database engine SQL BIT data type, but it corresponds to the BINARY data type instead.  There is no ANSI SQL equivalent for the Jet database engine SQL BIT data type.

The VALUE reserved word doesn't represent a data type defined by the Jet database engine.


In Operator (SQL)

Determines whether the value of an expression is equal to any of several values in a
specified list.

Syntax

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

Remarks

The In operator uses the following arguments.

Argument	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.

If expr is found in the list of values, the In operator returns True ( [image] 1); otherwise, it returns False (0).  You can include the Not logical operator to evaluate the opposite condition (that is, whether expr is not in the list of values).

You might use In to determine which orders are shipped to a set of specified regions:

SELECT *

FROM Orders

WHERE ShipRegion In ('Avon','Glos','Som')


Like Operator (SQL)

Used to compare a string expression to a pattern in an SQL expression.

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 like those recognized by the operating system 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]###"


Microsoft Jet Database Engine SQL Data Types

The Jet database engine SQL data types consist of 13 primary data types defined by the Microsoft Jet database engine and several valid synonyms recognized for these data types.

The following table lists the primary data types.  The synonyms are identified in Microsoft Jet Database Engine SQL Reserved Words.

Data type	Storage size	Description
BINARY	1 byte per character	Any type of data may be stored in a field of this type. No translation of the data (for example, to text) is made.  How the data is input in a binary field dictates how it will appear as output.
BIT	1 byte	Yes and No values and fields that contain only one of two values.
BYTE	1 byte	An integer value between 0 and 255.
COUNTER	4 bytes	A number automatically incremented by the Jet database engine whenever a new record is added to a table.  In the Jet database engine, the data type for this value is a Long.
CURRENCY	8 bytes	A scaled integer between [image] 922,337,203,685,477.5808 and 922,337,203,685,477.5807.
DATETIME (See DOUBLE)	8 bytes	A date or time value between the years 100 and 9999.
GUID	128 bits	A unique identification number used with remote procedure calls.
SINGLE	4 bytes	A single-precision floating-point value with a range of [image] 3.402823E38 to [image] 1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values, and 0.
DOUBLE	8 bytes	A double-precision floating-point value with a range of [image] 1.79769313486232E308 to [image] 4.94065645841247E-324 for negative values, 4.94065645841247E-324 to 1.79769313486232E308 for positive values, and 0.
SHORT	2 bytes	A short integer between [image] 32,768 and 32,767.
LONG	4 bytes	A long integer between [image] 2,147,483,648 and 2,147,483,647.
LONGTEXT	1 byte per character	Zero to a maximum of 1.2 gigabytes.
LONGBINARY	As required	Zero to a maximum of 1.2 gigabytes.  Used for OLE objects.
TEXT	1 byte per character	Zero to 255 characters.
Note   You can also use the VALUE reserved word in SQL statements.


Microsoft Jet Database Engine SQL Reserved Words

The following list includes all words reserved by the Microsoft Jet database engine for use in SQL statements.  The words in the list that aren't in all uppercase letters are also reserved by other applications.  Consequently, the individual Help topics for these words provide general descriptions that don't focus on SQL usage.

Note   Words followed by an asterisk (*) are reserved by the Jet database engine but currently have no meaning in the context of a Jet database engine SQL statement (for example, Level and TableID).

A

ADD ALL  Alphanumeric [image] See TEXT ALTER  AND	ANY  AS  ASC AUTOINCREMENT [image] See COUNTER Avg
B-C

BETWEEN BINARY  BIT  BOOLEAN [image] See BIT BY BYTE  CHAR, CHARACTER [image] See TEXT	COLUMN CONSTRAINT  Count COUNTER CREATE  CURRENCY
D

DATABASE DATE [image] See DATETIME DATETIME  DELETE DESC	DISALLOW DISTINCT  DISTINCTROW  DOUBLE   DROP
E-H

Eqv EXISTS FLOAT, FLOAT8 [image] See DOUBLE FLOAT4 [image] See SINGLE FOREIGN	FROM  GENERAL [image] See LONGBINARY GROUP GUID HAVING
I

IEEEDOUBLE [image] See DOUBLE IEEESINGLE [image] See SINGLE IGNORE Imp In IN INDEX	INNER  INSERT INT, INTEGER, INTEGER4 [image] See LONG INTEGER1 [image] See BYTE INTEGER2 [image] See SHORT INTO Is
J-M

JOIN KEY LEFT Level* Like LOGICAL, LOGICAL1 [image] See BIT LONG	LONGBINARY  LONGTEXT  Max  MEMO [image] See LONGTEXT Min  Mod MONEY [image] See CURRENCY
N-P

Not NULL  NUMBER [image] See DOUBLE NUMERIC [image] See DOUBLE OLEOBJECT [image] See LONGBINARY ON OPTION Or	ORDER  Outer* OWNERACCESS  PARAMETERS  PERCENT  PIVOT  PRIMARY  PROCEDURE
Q-S

REAL [image] See SINGLE  REFERENCES  RIGHT  SELECT  SET  SHORT  SINGLE	SMALLINT [image] See SHORT SOME  StDev StDevP  STRING [image] See TEXT Sum
T-Z

TABLE TableID* TEXT  TIME [image] See DATETIME TIMESTAMP [image] See DATETIME TOP TRANSFORM UNION UNIQUE UPDATE	VALUE  VALUES  Var  VARBINARY [image] See BINARY VARCHAR [image] See TEXT VarP  WHERE  WITH  Xor YESNO [image] See BIT


SQL Expressions

An SQL expression is a string that makes up all or part of an SQL statement.  For example, the FindFirst method on a Recordset object uses an SQL expression, consisting of the selection criteria found in an SQL WHERE clause.

The Microsoft Jet Database Engine uses the Visual Basic for Applications (or VBA) expression service to perform simple arithmetic and function evaluation.  All of the expressions operators used in Jet database engine SQL expressions (except Between, In, and Like) are defined by the VBA expression service.  In addition, the VBA expression service offers over 100 VBA functions that can be used in SQL expressions.  For example, an SQL query composed in the SQL View of the Microsoft Access Query Design window can use these VBA functions, and so can an SQL query used in the DAO OpenRecordset method in Microsoft Visual C++, Microsoft Visual Basic, and Microsoft Excel code.


Using Wildcard Characters in String Comparisons

Built-in pattern matching provides a versatile tool for making string comparisons.  The following table shows the wildcard characters you can use with the Like operator and the number of digits or strings they match.

Character(s)  in pattern	Matches in expression
?	Any single character
*	Zero or more characters
#	Any single digit (0 [image] 9)
[charlist]	Any single character in charlist
[!charlist]	Any single character not in charlist
A group of one or more characters (charlist) enclosed in brackets ([ ]) can be used to match any single character in expression and can include almost any characters in the ANSI character set, including digits.  In fact, the special characters opening bracket ([ ), question mark (?), number sign (#), and asterisk (*) can be used to match themselves directly only if enclosed in brackets.  The closing bracket ( ]) can't be used within a group to match itself, but it can be used outside a group as an individual character.

In addition to a simple list of characters enclosed in brackets, charlist can specify a range of characters by using a hyphen (-) to separate the upper and lower bounds of the range.  For example, using [A-Z] in pattern results in a match if the corresponding character position in expression contains any of the uppercase letters in the range A through Z.  Multiple ranges can be included within the brackets without any delimiting.  For example, [a-zA-Z0-9] matches any alphanumeric character.

Other important rules for pattern matching include the following:

An exclamation mark (!) at the beginning of charlist means that a match is made if any character except those in charlist are found in expression.  When used outside brackets, the exclamation mark matches itself.

The hyphen (-) can be used either at the beginning (after an exclamation mark if one is used) or at the end of charlist to match itself.  In any other location, the hyphen is used to identify a range of ANSI characters.

When a range of characters is specified, they must appear in ascending sort order (A-Z or 0-100).  [A-Z] is a valid pattern, but [Z-A] isn't.

The character sequence [ ] is ignored; it's considered to be a zero-length string ("").


ALTER TABLE Statement Example

The SQL statements shown below can be used on tables created with the CREATE TABLE statement.

This example adds a “Salary” field with a data type of Currency to the “Employees” table.

ALTER TABLE Employees ADD COLUMN Salary CURRENCY;

This example removes the “Salary” field from the “Employees” table.

ALTER TABLE Employees DROP COLUMN Salary;

This example adds a foreign key to the “Orders” table.  The foreign key is based on the “Employee ID” field and refers to the “Employee ID” field of the “Employees” table.  In this example, you don't have to list the “Employee ID” field after the “Employees” table in the REFERENCES clause because “Employee ID” is the primary key of the “Employees” table.

ALTER TABLE Orders ADD CONSTRAINT OrdersRelationship FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID);

This example removes the foreign key from the “Orders” table.

ALTER TABLE Orders DROP CONSTRAINT OrdersRelationship;


CREATE INDEX Statement Example

This example creates an index consisting of the fields “Home Phone” and “Extension” in the “Employees” table.

CREATE INDEX NewIndex ON Employees (HomePhone, Extension);

This example creates an index on the “Employees” table using the “Social Security Number” field.  No two records can have the same data in the SSN field, and no Null values are allowed.

CREATE UNIQUE INDEX CustID ON Customers (CustomerID) WITH DISALLOW NULL;

This example creates an index on a hypothetical ODBC linked table.  The table's remote database is unaware of and unaffected by the new index.

CREATE UNIQUE INDEX OrderID ON OrderDetails (OrderID);


CREATE TABLE Statement, CONSTRAINT Clause Example

This example creates a new table called “ThisTable” with two Text fields.

CREATE TABLE ThisTable (FirstName TEXT, LastName TEXT);

This example creates a new table called “MyTable” with two Text fields, a Date/Time field, and a unique index made up of all three fields.

CREATE TABLE MyTable (FirstName TEXT, LastName TEXT, DateOfBirth DATETIME, CONSTRAINT MyTableConstraint UNIQUE (FirstName, LastName, DateOfBirth));

This example creates a new table with two Text fields and an Integer field.  The “SSN” field is the primary key.

CREATE TABLE NewTable (FirstName TEXT, LastName TEXT, SSN INTEGER CONSTRAINT MyFieldConstraint PRIMARY KEY);


DROP Statement Example

The following examples assume the existence of a hypothetical “MyIndex” index and a hypothetical “Trainees” table.  Note that these objects do not actually exist in the Northwind database.

This example deletes the index “MyIndex” from the “Employees” table.

DROP INDEX MyIndex ON Employees;

This example deletes the “Trainees” table from the database.

DROP TABLE [Trainees];


SELECT Statement, FROM Clause Example

Some of the following examples assume the existence of a hypothetical “Salary” field in an “Employees” table.  Note that this field does not actually exist in the Northwind database “Employees” table.

This example selects the “LastName” and “FirstName” fields of all records in the “Employees” table.

SELECT LastName, FirstName FROM Employees;

This example selects all fields from the “Employees” table.

SELECT Employees.* FROM Employees;

This example counts the number of records that have an entry in the “PostalCode” field and names the returned field "Tally".

SELECT Count(PostalCode) AS Tally FROM Customers;

This example shows what the salary would be if each employee received a 10 percent raise.  It doesn't change the original salary amounts.

SELECT LastName, Salary AS Current, Salary * 1.1 AS Proposed FROM Employees;

This example places the title Name at the top of the “LastName” column.  The title Salary is displayed at the top of the “Salary” column.

SELECT LastName AS Name, Salary FROM Employees;

This example shows the number of employees and the average and maximum salaries.

SELECT Count(*) AS [Total Employees], Avg(Salary) AS [Average Salary], Max(Salary) AS [Maximum Salary] FROM Employees;

For each record, this example shows the LastName and Salary in the first and last fields.  The string "has a salary of" is returned as the middle field of each record.

SELECT LastName, 'has a salary of', Salary FROM Employees;


IN Clause Example

The following table shows how you can use the IN clause to retrieve data from an external database.  In each example, assume the hypothetical “Customers” table is stored in an external database.

External database	SQL statement
Microsoft Jet database. MYDATA.mdb is the name of the Jet database that contains the Customers table.	SELECT CustomerID FROM Customers IN MYDATA.mdb  WHERE CustomerID Like "A*";
dBASE III or IV. To retrieve data from a dBASE III table, substitute "dBASE III" for "dBASE IV;".	SELECT CustomerID FROM Customer IN "C:\DBASE\DATA\SALES" "dBASE IV;" WHERE CustomerID Like "A*";
dBASE III or IV using DATABASE syntax. To retrieve data from a dBASE III table, substitute "dBASE III" for "dBASE IV;".	SELECT CustomerID FROM Customer IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;]  WHERE CustomerID Like "A*";
Paradox 3.x or 4.x. To retrieve data from a Paradox version 3.x table, substitute "Paradox 3.x;" for "Paradox 4.x;".	SELECT CustomerID FROM Customer IN "C:\PARADOX\DATA\SALES" "Paradox 4.x;" WHERE CustomerID Like "A*";
Paradox 3.x or 4.x using DATABASE syntax. To retrieve data from a Paradox version 3.x table, substitute "Paradox 3.x;" for "Paradox 4.x;".	SELECT CustomerID FROM Customer IN "" [Paradox 4.x;DATABASE=C:\PARADOX\DATA\SALES;]  WHERE CustomerID Like "A*";


In Operator Example

The following example uses the “Orders” table in the Northwind.mdb database to create a query that includes orders shipped to Avon, Gloucester, and Somerset.

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


WHERE Clause Example

Some of the following examples assume the existence of a hypothetical “Salary” field in an “Employees” table.  Note that this field does not actually exist in the Northwind database “Employees” table.

This example selects the “LastName” and “FirstName” fields of each record in which the last name is King.

SELECT LastName, FirstName FROM Employees WHERE LastName = 'King';

This example selects the “LastName” and “FirstName” fields for employees whose last names begin with the letter S.

SELECT LastName, FirstName FROM Employees WHERE LastName Like 'S*';

This example selects employees whose salaries are between $20,000 and $30,000, inclusive.

SELECT LastName, Salary FROM Employees WHERE Salary Between 20000 And 30000;

This example selects employees whose last names fall in alphabetic order between Lon and Tol, inclusive.  It doesn't retrieve Tolstoy because Tolstoy follows Tol and therefore is outside the specified range.

SELECT LastName, Salary FROM Employees WHERE LastName Between 'Lon' And 'Tol';

This example selects orders placed during the first half of 1994.

SELECT OrderID, OrderDate FROM Orders WHERE OrderDate Between #1-1-94# And #6-30-94#;

This example selects employees who live in Interlaken, New York, or Frankfurt.

SELECT LastName, FirstName, City FROM Employees WHERE City In ('Interlaken', 'New York', 'Frankfurt');


GROUP BY Clause Example

Some of the following examples assume the existence of a hypothetical “Department” field in an “Employees” table.  Note that this field does not actually exist in the Northwind database “Employees” table.

This example creates a list of unique department names and the number of employees in each of those departments.

SELECT Department, Count([Department]) AS Tally FROM Employees GROUP BY Department;

For each unique job title, this example calculates the number of Sales department employees who have that title.

SELECT Title, Count(Title) AS Tally FROM Employees WHERE Department = 'Sales' GROUP BY Title;

This example calculates the number of items in stock for each combination of item number and color.  Note that the “ItemsInStock” table does not actually exist in the Northwind database.

SELECT Item, Sum(Units) AS Tally FROM ItemsInStock GROUP BY Item, Color;


HAVING Clause Example

Some of the following examples assume the existence of a hypothetical “Department” field in an “Employees” table.  Note that this field does not actually exist in the Northwind database “Employees” table.

This example selects the job titles in the Production department assigned to more than 50 employees.

SELECT Title, Count(Title) FROM Employees WHERE Department = 'Production' GROUP BY Title HAVING Count(Title) > 50;

This example selects departments with more than 100 employees.

SELECT Department, Count([Department]) FROM Employees GROUP BY Department HAVING Count(Department) > 100;


ORDER BY Clause Example

The SQL statements shown below use the ORDER BY clause to sort records alphabetically and then by category.

This example sorts the records by last name in descending order (Z-A).

SELECT LastName, FirstName FROM Employees ORDER BY LastName DESC;

This example sorts by category ID first, and then by product name.

SELECT CategoryID, ProductName, UnitPrice FROM Products ORDER BY CategoryID, ProductName;


ALL, DISTINCT, DISTINCTROW, TOP Predicates Example

The following examples use two hypothetical tables, “Customers” and “Orders.”  Note that these are purely hypothetical; the “Customers” and “Orders” tables in the Northwind database do not contain the columns and data shown in these examples.

Customers Table

First name	Customer ID

Bob	1

Adam	2

Beverly	3

Bob	4

Orders Table

Customer ID	Order ID

1	1

1	2

2	3

2	4

2	5

4	6

4	7

SELECT ALL FirstName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result	Updatable
Bob Bob Adam Adam Adam Bob Bob	Yes
SELECT DISTINCT FirstName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result	Updatable
Bob Adam	No
SELECT DISTINCTROW FirstName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result	Updatable
Bob Adam Bob	Yes
SELECT TOP 5 FirstName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY Orders.OrderID;

Result	Updatable
Bob Bob Adam Adam Adam	Yes


DELETE Statement Example

Some of the following examples assume the existence of a hypothetical “Payroll” table.  Note that this table does not actually exist in the Northwind database.

This example deletes all records for employees whose title is Trainee.  When the FROM clause includes only one table, you don't have to list the table name in the DELETE statement.

DELETE * FROM Employees WHERE Title = 'Trainee';

This example deletes all records for employees whose title is Trainee and who also have a record in the “Payroll” table.  The “Employees” and “Payroll” tables have a one-to-one relationship.

DELETE Employees.* FROM Employees INNER JOIN Payroll ON Employees.EmployeeID = Payroll. EmployeeID WHERE Employees.Title = 'Trainee';


INNER JOIN Operation Example

This example creates two equi-joins: one between the “Order Details” and “Orders” tables and another between the “Orders” and “Employees” tables.  This is necessary because the “Employees” table doesn't contain sales data, and the “Order Details” table doesn't contain employee data.  The query produces a list of employees and their total sales.

SELECT DISTINCTROW Sum(UnitPrice * Quantity) AS [Sales], FirstName & " " & LastName AS Name FROM Employees INNER JOIN(Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Employees.EmployeeID = Orders.EmployeeID GROUP BY FirstName & " " & LastName;


INSERT INTO Statement Example

This example selects all records in a hypothetical “New Customers” table and adds them to the “Customers” table. (When individual columns are not designated, the SELECT table column names must match exactly those in the INSERT INTO table.)

INSERT INTO Customers SELECT [New Customers].* FROM [New Customers];

This example creates a new record in the “Employees” table.

INSERT INTO Employees (FirstName,LastName, Title) VALUES ("Harry", "Washington", "Trainee");

This example selects all trainees from a hypothetical “Trainees” table  who were hired more than 30 days ago and adds their records to the “Employees” table.

INSERT INTO Employees SELECT Trainees.* FROM Trainees WHERE HireDate < Now() - 30;


LEFT JOIN, RIGHT JOIN Operations Example

These examples assume the existence of hypothetical “Department Name” and “Department ID” fields in an “Employees” table.  Note that these fields do not actually exist in the Northwind database “Employees” table.

This example selects all departments, including those without employees.

SELECT [Department Name], FirstName & " " & LastName AS Name FROM Departments LEFT JOIN Employees ON Departments.[Department ID] = Employees.[Department ID] ORDER BY [Department Name];

This example selects all employees, including those not assigned to a department.

SELECT LastName & ", " & FirstName AS Name, [Department Name] FROM Departments RIGHT JOIN Employees ON Departments.[Department ID] = Employees.[Department ID] ORDER BY LastName & ", " & FirstName;


PARAMETERS Declaration Example

This example requires you to provide an employee's last name and then uses that entry as the criteria for the query.

PARAMETERS [Enter a Last Name:] Text; SELECT * FROM Employees WHERE LastName =  [Enter a Last Name:];

This example requires you to provide a category ID and then uses that entry as the criteria for the query.

PARAMETERS [Enter a Category ID:] Value; SELECT DISTINCTROW CategoryID, ProductName, Count([Order Details].OrderID) AS Tally FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID GROUP BY CategoryID, ProductName HAVING CategoryID = [Enter a Category ID:];


PROCEDURE Clause Example

This example names the query “Category_List.”

PROCEDURE Category_List; SELECT DISTINCTROW CategoryName, CategoryID FROM Categories ORDER BY CategoryName;

This example names the query “Summary” and includes two parameters.  The PROCEDURE clause is changed to the following PARAMETERS declaration:

PARAMETERS [Beginning Date] DateTime,
[Ending Date] DateTime;

PROCEDURE Summary [Beginning Date] DATETIME, [Ending Date] DATETIME; SELECT DISTINCTROW ShippedDate, OrderID, EmployeeID, Format(ShippedDate, "yyyy") AS Year FROM Orders WHERE ShippedDate Between [Beginning Date] And [Ending Date];


SELECT...INTO Statement Example

Some of the following examples assume the existence of a hypothetical “Salary” field in a “Payroll” table.  Note that this table does not actually exist in the Northwind database.

This example selects all records in the “Employees” table and copies them into a new table named “Emp Backup.”

SELECT Employees.* INTO [Emp Backup] FROM Employees;

This example creates a new table called “Trainees” that contains only employee records that have the title Trainee.

SELECT Employees.FirstName, LastName INTO Trainees FROM Employees WHERE Title = 'Trainee';

This example makes a copy of the “Employees” table and places the new table in the hypothetical database BACKUP.MDB.

SELECT Employees.* INTO Employees IN "BACKUP.MDB" FROM Employees;

This example creates a new table that contains employee and payroll data for all trainees.  The “Employees” and “Payroll” tables have a one-to-one relationship. The new table contains all of the data from the “Employees” table plus the “Salary” field from the “Payroll” table.

SELECT Employees.*, Salary INTO Trainees FROM Employees INNER JOIN Payroll ON Employees.EmployeeID = Payroll.EmployeeID WHERE Title = 'Trainee';


TRANSFORM Statement Example

This example creates a crosstab query that shows product sales by month for a user-specified year.  The months are returned from left to right (pivoted) as columns, and the product names are returned from top to bottom as rows.

PARAMETERS [Sales for which year?] LONG; TRANSFORM Sum([Order Details].Quantity * ([Order Details].UnitPrice - ([Order Details].Discount / 100) * [Order Details].UnitPrice)) AS Sales SELECT ProductName FROM Orders INNER JOIN
(Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID WHERE DatePart("yyyy", OrderDate) = [Sales for which year?] GROUP BY ProductName ORDER BY ProductName PIVOT DatePart("m", OrderDate);

This example creates a crosstab query that returns product sales by quarter for each supplier for a user-specified year.  The quarters are returned from left to right (pivoted) as columns, and the supplier names are returned from top to bottom as rows.

PARAMETERS [Sales for which year?] LONG; TRANSFORM Sum([Order Details].Quantity * ([Order Details].UnitPrice - ([Order Details].Discount / 100) * [Order Details].UnitPrice)) AS Sales SELECT CompanyName FROM Orders INNER JOIN ((Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID)
INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID WHERE DatePart("yyyy", OrderDate) = [Sales for which year?] GROUP BY CompanyName ORDER BY CompanyName PIVOT "Qtr " & DatePart("q", OrderDate) In ('Qtr 1', 'Qtr 2', 'Qtr 3', 'Qtr 4');


UPDATE Statement Example

This example changes values in the “ReportsTo” field to 5 for all employee records that currently have ReportsTo values of 2.

UPDATE Employees SET ReportsTo = 5 WHERE ReportsTo = 2;

This example increases the “UnitPrice” for all nondiscontinued products from supplier 8 by 10 percent.

UPDATE Products SET UnitPrice = UnitPrice * 1.1 WHERE SupplierID = 8 AND Discontinued = No;

This example reduces the UnitPrice for all nondiscontinued products supplied by Tokyo Traders by 5 percent.  The “Products” and “Suppliers” tables have a many-to-one relationship.

UPDATE Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID SET UnitPrice = UnitPrice * .95 WHERE CompanyName = 'Tokyo Traders' AND Discontinued = No;


UNION Operation Example

This example retrieves the names and cities of all suppliers and customers in Brazil.

SELECT CompanyName, City FROM Suppliers WHERE Country = "Brazil" UNION SELECT CompanyName, City FROM Customers WHERE Country = "Brazil";

This example retrieves the names and cities of all suppliers and customers located in Brazil, using constants, an alias, and ordering by an alias.

SELECT CompanyName, City, "Supplier" AS Source FROM Suppliers WHERE Country = "Brazil" UNION SELECT CompanyName, City, "Customer" FROM Customers WHERE Country = "Brazil" ORDER BY City, Source;

This example retrieves the names and cities of all suppliers and customers in Brazil and the last names and cities of all employees in South America.

SELECT CompanyName, City FROM Suppliers WHERE Country = "Brazil" UNION SELECT CompanyName, City FROM Customers WHERE Country = "Brazil" UNION SELECT LastName, City FROM Employees WHERE Region = "South America";

This example retrieves the names and IDs of all suppliers and customers. This union assumes that there are the same number of columns in each table.

TABLE Customers UNION TABLE Suppliers;


SQL Subqueries Example

Some of the following examples assume the existence of a hypothetical “Salary” field in an “Employees” table.  Note that this field does not actually exist in the Northwind database “Employees” table.

This example lists the name, title, and salary of every sales representative whose salary is higher than that of all managers and directors.

SELECT LastName, FirstName, Title, Salary FROM Employees WHERE Title LIKE "*Sales Rep*" AND Salary > ALL (SELECT Salary FROM Employees WHERE (Title LIKE "*Manager*") OR (Title LIKE "*Director*"));

This example lists the name and unit price of every product whose unit price is the same as that of Aniseed Syrup.

SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice = (SELECT UnitPrice FROM [Products] WHERE ProductName = "Aniseed Syrup");

This example lists the company and contact of every customer who placed an order in the second quarter of 1995.

SELECT ContactName, CompanyName, ContactTitle, Phone FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate BETWEEN #04/1/95# AND #07/1/95#);

This example lists employees whose salary is higher than the average salary for all employees.

SELECT LastName, FirstName, Title, Salary FROM Employees T1 WHERE Salary >= (SELECT AVG(Salary) FROM Employees WHERE Employees.Title = T1.Title) ORDER BY Title;

This example selects the name of every employee who has booked at least one order.  This could also be done with an INNER JOIN.

SELECT FirstName, LastName FROM Employees AS E WHERE EXISTS (SELECT * FROM Orders AS O WHERE O.EmployeeID = E.EmployeeID);


Avg Function Example

This example uses the “Orders” table to calculate the average freight charges for orders with freight charges over $100.

SELECT Avg(Freight) AS [Average Freight] FROM Orders WHERE Freight > 100;


Count Function Example

This example uses the “Orders” table to calculate the number of orders shipped to the United Kingdom.

SELECT Count(ShipCountry)AS [UK Orders] FROM Orders WHERE ShipCountry = 'UK';


First, Last Functions Example

This example uses the “Employees” table to return the values from the “LastName” field of the first and last records returned from the table.

SELECT First(LastName), Last(LastName) FROM Employees;

The next two examples compare using the First and Last functions with simply using the Min and Max functions to find the earliest and latest birth dates of Employees.

SELECT First(BirthDate), Last(BirthDate) FROM Employees;

SELECT Min(BirthDate), Max(BirthDate) FROM Employees;


Min, Max Functions Example

This example uses the “Orders” table to return the lowest and highest freight charges for orders shipped to the United Kingdom.

SELECT Min(Freight) AS [Low Freight], Max(Freight) AS [High Freight] FROM Orders WHERE ShipCountry = 'UK';


StDev, StDevP Functions Example

This example uses the “Orders” table to estimate the standard deviation of the freight charges for orders shipped to the United Kingdom.

SELECT StDev(Freight) AS [Freight Dev] FROM Orders WHERE ShipCountry = 'UK';

SELECT StDevP(Freight) AS [Freight DevP] FROM Orders WHERE ShipCountry = 'UK';


Sum Function Example

This example uses the “Orders” table to calculate the total sales for orders shipped to the United Kingdom.

SELECT Sum(UnitPrice*Quantity) AS [Total UK Sales] FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID WHERE (ShipCountry = 'UK');


Var, VarP Functions Example

This example uses the “Orders” table to estimate the variance of freight costs for orders shipped to the United Kingdom.

SELECT Var(Freight) AS [UK Freight Variance] FROM Orders WHERE ShipCountry = 'UK';

SELECT VarP(Freight) AS [UK Freight VarianceP] FROM Orders WHERE ShipCountry = 'UK';


Like Operator Example

This example returns a list of employees whose names begin with the letters A through D.

SELECT * FROM Employees WHERE LastName Like "[A-D]*"

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"
	"*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"


IN <keyword>
IN

The IN keyword is used in these contexts:

In Operator (SQL)

IN Clause (SQL)

SQL Subqueries (SQL)

TRANSFORM Statement (SQL)


ON <keyword>
ON

The ON keyword is used in these contexts:

INNER JOIN Operation (SQL)

LEFT JOIN, RIGHT JOIN Operations (SQL)


ALL <keyword>
ALL

The ALL keyword is used in these contexts:

ALL, DISTINCT, DISTINCTROW, TOP Predicates (SQL)

SQL Subqueries

UNION Operation (SQL)


ASC/DESC <keywords>
ASC/DESC

The ASC and DESC keywords are used in these contexts:

CREATE INDEX Statement (SQL)

ORDER BY Clause (SQL)


BY <keyword>
BY

The BY keyword is used in these contexts:

GROUP BY Clause (SQL)

ORDER BY Clause (SQL)


CREATE <keyword>
CREATE

The CREATE keyword is used in these contexts:

CREATE INDEX Statement (SQL)

CREATE TABLE Statement (SQL)


DROP <keyword>
DROP

The DROP keyword is used in these contexts:

ALTER TABLE Statement (SQL)

DROP Statement (SQL)


INDEX <keyword>
INDEX

The INDEX keyword is used in these contexts:

CREATE INDEX Statement (SQL)

DROP Statement (SQL)


INTO <keyword>
INTO

The INTO keyword is used in these contexts:

INSERT INTO Statement (SQL)

SELECT ...  INTO Statement (SQL)


JOIN <keyword>
JOIN

The JOIN keyword is used in these contexts:

INNER JOIN Operation (SQL)

LEFT JOIN, RIGHT JOIN Operations (SQL)


SELECT <keyword>
SELECT

The SELECT keyword is used in these contexts:

SELECT Statement (SQL)

SELECT ...  INTO Statement (SQL)


TABLE <keyword>
TABLE

The TABLE keyword is used in these contexts:

ALTER TABLE Statement (SQL)

CREATE INDEX Statement (SQL)

DROP Statement (SQL)

UNION Operation (SQL)


WITH <keyword>
WITH

The WITH keyword is used in these contexts:

CREATE INDEX Statement (SQL)

WITH OWNERACCESS OPTION Declaration (SQL)