How To Build Advanced & Action Queries In Ms Access 2013 & 2016

In this chapter 5 tutorial, I will explain the various advanced queries in MS Access and other RDBMS (such as Mysql), such as the four Action queries which include: the Make Table query, Append query, Update query and Delete query, then I will go over to Parameter query, Crosstab query, Find Duplicate query, Find Unmatched query, etc.

These advance queries can also be built directly in MS Access SQL view, but I don’t want to bother you with much of codes until I explain SQL in the next chapter because it is a broad topic and can be cumbersome if not properly explained. So in chapter 6 (introduction to SQL), I will revisit some of these advance queries and show you how they can be built directly in SQL view.

There is another type of query called the UNION query, but it is too complex for this chapter, I will explain it in the chapter 6 (under SQL).

This chapter 5 of this MS Access tutorial has 3 parts. This is part one and I will show you the in and out of action queries in RDBMS (Relational Database Management System) like Mysql. In part 2, I will show the 3 types of queries you can build through the MS Access New query Wizard. Then in part 3, I will explain how to build parameter query (The most versatile query type) in MS Access 2013 and 2016.

In the previous chapter (chapter 4), I explicitly explained how to build simple select queries, the various methods of filtering a database in Microsoft Access, various query functions and expressions, then lastly I illustrated the practical applications of all these in Student’s result (Grade list) system and Workers; payroll system. Also see the full tutorial course content of this Microsoft Access Tutorial Series.

BUILDING ACTION QUERIES IN MS ACCESS

Action queries are queries perform specific operations and can be used as an efficient tool for making modifications to bulk data. They can also be used to change, retrieve, and display data. Before creating an action query, it is always advisable that you first run a select query to determine how the action query will affect your records and the appropriate criteria to set in order to get a desired result.

TYPES OF ACTION QUERIES

There are 4 types of action queries which include: Make Table query, Append query, Update query and Delete query. Each of them is used to carry out a specific action on a database.

MAKE TABLE QUERY

This type of action query creates a new table from all or part of the data in other table(s) based on the specified criteria.

For example, in a database table that contains records of students, both male and female, you can filter the male students from that existing table using a simple select query and then use a make table query to create a new table for the male students using the records the simple select query returned.

To build a make table query:

1. Click the Create tab, then click the Query Design icon located in the Queries group.

2. The Show table dialogue box appears. Double click on the table(s) you wish to include in the simple select query. Then close the Show Table dialogue box.

3. Select the fields that you would like to include in the query. Referring to the example above, you can select all the fields.

4. Enter your query criteria in the Criteria row. Referring to the example above, I will type "Male" (in double quotes) under the SEX column as shown below.

5. Click the Design tab, then click the Make Table icon located in the Query Type group. This prompts the Make Table dialogue box.

6. Type a name for the new table. In my case here, I will type “Male Students Record” (without quotes)

7. Next you have to specify where you want MS Access to save your new table as shown below. You have 2 options. You can either select Current Database or Another Database. If you select the later, then you have to specify the location and name of the database. Click OK as shown below.

8. Click the Run icon located in the Results group.

9. A dialogue box pops up asking for confirmation of the action you want take. Click Yes to copy the results of the simple select query to a new table as shown below.

MS Access pastes or appends the matching records to the specified table.

APPEND QUERY

This type of action query adds a group of records from one or more tables to the end of another table(s) based on the specified criteria. It is very similar to the make table query.

For example, if a university admitted a certain number of new students and saved their records in another table. But later they wished to add the master list of all their students in another table, an append query will be needed. But first they have to create a simple select query to select the record of the new students.

NOTE:

The two tables must not have the same number of fields or columns but ensure that each field in one of the tables have an equivalent field with the same or similar data type in the other table.

To build an append query:

1. Click the Create tab, then click the Query Design icon located in the Queries group.

2. The Show table dialogue box appears. Double click on the table(s) you wish to include in the simple select query. Then close the Show Table dialogue box.

3. Select the fields that you would like to include in the query. Referring to the example above, you can select all the fields except the ID field or any other field that contains Auto Number as data type. I will tell you why you should not do so later in this chapter.

4. Click the Design tab, then click the Append icon located in the Query Type group. This prompts the Append dialogue box.

5. Next you have to specify the table you want MS Access to append your new records as shown below. You also have 2 options. You can either select Current Database or Another Database. 

If you select the later, then you have to specify the location of the database and name of the table. So referring to the example above, I will select the table containing the master records of students, which is the students record table located in the same database.

6. Click OK as shown below. 

7. Click the Run icon located in the Results group.

8. A dialogue box pops up asking for confirmation of the action you want take. Click Yes to append the records returned by the simple select query to another existing database as shown below.

MS Access appends or adds those specified record to specified table. Open the table to confirm if those records were really added

NOTE:

Make sure you don’t select a field with Auto Number as data type such as the ID column in the data source table(s). Selecting it will cause MS Access to pop an error message like the one shown below.

To correct this error:

1. Click No in the dialogue box above and then go back to Query Design view and delete the column(s) or field(s) containing Auto number as their data type.

2. Also, open the data source table(s) of the simple select query in design view and delete those field(s) or column(s).

3. Save the changes made to the data source table(s) and finally, come back to the append query and click the Run command again.

This time the query should run smoothly without any further error message if you got every other thing correctly.

UPDATE QUERY

This type of action query makes global changes to a group of records in one or more existing tables based on the specified criteria.

For example, a manager may want to increase the basic salary of workers with a particular qualification in the payroll table of his workers. He will first create a simple select query using the payroll table as data source, then he will use an update query to update the record(s) that match the criteria he set in the simple select query.

He can also use update query to update the name of a newly married female worker who just changed her surname by using the former surname as criteria in the simple select query and then use the new surname as the update value in the update query.

To build an update query:

1. Click the Create tab, then click the Query Design icon located in the Queries group.

2. The Show table dialogue box appears. Double click on the table(s) you wish to include in the simple select query. Then close the Show Table dialogue box. In my case, the table name is “workers payroll system”.

3. Select the fields that you would like to include in the simple select query. Referring to the example above, you can select all the fields.

4. Set the criteria that will return only the records you want to update. Referring to the above example, let’s say the manager wants to increase the basic salaries of workers with BSc qualifications from $43000 to $45000. 

Then I will have to specify 2 criteria: one will be in the QUAL column and the other in the BASIC SALARY column. For the QUAL row, in the Criteria row, I will type "MSc" (in quotes). Then for the BASIC SALARY column, in the Criteria row, under of the select I will type 43000 which is the former basic salary of workers with BSc qualification as shown below.

5. Click the Design tab, then click the Update icon located in the Query Type group. This will insert Update To row above the Criteria rowas shown below.

6. In the Update To row, enter the new data you want. For the example above, I will enter 45000 under the BASIC SALARY column and MSc under the QUAL row as shown below.

7. Click the Run icon located in the Results group.

8. A dialogue box pops up asking for confirmation of the action you want take. Click Yes to update the records returned by the simple select query in the data source table as shown below.

MS Access updates the matching records in the specified table.

DELETE QUERY

This type of action query deletes a group of records from one or more tables based on the specified criteria. This query uses the WHERE clause.

For example, if the university administrators discover that the records of some recently graduated students were still on the database containing the list of current students, they can use a simple select query to delete the records of these recently graduated students from the current database.

To build a delete query:

1. Click the Create tab, then click the Query Design icon located in the Queries group.

2. The Show table dialogue box appears. Double click on the table(s) you wish to include in the simple select query. Then close the Show Table dialogue box. In my case, the table name is “workers payroll system”.

3. Select the fields that you would like to include in the simple select query. Referring to the example above, you can select all the fields.

4. Click the Design tab, then click the Delete icon located in the Query Type group. This will insert Delete row above the Criteria row filling all the fields with WHERE clause.

6. In the Criteria row, enter the criteria that will filter the records you want to delete from the table. 

For the example above, I will enter a YEAR expression that will be used as criteria: Year([REG DATE])=2016 under the REG DATE column as shown below.

7. Click the Run icon located in the Results group.

8. A dialogue box pops up asking for confirmation of the action you want take. Click Yes to update the records returned by the simple select query in the data source table as shown below.

MS Access updates the matching records in the specified table.

DELETE QUERY

This type of action query deletes a group of records from one or more tables based on the specified criteria. This query uses the WHERE clause.

For example, if the university administrators discover that the records of some recently graduated students were still on the database containing the list of current students, they can use a simple select query to delete the records of these recently graduated students from the current database.

 To build a delete query:

1. Click the Create tab, then click the Query Design icon located in the Queries group.

2. The Show table dialogue box appears. Double click on the table(s) you wish to include in the simple select query. Then close the Show Table dialogue box. In my case, the table name is “workers payroll system”.

3. Select the fields that you would like to include in the simple select query. Referring to the example above, you can select all the fields.

4. Click the Design tab, then click the Delete icon located in the Query Type group. This will insert Delete row above the Criteria row filling all the fields with WHERE clause.

5. In the Criteria row, enter the criteria that will filter the records you want to delete from the table. 

For the example above, I will enter a YEAR expression that will be used as criteria: Year([REG DATE])=2016 under the REG DATE column as shown below.

6. Click the Run icon located in the Results group.

7. A dialogue box pops up asking for confirmation of the action you want take. Click Yes to delete the records returned by the simple select query in the data source table as shown below.

MS Access deletes the matching records in the specified table.

NOTE:

Make sure that the selected records are the ones you want to delete because once they are deleted, from the database table, you can’t recover them. 

That's all for this part. 

In part 2, I will explicitly explain how to build Find unmatched records query and Find duplicate records query in Microsoft access 2013 and 2016.

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.