3 4
Adding new data is another job that’s typically considered a data entry task, unless you’re dealing with large amounts of data. You can use an append query to import data from a foreign source and then append it to an existing table, or you can fill a database with data from another Access database or even a table in the same database. Like select and update queries, append queries can be limited by criteria.
Let’s take a look at a simple append query example. To append records from the Employees table to an empty but structurally similar table, follow these steps:
note
Figure 10-7. Identify the table to which you’re appending records.
Figure 10-8. Click Yes to append the records.
tip - Check your field names in append queries
Troubleshooting - I’m getting a Type Conversion error when I try to run an append query
Check that each field in the target table is the same data type as the field in the source table being appended to it. You can’t append data from a text field to a numeric field, for example. In some cases, you might need to use a conversion function such as CInt or CStr to convert field data to another data type in a query column expression to perform a successful append. For more information about conversion functions, type Conversion Functions in the Ask A Question box in the Visual Basic Editor window, and select the Conversion Functions Help topic.
Let’s take a look at the query’s SQL statement, shown here:
INSERT INTO EmployeesAppend SELECT Employees.* FROM Employees;
tip
The INSERT INTO statement identifies the statement as an append query. Here’s the statement in its simplest form:
INSERT INTO target SELECT source ;
The INSERT INTO statement copies records from one table to another, where target refers to a table—directly or by using the IN predicate. The SELECT statement’s source is any valid SELECT statement and can include a GROUP BY clause, a join, or a subquery. If you’re not appending every field, identify the fields in the SELECT statement instead of using the table. * syntax.
You can append a single row of data, although this is unusual. In the right circumstances, however, this technique can be extremely useful. For instance, you could use an append query to save control values from an unbound data entry form to a table, letting users add a new record to a table on the fly. The query shown in Figure 10-9 will add a single row to the EmployeesAppend table. The target table’s field list isn’t visible in the upper pane; simply choose Query, Append Query to view the target table.
Figure 10-9. An append query can add a single row of specific values to a table.
If you run the query, Access will add a new record and then insert the values Smith and John in the LastName and FirstName fields, as shown in Figure 10-10.
Figure 10-10. The previous query added one row and two field values to the EmployeesAppend table.
The query’s intent is a little clearer in its SQL statement, shown here:
INSERT INTO EmployeesAppend ( LastName, FirstName ) SELECT [Forms]![frmNewName]![txtFirstName] AS FirstName, [Forms]![frmNewName]![txtLastName] AS LastName;
The INSERT INTO statement identifies the target table and affected fields, while the SELECT statement shows the syntax for picking up data from a control on a form.