Adding Data with an Append Query

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:

  1. Using the method described in the section "Protecting Your Data," create a copy of the Employees table structure. (Don’t copy the data.) Name the copy EmployeesAppend.

    note


    In this exercise, you should copy only the Employees table structure, not the data. That’s because this table includes a primary key value. Since a primary key field can’t contain duplicates, an append query can fail if records with duplicate primary key values are in the target table.

  2. Select the Employees table in the Database window, choose Query from the New Object button’s drop-down list, and then click OK in the New Query dialog box to open the new query in Design view.
  3. Drag the asterisk (*) from the field list to the first Field cell in the design grid.
  4. Choose Query, Append Query.
  5. In the Append dialog box, choose EmployeesAppend from the Table Name drop-down list, as shown in Figure 10-7, and click OK.

    figure 10-7. identify the table to which you’re appending records.

    Figure 10-7. Identify the table to which you’re appending records.

  6. Examine the results before actually running the query. Choose Datasheet View from the View button’s drop-down list to preview the results.
  7. Return to Design view, and click the Run button on the Query Design toolbar to execute the query. Click Yes in response to the confirmation message shown in Figure 10-8, which indicates the number of records the query will append.

    figure 10-8. click yes to append the records.

    Figure 10-8. Click Yes to append the records.

  8. Open the EmployeesAppend table to verify that the query appended the records.

tip - Check your field names in append queries


Append queries can return a few errors; the cause is usually mismatched fields. An error can occur when the field names in the target table have been changed or when some fields have been deleted. The field names don’t have to match in the source table and target table, but the correct field names in the target table must be selected in the Append To cell of each query column. For example, you might want to append from a field named ContactName in one table to a field named Contact in another table. In this case, the query column is named ContactName, and Contact is selected in the Append To cell for this column. Access automatically fills in the Append To cell for fields that have matching names in the source and target tables. For those that don’t, you have to select the Append To field manually.

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


When appending data to a table with an AutoNumber key field, omit the key field from the design grid. Access will create a new value for this field automatically when the new records are appended to the target table.

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.

Appending a Single Row

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.

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.

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.



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net