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 explain how to build Find Duplicate query and Find unmatched query. Then in part 3, I will explain how to build parameter query and crosstab query in MS Access 2013 and 2016.

In the previous chapter (chapter 4) at http://www.microsofttut.com/2017/03/introduction-to-queries-in-ms-access-2013-2016.html, 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.

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.

Simple Select query for the make table query

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.

Make table dialogue box

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.
Make table confirmation dialogue
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:
Click http://www.microsofttut.com/2017/03/how-to-build-advanced-action-queries-in-ms-access.html to see full post with screen to screen images and steps