Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

674

Creating queries to append records

As the word append suggests, an append query attaches or adds records to an existing table. An

append query adds the records selected by the query to another table. You can append records to a

table in the same database or in another Access database.

Append queries are useful for adding information to another table on the basis of some scoping

criteria. Even so, append queries are not always the fastest way of adding records to another data-

base. For example, if you need to append all fields and all records from one table to a new table,

the append query is not the best way to do it. Instead, use the Copy and Paste options on the Edit

menu when you’re working with the table in a datasheet or form.

Append query creation follows the pattern already discussed for update and make-table queries.

You start by building a select query that returns the desired records and clicking the Append but-

ton in the ribbon’s Query Type group. Access asks you which table receives the records.

A good example of an append query is periodically adding inactive customers to tblInactive-

Customers. In fact, the query you’d use is exactly the same as the previous example, except that

you execute the query as an append, rather than as a make-table query.

When you’re working with append queries, be aware of these rules:

l If the table you’re appending records to has a primary key field, the records you add can’t

have Null values or duplicate primary key values. If they do, Access won’t append the

records and you’ll get no warning. In many cases, the destination table doesn’t need a des-

ignated primary key. Or, you may choose to use an AutoNumber primary key and let

Access assign the key value to each record added to the destination table.

l (^) If you use the asterisk (*) field in a field’s row in Design view, you can’t also use individ-
ual fields from the same table. Access assumes that you’re trying to add field contents
twice to the same record and won’t append the records.
Note
When you’re using the append query, only fields with names that match in the two tables are copied. For exam-
ple, you might have a small table with six fields and another with nine fields. The table with nine fields has only
five of the six field names that match fields in the smaller table. If you append records from the smaller table to
the larger table, only the five matching fields are appended, leaving the other four fields empty.
Creating a query to delete records
Of all the action queries, the delete query is the most dangerous. Unlike the other types of queries
you’ve worked with, delete queries irreversibly remove records from tables.
A delete action query can work with multiple tables to delete records. If you intend to delete
related records from multiple tables, however, you should do the following:

1. Define relationships between the tables in the Relationships Builder.


2. Check the Enforce Referential Integrity option for the join between tables.


3. Check the Cascade Delete Related Records option for the join between tables (for one-to-
   one or one-to-many relationships).