| | Product Information Help and How-to Training Templates Support and Feedback Technical Resources Additional Resources | Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.
Create an append query (MDB)
- Create a query that contains the table whose records you want to append to another table.
 How?
- In the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), click Queries
 under Objects, and then click New on the Database window toolbar.
- In the New Query dialog box, click Design View, and then click OK.
- In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
- Double-click the name of each object you want to add to the query, and then click Close.
- Add fields to the Field row in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), and if you want, specify criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) and a sort order.
- To view the query's results, click View
 on the toolbar.
- In query Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.), click the arrow next to Query Type
 on the toolbar, and then click Append. The Append dialog box appears.
- In the Table Name box, enter the name of the table you want to append records to.
- Do one of the following:
If the table is in the currently open database, click Current Database.
If the table is not in the currently open database, click Another Database and type the path of the database where the table is stored or click Browse to locate the database. You can also specify a path to a Microsoft FoxPro, Paradox, or dBASE database, or a connection string to an SQL database (SQL database: A database that is based on Structured Query Language (SQL).).
- Click OK.
- Drag from the field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) to the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) the fields you want to append and any fields you want to use for setting criteria.
If all the fields in both tables have the same names, you can just drag the asterisk (*) to the query design grid. However, if you're working in a database replica (replica: A copy of a database that is a member of a replica set and can be synchronized with other replicas in the set. Changes to the data in a replicated table in one replica are sent and applied to the other replicas.), you'll need to add all the fields instead.
- If you have a field with an AutoNumber data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).), do one of the following:
Add AutoNumber values automatically
To have Microsoft Access add AutoNumber values automatically, don't drag the AutoNumber field to the query design grid when you create the query.
With this method, Access appends records and automatically inserts AutoNumber values. The first record appended has a value that is one larger than the largest entry that was ever entered in the AutoNumber field (even if the record that contained the largest AutoNumber value has been deleted).
Use this method if the AutoNumber field in the table you're appending to is a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.), and the original table and the table you're appending to contain duplicate AutoNumber values.
Keep the AutoNumber values from the original table
To keep the AutoNumber values from the original table, drag its AutoNumber field to the query design grid when you create the query.
- If the fields you've selected have the same name in both tables, Microsoft Access automatically fills the matching name in the Append To row. If the fields in the two tables don't have the same name, in the Append To row, enter the names of the fields in the table you're appending to.
- In the Criteria cell for the fields that you have dragged to the grid, type the criteria on which additions will be made.
- To preview the records that the query will append, click View
on the toolbar. To return to query Design view, click View
 on the toolbar again. Make any changes you want in Design view.
- Click Run
 on the toolbar to add the records.
|
United States
Email this link
Printer-Friendly Version
Share
Hide All
