Home | Work & Business | Microsoft Software | Access Queries | How to Make a Table From Query in Access

How to Make a Table From Query in Access

by William M Rea

Overview

Creating a new table in Microsoft Access is relatively easy using an action query. You will first need to create and execute a data select query before building the new table with the action query. In this example you will extract data from the Orders table and then create a shipping address table. A shortened version of the two tables might look like: Customers Table ------- Orders CUSTOMERID ------ ShipName CompanyName ------ ShipAddress ContactName ------- ShipRegion ContactTitle --------- ShipPostalInfo

42e842b5-4d80-fafb-4d6d-4b7cb2b6f377

Step 1

Create and test the data selection query. Select the Data Extraction icon in the toolbar. (This is the icon with two overlapping rectangles). Create the new table and then add Customers and Orders tables to it. Drag the CustomerID field from the Customers table and drop it into the query's first column. This will link the shipping address table to the Orders table. Drag all of the other fields to the new table to create fields in the shipping address table. In the ShipName column's first Criteria row, type <>[Customers].[CompanyName]. In the next row of the ShipAddress column, type <>[Customers].[Address]. Now Double click in any empty area in the Query Design window's upper pane. This will open the Query Properties window. Open the Unique Values drop-down list and select YES. Now click the toolbar's Run icon (!). The selected data from the query is now available.
Step 2

Create the new shipping address table from the data obtained from the previous query. On the toolbar select the Design view icon and choose Query, Make-Table Query. Type in the name of the new table, tblShipAdresses, into the text box and click OK. Click on the run bar (!) on the toolbar. A record count will be displayed. Click YES to create the tblShipAddresses table. Now press the F11 key to activate the database window and open the new table. You will need to change the properties of some of these new fields to make them required. This can be done by changing the values in the field's Required property field.
Step 3

Add the table to the database. Close the new tblShipAddresses. Answer yes to the save question and yes again when you are asked to apply new data integrity rules. The new data integrity rules can be added using the Relationships button from the toolbar.