Search all of Office.com
 
Support / Access / Access 2003 Help and How-to / Creating and Working with Databases and Objects / Database Objects / Queries / Creating Queries / Action Queries
 
 

Create a table from another table with a query (MDB)

Applies to: Microsoft Office Access 2003

 
  1. Create a query, selecting the tables or queries that contain the records you want to put in the new table.

ShowHow?

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click Queries Button image under Objects, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design View, and then click OK.
  3. In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
  4. Double-click the name of each object you want to add to the query, and then click Close.
  5. 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.
  6. To view the query's results, click View Button image on the toolbar.
  1. In query Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), click the arrow next to Query Type Button image on the toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), and then click Make Table. The Make Table dialog box appears.
  2. In the Table Name box, enter the name of the table you want to create or replace.
  3. Do one of the following:

Click Current Database.

Click Another Database, and then either type the path of the database where you want to put the new table or click Browse to locate the database.

  1. Click OK.
  2. 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 in the new table.
  3. In the Criteria cell for the fields that you've dragged to the grid, type the criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.).
  4. To preview the new table before you create it, click View Button image on the toolbar. To return to query Design view and make changes or run the query, click View Button image on the toolbar again.
  5. To create the new table, click Run Button image on the toolbar.

 Note   The data in the new table you create does not inherit the field properties or the 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.) setting from the original table.