Parameterized and Action Queries

Parameterized Queries

Introduction

A parameterized query is a query that is missing a value in a criteria. The word "missing" here seems negative to indicate something bad in the query but it is not so. The query is purposely created like that so that a value would be provided to its filter.

At the time the query is created, the filter is setup in a certain way. When the query is run, the missing value must be provided to complete the criterion, then the query is complete, and the result of the query is produced. Most of the time, a parameterized query is created so the user must specify the specific record(s) to produce.

Although we specified that the query is parameterized, it is in fact the SQL statement that is parameterized. This means that you can create a SQL expression used as the Record Source of a form or report, but oblige the user to provide the missing information so the form or report would display only the record(s) that use(s) that value.

Practical Learning: Introducing Parameterized Queries

1. Start Microsoft Access
2. In the list of files, click Altair Realtors3 from the previous lesson
3. On the Ribbon, click Create and click Query Design
4. On the Show Table dialog box, click Properties, click Add, and click Close
5. In the top list, double-click PropertyNumber, PropertyType, City, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Condition, and MarketValue
6. To see the results, on the status bar, click the Datasheet button
7. If necessary, on the Ribbon, click Home.
   Change the following characteristics:
   Font Name: Calisto MT (if you don't have that font, select Times New Roman)
   Background Color: Green, Accent 6, Lighter 40% (Theme Colors: 10th column, 4th row)
   Alternate Row Color: Green, Accent 6, Lighter 80% (Theme Colors: 10th column, 2nd row)

   

8. After viewing the result, in the Views section of the Ribbon, click the arrow below the View button and click SQL View
9. Change the SQL statement as follows:

   SELECT Properties.PropertyNumber,
          Properties.PropertyType AS [Property Type],
          Properties.City,
          Properties.Bedrooms AS Beds,
          Properties.Bathrooms AS Baths,
          Properties.FinishedBasement AS [Basement?],
          Properties.IndoorGarage AS [Garage?],
          Properties.Condition,
          Properties.MarketValue AS [Market Value]
   FROM   Properties;

10. Display the Design View of the query

Creating a Parameterized Query

When creating a parameterized query, you must provide a parameter to it. To proceed, open the query in Design View and select the necessary columns. In the Criteria box of the field that will hold the criteria, type the square brackets[]. Inside the brackets, enter anything you want, such as a message or a question. The message or question will be presented to the user who must type a value in a text box. Once the user provides the value and clicks OK or presses Enter, the query would run.

Practical Learning: Creating a Parameterized SQL Expression

1. Click the Criteria box for the PropertyNumber column and type: [Enter the property # to view:]

   

2. Switch the query to Datasheet View to view the result
3. Enter the property number as 192703

   

4. Click OK

   

5. Return to the Design View
6. Delete [Enter the property # to view:]
7. Click the Criteria box for the Property Type column and type: [Enter the type of property you want to see:]

   

8. Switch the query to Datasheet View to view the result
9. Enter the property type as townhouse

   

10. Press Enter

    

11. Return to the Design View

Parameterized Conjunction

You can create a parameterized query that asked two or more questions that must be combined to complete the query. To do this, in the Design View of the query, enter a message for the Criteria box of each column.

Practical Learning: Creating a Parameterized Disjunction

1. Clich the Criteria box for the Bedrooms column and type [How many bedrooms do you require?]

   

2. Switch the query to Datasheet View to view the result
3. Enter the property type as single family and press Enter
4. Enter the number of bedrooms as 5 and press Enter

   

5. Return to the Design View
6. Delete [How many bedrooms do you require?]

Parameterized Disjunction

A parameterized disjunction requests two or more values. The query produces all record that include any of the values. To create such a parameterized query, add the first question or message to the Criteria box of the desired field. Then add the second message or question to the Or box of the same column. If you need additional values, enter their questions or messages in the boxes below the Or cell of the same column.

Practical Learning: Creating a Parameterized Disjunction

1. Clich the Or box of the Property Type column and type [What other type of property od you want to see?]

   

2. Switch the query to Datasheet View to view the result
3. Enter the first property type as townhouse and press Enter
4. Enter the second property type as single family and press Enter

   

5. Close the query without saving it

Action Queries

Introduction

Microsoft Access provides some ready-made queries that allow you to execute semi-complex actions on existing tables. The actions are performed as queries. When you use an action query, you are not creating a regular query in the strict sense. Most of these queries are usually useful only once. For this reason, you will hardly need to save them.

The Make Table Action Query

Instead of first creating a table then filling it with values, if you already have values stored in a table, Microsoft Access allows you to create a new table filled with values from that table.

Using queries, there are two main techniques you can use to create a new table for your database. Microsoft Access provides a technique called Make Table Query. This type of query is used to retrieve all or some fields of an existing table and, instead of creating a new query that depends on an existing table, you would get a brand new table, independent of any existing table. This type of action can let you create a new table based on a rule applied to an existing table.

To create a table using a Microsoft Access query, start a new query in Design View. Then, in the Query Type section of the Ribbon, click the Make Table button . This would present you with a Make Table dialog box that allows you to specify a new name for the query or to select an existing table that would receive the new data.

Practical Learning: Using Make-Table Queries

1. The Altair Realtors3 database should still be opened.
   On the Ribbon, click Create and, in the Queries section, click Query Design
2. On the Show Tables dialog box, click Properties, click Add, and click Close
3. In the Query Type section of the Ribbon, click the Make Table button
4. In the Table Name combo box, type Condominiums as the name of the table

   

5. Make sure the Current Database radio button is selected and click OK
6. In the Properties list, double-click PropertyNumber, PropertyType, City, Locality, State, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Condition, and MarketValue
7. In the bottom side of the window, click the Criteria box for the PropertyType column and type condominium
8. Click the check box of Show to remove the check mark

   

9. To create the table, on the Ribbon, click the Run button
10. You will receive a message

    

11. Read it and click Yes
12. Close the query without saving it
13. In the Navigation Pane, double-click the Condominiums table

    

14. Close the table

The Update Query

If you have a large list and many records need to receive a common change, you can create a special query named Update Query. An Update Query allows you to change the existing values of one or more columns of a table. In other words, you can retrieve the values that are already in the fields of a table, change these values and have the table saved with them.

To create an Append Query, start a query in the Design View. In the Query Type section of the Ribbon, click the Update button . When creating the query, you will have to set a condition that Microsoft Access will apply to a table to find out what record(s) need(s) to be updated. To do this, you use the Criteria box of a column, just like we learned during data analysis.

Practical Learning: Updating Records

1. Open the Metro System1 database from Lesson 18
2. In the Navigation Pane, double-click the Silver Line table to view its records

   

3. Close the table
4. On the Ribbon, click Create and, in the Queries section, click Query Design
5. On the Show Tables dialog box, click Silver Line, click Add, and click Close
6. In the Query Type section of the Ribbon, click the Update button
7. In the top list, double-click MetroLine
8. In the bottom side of the window, click the Criteria box for the MetroLine column and type Silver

   

9. To execute the action, on the Ribbon, click the Run button
10. You will receive a message box

    

    Read it and click Yes
11. Close the query without saving it
12. In the Navigation Pane, double-click the Silver Line table to review it

    

13. Close the table

An Append Query

If you have a table with a significant number of records and you need to add those records to another table, the work can become overwhelming. Microsoft Access provides a feature to create a special query that can be used to add many records to a table in one step.

An Append Query allows you to add records to an existing table but you don't create the records. They must be retrieved from one table and added to another table.

To create an Append Query, start a query in the Design View. In the Query Type section of the Ribbon, click the Append button . You will be presented with a dialog box that expects you to either select the table to which you will add the records, or to specify the name of a new table that would receive the records. If you want to add the records to an existing table, you can select it from the combo box.

As you may imagine, appending a record consists of adding a record to a table. If you want to do this in SQL, simply create an INSERT statement.

Practical Learning: Appending Records

1. The Metro System1 database should still be opened.
   In the Navigation Pane, double-click the MetroStations table and scroll completely down in the list
2. Notice that there are no stations for the Silver line.
   Close the table
3. On the Ribbon, click Create and, in the Queries section, click Query Design
4. In the Show Table dialog box, click Silver Line, click Add, and click Close
5. In the Query Type section of the Ribbon, click the Append button
6. In the Append dialog box, click the arrow of the Table Name combo box, and select MetroStations

   

7. Click OK
8. In the top list, double-click StationNumber, StationName, MetroLine, and Location
9. To execute the query, on the Ribbon, click the Run button
10. Read the message:

    

    Then click Yes
11. Close the query without saving it
12. In the Navigation Pane, double-click the MetroStations table and scroll completely down in the list
13. Notice that now it contains stations for the Silver line.
    Close the table

The Delete Query

If you have a few records that need to be removed from a table, Microsoft Access provides various techniques to delete such records. To delete a group of records in one action, you can create a special query called a Delete Query.

Like all other Action queries, a Delete Query allows you to select the necessary fields of a table. The first difference between this and the Select Query is that the Delete Query, like all other action queries, selects or considers all fields, even those you don't include in your statement. The other columns are those that would be used to specify the rules under which a record must be deleted.

You can use a criterion or many criteria to set the rule to follow in order to get rid of (a) record(s). Like most other action queries, the action of a Delete Query is irreversible.

Practical Learning: Deleting Database Records

1. The Metro System1 database should still be opened.
   In the Navigation Pane, double-click the MetroStations table to open it
2. Scroll down and notice that some stations are for the Maroon line but we know there is no Maroon metro line
3. Close the MetroStations table
4. On the Ribbon, click Create and, in the Queries section, click Query Design
5. On the Show Tables dialog box, click MetroStations, click Add, and click Close
6. In the Query Type section of the Ribbon, click the Delete button
7. In the top list, double-click MetroLine
8. In the bottom side, click the Criteria box for the column and type maroon

   

9. To execute the action, on the Ribbon, click the Run button
10. You will receive a message box

    

11. Read it and click Yes
12. In the Navigation Pane, double-click the MetroStations table to open it
13. Scroll down and notice that there are no more Maroon stations
14. Close the MetroStations table
15. Close the table

Special Operations on Queries

Merging the Records of Many Tables

If you have various tables, you can create a query that combines their records so you can examine those records when they are put together. Here is an example:

To assist you with this, the SQL provides an operator named UNION. The primary formula to use it is:

SELECT columns-names FROM table-name1
UNION [ALL]
SELECT columns-names FROM table-name2

This is equivalent to creating two SELECT statements. Each contains some columns and a table. Both statements must contain the same number of columns. The data types of the columns on each position must be compatible. Here is an example:

SELECT Employees.EmployeeNumber,
       Employees.FirstName,
       Employees.LastName
FROM Employees
UNION
SELECT Clerks.ClerkCode,
       Clerks.FirstName,
       Clerks.LastName
FROM Clerks;

Selecting Into a Table

If you have some records in an existing table, you can copy all or some of those records to put them in a new table you create. To do this, the formula to follow is:

SELECT fields INTO new-table-name FROM existing-table [WHERE condition]

To use all columns and all records, start with the SELECT operator, followed by *, followed by INTO, followed by a name for the table you want to create, followed by FROM, and the name of the original table that contains the records. Here is an example:

SELECT * INTO CompanyRecipients FROM Employees;

Instead of using all columns, you can specify only the desired field after the SELECT keyword. Here is an example:

SELECT EmployeeNumber, LastName, FirstName, EmploymentStatus
INTO Salaried FROM Employees;

Instead of using all records, you can use a condition by which the records would be selected and added to the new table you are creating. To set the condition, you can create a WHERE statement as the last in the whole expression. Here is an example:

SELECT *
INTO FullTimeEmployees
FROM Employees
WHERE EmploymentStatus = 'Full Time';

Removing All Records from a Table

In the SQL, to remove all records from a table, use the DELETE command. The formula to follow is:

DELETE columns-names FROM table-name;

Normally, you use * as the columns-names. The table-name parameter must be a valid name of a table of the current database. Here is an example:

DELETE * FROM States;

When you execute, you will receive a warning:

If you click Yes, all records from the table will be removed.

Deleting Some Recordx

To delete one or more records, add a WHERE condition to a DELETE command. The formula to follow is:

DELETE columns-names FROM table-name WHERE condition;

The WHERE clause specifies the condition by which some records will be deleted. Here is an example:

DELETE * FROM [Staff Members] WHERE [Marital Status] = "Single";

Once again, when you execute, you will receive a warning that allows you to decide whether you want to continue the operation.

Practical Learning: Ending the Lesson

• Close Microsoft Access