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 pejorative 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 criterion 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 to display.

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 that uses that value.

Practical Learning: Introducing Parameterized Queries

1. Start Microsoft Access
2. Open the Altair Realtors2 database
3. In the Navigation Pane, right-click the Properties form and click Copy
4. Right-click an empty area of the Navigation Pane and click Paste
5. Set the Name to Property Review
6. Click OK
7. In the Navigation Pane, right-click Property Review and click Design View
8. Double-click the button at the intersection of the rulers to access the Property Sheet

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 [] and, inside the [], enter anything you want. Here is an example:

Most of the time, you will enter a question. The question would be presented to the user from a message 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. In the Property Sheet, click the Data tab, click Record Source and click its ellipsis button
2. Read the message box and click Yes
3. In the list of fields, double-click *
4. Again, in the list of fields, double-click Property #
5. In the bottom section of the window, click the Criteria box for Property # and type
   

   [Enter the property number you want to see]

   

6. Close the Query Builder
7. When asked whether you want to save, click Yes.
   Notice that the Record Source now contains a SQL statement
8. Close the form
9. When asked whether you want to save, click Yes
10. In the Navigation Pane, double-click Property Review
11. When asked to enter a property number, type a property number such as 749562 and click OK.
    Notice that the form shows a property and the Current Record display 1 of 1
     
    

12. Right-click the title bar of the form and click Design View
13. Right-click the title bar of the form again and click Form View
14. When asked to enter a property number, type a property number such as 247472 and press Enter
15. Close the form

Action Queries: Table Creation

We have used queries so far only to create a list of fields that would be considered for a set of records. We also mentioned that a query in Microsoft Access is simply a means of graphically representing data. Indeed, a query is based on a SQL statement. As SQL is its own, fully functional language, we can use it to perform far more operations than to only select columns for filtering. The SQL can be used to create tables, perform data entry, modify records, etc. Some of these operations can be performed visually in the query Design View. Some others can be performed using SQL statements. To know what is going on behind the scenes, you can write your own code. You can also use the Design View to start a query, and then open the code to customize the SQL statement.

As a computer language, the SQL is equipped to perform all basic and necessary operations of a database. As such, it can be used to create a table. Although you will usually use the visual means of Microsoft Access, you can still use a SQL statement to create a table. In Microsoft Access, to create a table using SQL code, start a query in Design View but do not select any table for it. Then, display the SQL View and write your code. To execute the statement, you can run it.

In the SQL, to create a table, you start your statement with the CREATE TABLE expression followed by the desired name of the table as follows:

CREATE TABLE TableName

As you have probably seen by now, every table must have at least one field (or column). The list of columns of a table starts with an opening parenthesis "(" ends with a closing parenthesis and an optional semi-colon ");". If the table will be made of more than one column, you can separate them with a comma. The formula to use is:

CREATE TABLE TableName (Column1, Column2, Column_n);

To make the statement easier to read, and because some columns can be long, you can create each on its own line. The syntax would become:

CREATE TABLE TableName
(
Column1,
Column2,
Column_n
)

To create a column, you specify its name, followed by its data type, and some possible options. Therefore, the syntax of creating a column is:

ColumnName DataType, Options

The name of a column can be in one or many words. If you put space after the first word, the SQL engine would treat the next word as a data type. For example, the following statement would produce an error:

CREATE TABLE Employees
(
Last Name
)

If you want to use space in a column name, include it between an opening square bracket "[" and a closing square bracket "]". The above statement would be changed to:

CREATE TABLE Employees
(
[Last Name]
)

Here are the SQL data types supported in Microsoft Access (remember that the names of data types are not case sensitive):

 
Here is an example that creates a table named Persons:

CREATE TABLE Employees
(
    EmployeeNumber char,
    [Date Hired] DateTime,
    FirstName VARCHAR,
    [Last Name] Text,
    Gender char,
    MaritalStatus Integer,
    HourlySalary Money,
    [Employee Picture] Image,
    [Employee Review] Memo
);

The Field Size of a Field

When studying the techniques of creating fields in a table's Design View, we saw that a property called Field Size could be used to specify the size of the value used on a field. In the SQL also, this property is fixed for most fields expect those that are text-based. Therefore, when creating a field whose data type is char, text or varchar, you can optionally specify the desired number of characters that the field should allow.

To specify the maximum number of characters of a text-based field, include it in parentheses just to the right of the data type. Here are examples:

CREATE TABLE Employees
(
    EmployeeNumber char(10),
    [Date Hired] DateTime,
    FirstName VARCHAR(40),
    [Last Name] Text(50),
    Gender char(1),
    MaritalStatus Integer,
    HourlySalary Money,
    [Employee Picture] Image,
    [Employee Review] Memo
);

By default, the user is not required to provide a value for each field when performing data entry. This is because, by default, each field is set to NULL. This is equivalent to setting the Required property of a field to No (which is the default) in a table's Design View. As this property is to No in the Design View, you can also specify it by typing NULL on the right side of any column you want. Here are examples:

CREATE TABLE Employees
(
    EmployeeNumber char(10),
    [Date Hired] DateTime,
    FirstName VARCHAR(40) NULL,
    [Last Name] Text(50),
    Gender char(1) NULL,
    MaritalStatus Integer,
    HourlySalary Money,
    [Employee Picture] Image NULL,
    [Employee Review] Memo
);

If you do not specify the NULL option, the nullity is implied. On the other hand, if you want to require the values of a field so the user cannot move to the next record unless the field is filled, type NOT NULL to its right. This would indicate to Microsoft Access that the field does not allow a null value. Here is an example:

CREATE TABLE Employees
(
    EmployeeNumber char(10),
    [Date Hired] DateTime,
    FirstName VARCHAR(40) NULL,
    [Last Name] Text(50) NOT NULL,
    Gender char(1) NULL,
    MaritalStatus Integer,
    HourlySalary Money,
    [Employee Picture] Image NULL,
    [Employee Review] Memo
);

During data entry, the user is allowed to type the same value in the same fields of two different records. For example, it is not unusual for two people to have the same last name. In some other cases, you may want each record to have a different value for a particular field. For example, a company may not allow two employees to have the same employee number or the same security numbers, just like two cars should not have the same tag number in the same state. To communicate this, on the right side, type the UNIQUE keyword. Here is an example:

CREATE TABLE Employees
(
    EmployeeNumber char(10) UNIQUE,
    [Date Hired] DateTime,
    FirstName VARCHAR(40) NULL,
    [Last Name] Text(50) NOT NULL,
    Gender char(1) NULL,
    MaritalStatus Integer,
    HourlySalary Money,
    [Employee Picture] Image NULL,
    [Employee Review] Memo
);

Practical Learning:  Creating a Table With SQL

1. To create a new database, press Ctrl + N
2. Set the name to Clarksville Ice Cream1
3. Click Create
4. CClose the default table without saving it
5. On the Ribbon, click Create
6. In the Queries section, click Query Design
7. In the Show Table dialog box, click Close
8. Right-click the middle of the window and click SQL View/li>
9. Change the statement in the Query1 window as follows:
   

   CREATE TABLE Employees
   (
       EmployeeNumber char(10) UNIQUE,
       [Date Hired] DateTime,
       FirstName VARCHAR(40) NULL,
       [Last Name] Text(50) NOT NULL,
       Gender char(1) NULL,
       MaritalStatus Integer,
       HourlySalary Money,
       [Employee Picture] Image NULL,
       [Employee Review] Memo
   );

   

10. To save the query, click the Save button 
11. Type Table Creation as the name of the query
12. Click OK
13. To execute the query, on the Ribbon, click the Run button
14. Close the Query window

After creating a table, you should populate it with the necessary values. You can open the table from the Navigation Pane and enter the values as we have done so far. The SQL provides the actual means of performing data entry on a table. In the language's standard, you can create a table and fill it with values. Alternatively, you can add various new records to the  fields of an existing table. This is also referred to as inserting records. In the SQL, data entry is performed using the INSERT INTO expression, followed by the table name, followed by the VALUES keyword, and followed by the values in parentheses. If the table is made of only one column, you can include the desired value in the parentheses. If the table is made of more than one column, you can separate the values with commas. The syntax to use is:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

The TableName attribute must be the name of an existing table of the current database. If the name is wrong, the SQL would consider that the table you are referring to does not exist. Consequently, you would receive an error. The VALUES keyword indicates that you are ready to list the values of each field of the record. The values of the columns must be included in parentheses.

In the above syntax, the value of each field of the column must be entered in the exact order of the columns as they were created in the table. Fortunately, the SQL allows you to perform data entry in the order of your choice. To do this, when creating the statement, after specifying the name of the table, open the parentheses and type the order of the columns in the order of your choice but make sure you type valid names of existing columns. Then, in the parentheses of the VALUES attribute, type the values in the order specified in the parentheses of the table name. This random order of fields presents another advantage: it allows you to specify only the fields whose values you want to enter.

If the column if a BIT data type, you must specify one of its values as 0, 1 (or -1), True, False, On, Off, Yes, or No.

If the column is a numeric type, you should pay attention to the number you type. If the column was configured to receive an integer (Int, Smallint, Integer, or Long), you should provide a valid natural number without the decimal separator.

If the column is for a decimal number (real, float, single, double, or numeric), you can type the value with its character separator (the period for US English).

If the column was created for a date data type, make sure you provide a valid date or a valid time.

If the data type of a column is a string type, you can include its value between either single or double quotes. For example, a shelf number can be specified as 'HHR-604' and a middle initial can be given as 'D'.

Practical Learning:  Performing Data Entry With SQL

1. The Clarksville Ice Cream1 database should still be opened.
   On the Ribbon, click Create and, in the Queries section, click Query Design
2. On the Show Table dialog box, click Close
3. Right-click the query window and click SQL View
4. Change the SQL statement as follows:

   INSERT INTO Employees(EmployeeNumber, [Date Hired], FirstName, [Last Name])
   VALUES('287495', 12/08/1998, 'Alex', 'Cozart');

   

5. To execute the query, click the Run button 
   You will receive a message box
    
   
6. Read it and click Yes
7. To add another record, change the statement as follows:
   

   INSERT INTO Employees(EmployeeNumber, [Last Name], Gender, HourlySalary)
   VALUES('227947', 'Jameson', 'M', 18.85);

8. Execute the statement and click Yes
9. Close the query
10. When asked whether you want to save, click No
11. In the Navigation Pane, double-click the Employees table to see its records

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. This is done through an action query. Creating the table is just one of the actions that Microsoft Access provides through a query. It is important to know that, when using an action query to perform a specify action such as creating a table, 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, as the action may be needed only once. If you perform the action of the query, you do not need to save it but once you apply its intended action, the related action is executed and stays with the database even if either you do not save the query or save it but later on delete the query.

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. For example, suppose the Cars table of your car rental database contains cars that should not be rented to customers perhaps because of their age. You can create a query that would make a list of cars that follow this rule, apply it, and then create a new table of only the necessary cars.

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. Open the Bethesda Car Rental1 database
2. In the Navigation Pane, double-click the Cars table to open it. Notice that some cars have the year set to 2000, 2002, 2003, 2005
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 Cars, click Add, and click Close
6. In the Query Type section of the Ribbon, click the Make Table button
7. In the Table Name combo box, type Cars to Consider Retiring as the name of the table

   

8. Make sure the Current Database radio button is selected and click OK
9. In the Cars list, double-click Tag Number, Make, Model, Car Year, and Condition
10. To set the criteria for the cars that need to go, click the Criteria field of the Car Year column and type <= 2005
     
    
11. To preview the list of cars that will be considered, on the Ribbon, click the View button
12. Close the query
13. When asked whether you want to save it, click Yes
14. Set the name to Create A List Of Retiring Cars as the name of the query and press Enter
15. To execute the action, in the Navigation Pane, double-click Create A List Of Retiring Cars
16. You will receive a message box
     
    
17. Read it and click Yes
18. You will receive a second message box
    
    
    
    Read it and click Yes
19. In the Navigation Pane, double-click Cars to Consider Retiring: Table to review it
20. Close the table

DROP TABLE TableName;

ALTER TABLE TableName DROP COLUMN ColumnName;