Types of query in Access

You have probably used Select Queries in Access to select information from your database. Other types of query let you modify or delete records. These are listed on the Query Type toolbar button:

This button appears whenever the Query Design window is active on screen. If you click the arrow on the right of this button, a list will drop down to show the types of query available:

Select Query
Crosstab Query
Make Table
Update Query
Append Query
Delete Query

The button always defaults to Select Query

Update query

The Query Designer window changes when you choose . The and rows disappear and are replaced by a row labelled . Enter the new value for the field in this row. This can be a constant so that every record will be set to the same value or it can be a calculated value - for example to raise certain prices by 10%.

When you run the query, Access will warn you that a number of records are about to be updated. If you click Yes then Access will update the fields in these records. There is no way of undoing the changes once you have clicked Yes.

Delete query

A Delete Query is simpler than an Update Query. The only change that you'll notice in the Query Designer when you select Delete Query is that the Sort and Show rows will disappear.

When you run the query, Access will warn you that a number of records are about to be deleted. If you click Yes then Access will delete these records. There is no way of retrieving the records that have been deleted.

Warning

Both the Update and Delete queries will show a message telling you how many records are going to be affected and warning you that you will not be able to undo this change. This is a serious warning. These two types of query can destroy a lot of data very quickly.

A good way of working with these types of queries is to start by creating an ordinary Select Query. Set the criteria to pick the records that you want to alter or delete then run this query and check that it has indeed picked the records that you intended. Make a note of the number of records it selects.

When you are happy that the Select Query is picking the correct records, click the toolbar button and convert the query into an Update or Delete query. Run this query and check that the warning message gives the correct number of records. This will confirm that you are affecting the records that you wanted to.