When you start using Microsoft Access you usually create tables first and then queries which display sorted or filtered data based on these tables. These queries are Select queries, because they select data and display the results in the way you want. Action queries go one step further and let you change data in the original table. This articles summaries the different kinds of action queries and describes how they can be used.

The different kinds of Action queries are Make Table query, Delete query, Append query and Update query. Each of these types changes data in one or more of the original tables, and are not reversible, so use with caution. It's a good idea to backup the database first before running an Action query. If this is not practical then you could create a copy of the original table or tables you are about to change, within the same database. We'll now look at these different kinds of Action Query.

The Make Table query is the safest to use because it creates a brand new table from the Make Table query and does not change the original data. In effect this type copies records from one table and pastes them to a new table. Suppose, for example, you have a staff table listing all employees by town, and you want to create a table listing staff for one particular town because you've opened a new office there. To create a Make Table query you would first create a normal select query, showing all the fields from the original staff table, but with the criteria for the particular town. If you run the query you'll see a table like display listing all staff records for that town only. Now return to query design, change the query to a Make Table type. To do this choose the option at the top of the query design display, save the query with an appropriate name and then run the query. This time Access will prompt you to enter a new table name, then tell you how many records you are about to create in this table. Proceed with the query and a brand new table is created containing just these records.

The Delete query will delete all records in a table matching your chosen criteria, so be careful. In the above example suppose we want to delete all the staff records in the original table for the town in question, because we have just copied these records into a new table. As before we create a regular select query first based on the original table, using the same town as criteria, and run the query to check that the staff records for this town are listed. Then we switch back to query design view, change the query type to Delete and save the query. Next run the query and Access prompts you to show the number of records you are about to delete from the original table. Make sure the number of records indicated is what you expect! Then proceed with the query and Access deletes all the specified records from the original table.

The Append query does the opposite job of the Make Table query. An Append query will append, or add, data from one table to another. For example suppose you close the office in that other town you used earlier and now want to add all the staff from the town table back into the main table. To do this we first create a select query based on the town table created earlier, displaying all the records, without any limiting criteria. Run the select query and you'll see all the new town staff records. Then back in query design view choose the Append query type. Once you choose the Append type, Access prompts you to enter the name of the table to append this data to. This table can be in the current database or in another database. Choose the original staff table, and Access matches the fields in the new town table with those in the original table. Then save the query. Now run the query and all the records are added to the original table.

The Update query is very powerful in a different way. It is used to change one or more field values in a particular table for all the records in the table. For example suppose the staff records contain salaries for all staff and you decide to award a 3% increase to all - well you are a generous person. An Update query can do a blanket change to every record in the table. To do this first create a select query in query design view. You only need to include the fields you intend to change, but you can also choose all the fields if you wish. Then choose the query type Update. The lower part of the design view changes and new entry "Update to" appears. In the "Update to" cell under the salary field, type in 1.03*[SALARY] and click into the next cell down just to ensure there are no Access error alerts - these can occur if the data is mistyped. The square brackets are required to identify the field name. Then run the query. Access prompts you show the number of records about to be changed. Proceed with the query and all the changes are made.

These four Action Queries can be very useful tools in your database management work. It's a good idea to backup your database first or create copies of relevant tables before making any changes. A Make table query creates a new table based on a select query and chosen criteria; a Delete query deletes all records in a particular table which match chosen criteria; an Append query adds data from one table to another, and an Update query changes one or more values of fields in all records in a table. Interested in learning more about Access queries? A really effective way would be to attend a training course and lots more about queries and other Access features.