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 both disappear from the grid and are replaced by a new row labelled Update To:. Enter the new value for the field in this row. This value can be a constant so that every record will be set to the same value or it can be a calculated value based on data stored in this or other fields - for example to raise prices by 10%.

This example will use the Date() function to update the Cli_Update field of all records to today's date. As with a regular Select query, you can add a expressions in the Criteria line to choose which records will be selected.

When you run the query, Access will warn you that you are about to update records and will tell you how many records are going to be updated. Click Yes if you are sure that you want Access to update the fields in these records.

Note that there is no way of undoing the changes to the data once you have clicked the Yes button.

Delete query

The designer for 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 both disappear. This simplicity is deceptive because this query can delete a lot of data very quickly.

The example shown here will delete all entries where the Cli_Active field is false. There is no need to select any of the other fields in the designer grid. A Delete Query will always delete all the fields from the records selected.

As with the Update query described above, Access will warn you that you are going to delete a certain number of records and will ask whether you want to continue. If you click the Yes button then Access will quickly and quietly delete the records and you have no way of retrieving them.

If you realise that you have clicked Yes by mistake then all that you can do is wait. You can't interrupt the process by closing down Access and hope that only a few records have been deleted. Closing Access in the middle of running a query will just corrupt the file and you'll lose all the records from all the tables in the database.

Warnings

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.