Warning: All the queries discussed below change the state of the database: most of the time, you will not be able to undo them. Therefore, before running these queries, first change the query type to SELECT, run it and look at the output. This will help you make sure that the query will give you the results you desire. Then change the query back to the intended query type and run it. We illustrate the process in each case below.
These queries add one or more records to a database. Unless you are a programmer, you will never write a query to add a single record to a table- you will simply open the table and add a new record by typing it in.
We describe the syntax (an INSERT statement) partly for the sake of completeness, and partly because it helps you understand the next subsection. If you are using a high-end database that lacks a civilized GUI, you may sometimes need to resort to typing an INSERT statement.
The syntax goes:
INSERT INTO <tablename> (column1, column2, column3...) VALUES (value1, value2,
value3...)
The columns and values must correspond exactly, and the data type of each
column must match- thus, text must be enclosed in quotes.
Example:
Insert into Patients (Patient_ID, FirstName, LastName) values (12345, 'John',
'Smith')
Sometimes, however, you want to add records in bulk to a table, where the source of the records is an SQL query on multiple other tables. From the SQL perspective, such a query is an INSERT statement coupled with a SELECT statement.
The syntax goes
INSERT INTO <tablename> (column1, column2, column3...) <select-statement>
The SELECT statement should return as many columns as there are columns in
the INSERT part of the statement.
Testing the Append query: Run the SELECT statement by itself. What you
see on the screen is what will be added to the table in question.
Example:
A Make-table query is identical to a SELECT statement except that its output,
instead of being directed to the screen for you to look at, goes to create a new
table whose name you specify. (If a table with this name already exists, you are
warned before over-writing it.)
If you are using expressions for particular columns, it is highly desirable to
designate such columns with an alias: otherwise, Access devises column names
that are not always obvious or even meaningful (like "Expr1").
An UPDATE query changes the contents of one or more records in a table. As with the INSERT statement, it is not worth your while, if you aren't a programmer, to write a query that changes a single record- you just open the table, go to the record in question, and edit it. UPDATE statements are more useful for changing the contents of several records (often hundreds or even thousands) in a single operation.
The UPDATE statement is particularly powerful when you change the contents of a table based on criteria specified against a table one or two relationship links away.
The syntax depends on the operation that you want to perform.
Updating multiple records from a single table, optionally based on criteria
on the same table:
UPDATE <tablename> SET column1 = value1, column2 = value2, .... [WHERE
<condition>]
e.g.,
UPDATE Patients where State='MS' and DateOfBirth < #1/01/1921#
Updating a table based on a criteria from several tables:
Testing the Update query: Change temporarily to a SELECT statement, and run. If your query involves multiple tables with a WHERE clause, you will see the records that are going to be changed.
An DELETE query deletes one or more records in a table. As with the INSERT/UPDATE statement, it is simpler, if deleting a single record, to just do it manually. DELETE statements are more useful for deleting numerous records in a single step. Even if you want to delete all the records in a table, doing this with a query is much faster than trying to select all the records in a table and then hitting the cut or delete key. (With the latter approach, Access will try to copy the records into an "Undo" buffer, so that you can undo your changes. With a query, you are only warned before you execute it.)
The syntax depends on the operation that you want to perform.
Deleting all records from a single table:
DELETE FROM <tablename>
Deleting records from a single table based on a criterion on the same table:
DELETE FROM <tablename> WHERE <condition>
e.g.,
DELETE FROM Patients where State='MS' and DateOfBirth < #1/01/1921#
Deleting from a table based on a criteria from several tables:
Testing the Delete query: Change temporarily to a SELECT statement, and run. If your query involves multiple tables with a WHERE clause, you will see the records that are going to be deleted.
Access is not particularly smart when trying to operate on ODBC-linked tables; an UPDATE or a DELETE query based on multiple-table criteria often fails whereas the same query would succeed if operating on Access tables.
This bug seems to be due to errors in the vendor-specific SQL that is generated: unfortunately, the multiple-table-based update or delete query syntax varies greatly depending on which vendor engine you are using. (This was one of the last parts of SQL to be standardized, and even