“Append Query”

An “Append Query” allows us to add a record from one table to another table.
The only requirement for this is that the data types of fields you want to add to and copy from, have to be the same. The field names do not matter.

An example.
I have two tables, “Products” and “Articles”.
Both have two fields with data type “Text” and a field with data type “Numeric”.
Whether there are hundreds of other fields in a table or not it does not matter.
The only thing I would do is to add the records with the field names “ID”, “Name” and “Price” from the “NewProducts” table to the ” Products” table.

The first thing I do is connect the two tables.

I select the “Create” tab in the “Ribbon” and click the “Query Design” button.
In the “Show Table” dialog, select the table from which you want data, in this case the “NewProducts” table. Click the “Add” button, and click the “Close” button.

Change the “Query” type by clicking the “Append” button in the “Ribbon”.
In the “Append” dialog, select the table that you want to add the records to.
Just click the drop-down arrow, and select the table from the list.
In this case, the “Products” table.
Click the OK button.

Place the fields in the “Products” table in the grid.

Under each field in the “Append to” row, choose the field that you want to add new products to.

Click the “Save” button in the “Quick Access” toolbar.
Give the “Query” an appropriate name and click the OK button.

Click the “Run” button in the “Ribbon”.
“Access” warns you how many rows you are going to append to the table.

Click “Yes”.
Open the table where the new records have just been added.

Delete Query

A “Delete Query” allows us to delete multiple records from a table.
An example: suppose I want to delete products that cost less than 100 Euros from above table (Products).

First I close the table.
I select the “Create” tab in the ribbon, and click the “Query Design” button.
In the “Show Table”, select the table from which you want to delete the data, in this case the “Products” table.
Click the “Add” button, and click the “Close” button.
I add the “price” field to the grid, this is also the field where I want to add the criteria <100 (less than 100).

Before you “Run” the “Query”, it might be a good idea to first look at the result of what you are going to remove.

Just click the “View” button in the “Ribbon”. This brings us to the “Datasheet View” that shows us the records which are about to be removed.
If you are satisfied, then click the “View” button again in the “Ribbon”.
This brings us back to “Design View”.
Click the “Run” button in the “Ribbon”.
“Access” shows a warning window stating how many records you’re going to remove
Click on the “Yes” button when you are satisfied with this.
Items with a price less than 100 Euro are removed from the table.

Attention!

When you remove records from a table, you can not undo this.

Another tip:

If you wish to remove all the records from a table in one pass, select the “Fields” in the grid.
Click the downward pointing arrow and choose the top option from the drop-down menu. This is the tablename with a ‘*’ sign.

Click the “Run” button in the “Ribbon”.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats