Update and Delete queries in Microsoft Access

You have probably already used Select Queries in a Microsoft Access database. A Select Query is the default type of query and just selects information from one or more tables in your database. It's very safe to use because it doesn't alter any of your data.

Other types of query are more powerful and more dangerous. These queries let you modify or delete records. They are listed under 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 showing Select Query which is why you might not have seen the other types of query before.

Update query

The Query Designer window changes when you choose Update Query.

The Sort and Show rows disappear and are replaced by a row labelled Update To:. 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%.

This example will update the Cli_Update field of all records to today's date.

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.

This example will delete all entries where the Cli_Update field is false.

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. Be careful to leave the selection criteria the same so that you know that this new query will affect the same records as before. Run this query and check that the warning message gives the correct number of records before pressing Yes. This will ensure that you are affecting the same records.