Create A Make Table Query

You will be editing the underlying data stored in Northwind tables, so we strongly urge you to make a copy of the database before proceeding. Navigate to the Northwind.mdb file in the Samples subfolder of your Microsoft Office folder. Copy Northwind.mdb to the Clipboard and paste it in another folder. Rename the file CopyOfNorthwind.mdb, which you can now open and use without worrying about changing the original file

Choose Tools and Relationships. Right-click the line between Employees and Orders and click Delete; choose Yes to confirm. (We’ll discuss why we did this at the end of the tutorial.) Close the Relationships window.

Often when you want to create a new table from existing records, you can simply copy and paste the table. But the Make Table query has the advantage of allowing you to set criteria to define the records you want.

In the Database window, double-click the Employees table to open it. In the Country column, note that some employees are from the United States, and others are from the United Kingdom. We’ll use a Make Table query to create a table with the records of the UK employees.

Close the table. With the Employees table selected in the Database window, choose Insert and Query. With Design View selected, click OK. Open Query and select Make Table Query. In the Make Table dialog box, typeUK Employees. With Current Database selected, click OK.

Double-click the asterisk (*) in the Employees field list, which will add all the fields from the Employees table to the new UK Employees table. Double-click the Country field to add it to the design grid. Deselect the Show checkbox in the field. On the Criteria row, typeuk. Click the View button at the far left of the toolbar to view the records that will be in the new table. Click View again to return to Design view.

Click the Run button on the main toolbar, which is the button with the exclamation point (!). The message says Access will paste four records in a new table. Click Yes. Close the query without saving it. In the Database window, double-click the UK Employees table to open it. Note that it has the records of the four UK employees. Close the table.

Create A Delete Query

In the Database window, click once to select the Employees table. Choose Insert and Query. With Design View selected, click OK. Open Query and select Delete Query. The Delete row is added to the design grid. Double-click the asterisk (*) in the field list to add all fields to the query. Click in the Delete row of the Employees.* column and open the drop-down list. Note there are two choices, From and Where. Use From for the column that determines the field or fields from which records will be deleted. Use Where for the column that has criteria that defines the records that will be deleted. The selection for the Employees.* column should be From.

Double-click Country to add it to the design grid. Where is automatically inserted in the Delete row. Typeuk in the Criteria row. This query deletes recordsfrom all the fields of the Employees tablewhere the Country field has the UK value.

Click the View button. The four records that will be deleted are displayed. Click View again to return to Design view. Click the Run button. The message says you are about to delete four records. A Delete query cannot be undone, so always make sure you want to delete the records before proceeding. Click Yes to delete the records. Close the query without saving it.

Create An Append Query

In the Database window, click once to select the UK Employees table. Choose Insert and Query, and with Design View selected, click OK. Open Query from the toolbar and choose Append Query. In the Append dialog box, open the drop-down list in the Table Name field and choose Employees. With Current Database selected, click OK. Double-click the asterisk (*) in the UK Employees field list to add all the fields to the design grid.

Click the View button. The four records shown will be added to the Employees table. Click the View button to return to Design view. Click the Run button. In the message, click Yes to add the four records to the Employees table. Close the query without saving it. Open the Employees table. The table now includes all the UK records, and you have the same Employees table you started with. Close the table. In the Database window, click the UK Employees table and press the DELETE key. Click Yes to confirm.

Use Delete Queries With Caution

With respect to Delete queries, this tutorial used a greatly simplified example that did not reflect typical real-world conditions. Delete queries are often difficult to use and apply, because any well-constructed database will have an interlocking web of relationships. These associations, depending on how they are defined, can dramatically affect the impact a delete query will have on your database.

For example, at the start of the tutorial you deleted the relationship between the Employees and Orders table. Had you kept it intact, you wouldn’t have been able to execute the Delete query because referential integrity was enforced. Thus, you couldn’t delete records on the one side of the relationship (Employees) because it contains values on the other side (Orders). On the other hand, you can initiate a Delete query in a single table and wind up deleting records throughout your database if the Cascade Delete option is chosen. Thus, make sure you have a good understanding of how relationships work before executing Delete queries. Close the database and exit Access

Home Access 2003 Queries Create Queries That Do More Than Retrieve Data
Search MS Office A-Z   |   Search Web Pages/ Design A-Z