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

Start Microsoft Access

Open the Altair Realtors2 database

In the Navigation Pane, right-click the Properties form and click
Copy

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

In the Property Sheet, click the Data tab, click Record Source and
click its ellipsis button

Read the message box and click Yes

In the list of fields, double-click *

Again, in the list of fields, double-click Property #

In the bottom section of the window, click the Criteria box for
Property # and type

[Enter the property number you want to see]

Close the Query Builder

When asked whether you want to save, click Yes.Notice that the
Record Source now contains a SQL statement

Close the form

When asked whether you want to save, click Yes

In the Navigation Pane, double-click Property Review

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

Right-click the title bar of the form and click Design View

Right-click the title bar of the form again and click Form View

When asked to enter a property number, type a property number such
as 247472 and press Enter

Close the form

Action Queries: Table Creation

Introduction

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.

Creating a Table in SQL

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:

ColumnNameDataType, 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]
)

The Data Type of a Field

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

Data Type

Description

Char or Text

The char or the text data types
can be used for columns whose fields would receive (or
present) text. The field must not contain more than 255
characters.This is the same as Microsoft Access's Text
data type.

Varchar

This is one of the most regularly used data
types of a SQL column. It can used for strings (text) of any
kind.

Memo

This is for a field that should hold up to
65656 characters.This the same as Microsoft Access's
Memo.

Bit

This is used for a Boolean field that accepts
only a True or False, a Yes or No, and Off or On, or a 0 or 1
as a value.This is equivalent to the Yes/No data type in
Microsoft Access.

Smallint

The smallint data type can be used for
a field that would hold numbers that can range from -32,768 to
32767.

Int or Integer

Each of these data types can be used to
represent a natural number.This is the same as the
Integer in Microsoft Access.

Long

This is used for fields that would hold small
to very large natural numbers.This is the same as the
Long Integer option in Microsoft Access.

Real

Real is a relatively small data type in the
world of double-precision representation but can be used on a
column whose fields would hold numbers that can range from
-3.402823E38 to 1.401298E-45 for
negative values or from 1.401298E-45 to 3.402823E38
for positive values.This data type is close to Microsoft
Access's Single.

Float

The float data type can be used on a column
whose fields would hold numbers with a decimal portion. Like
the Single data type in Microsoft Access, the float is
mainly used on a column where number precision is not a big
issue.

Numeric

The numeric data type can be used on a column
whose fields would hold numbers with a decimal portion. It is
close to the Microsoft Access' Double data type and can
be used when numeric precision is needed.

Double

This is the same as Microsoft Access's
Double data type.

Money or Currency

This data type is appropriate for fields that
would hold numbers that represent monetary values.

Datetime

Equivalent to Microsoft Access Date/Time
data type, the DATETIME data type can be applied to a
column whose fields would display either date, time or both
date and time values.

Binary

The binary data type can let a field accept
any type of data but it is equipped to interpret the value.
For example, it can be used to receive hexadecimal numbers.

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:

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:

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:

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:

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

The Clarksville Ice Cream1 database should still be opened.On
the Ribbon, click Create and, in the Queries section, click Query
Design

In the Navigation Pane, double-click the Employees table to see
its records

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. 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

Open the Bethesda Car Rental1 database

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

Close the table

On the Ribbon, click Create and, in the Queries section, click
Query Design

On the Show Tables dialog box, click Cars, click Add, and click
Close

In the Query Type section of the Ribbon, click the Make Table
button

In the Table Name combo box, type Cars to Consider Retiring
as the name of the table

To set the criteria for the cars that need to go, click the
Criteria field of the Car Year column and type <= 2005

To preview the list of cars that will be considered, on the
Ribbon, click the View button

Close the query

When asked whether you want to save it, click Yes

Set the name to Create A List Of Retiring Cars as the name
of the query and press Enter

To execute the action, in the Navigation Pane, double-click
Create A List Of Retiring Cars

You will receive a message box

Read it and click Yes

You will receive a second message box

Read it and click Yes

In the Navigation Pane, double-click Cars to Consider Retiring:
Table to review it

Close the table

Action Queries: Appending Records

Introduction

When working in a sensitive database, if you
were the one in charge of data entry, you may prefer to use a
temporary table to create records to make sure they are validated
before actually adding them to the system. If you had created a
significant number of these records and need to add them to the main
table, the work can become overwhelming. Fortunately, Microsoft
Access allows you 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 do not create the records. They must be
retrieved from one table and transferred to another table. For
example, in our Bethesda Car Rental1 database, imagine the company
had acquired many cars at once but they were created in their own
table waiting to receive final approval after inspection. Now that
this has been done, you can add them to the list of cars that is
made available to the clerks who process data entry.

Creating an Append Query

To start 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 as we saw with the Make Table Query.

Practical
Learning: Appending Records

The Bethesda Car Rental1 database should still opened.In the
Navigation Pane, double-click Cars: Table to open it

Notice the number of cars in the current table (39). After viewing
the table, close it

In the Navigation Pane, double-click the Cars Reviewed and
Approved table to open it

After viewing the table, close it

On the Ribbon, click Create and, in the Queries section, click Query
Design

In the Show Table dialog box, click Cars Reviewed and Approved

Click Add and click Close

In the Query Type section of the Ribbon, click the Append button

In the Append dialog box, click the arrow of the Table Name combo
box, and select Cars

To execute the query, In the Navigation Pane, double-click Add
New Cars to the Application

Read the strings on the message box:

Then click Yes

Another message box will come up:

Read it and click Yes

Close the query window

From the Navigation Pane, open the Cars table to verify that the
number of cars has increased

The Update Query

If you have a large database and there are many records
that need to receive a common change, you may face an arduous task. To apply
this type of impact to a set of records, you can create a special query. To
assist you with updating many record at the same time, Microsoft Access
provides an action 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 on 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

The Bethesda Car Rental1 database should still be opened.In the
Navigation Pane, double-click the Cars table to open it

Notice that some cars have the year set to 2000, 2002, or 2005

Close the table

On the Ribbon, click Create and, in the Queries section, click Query
Design

On the Show Tables dialog box, click Cars

Click Add

Click Close

In the Query Type section of the Ribbon, click the Update button

In the Cars list, double-click Car Year and Condition

To set the criteria for the cars that need to go, click the Criteria
field of the Car Year column and type <= 2005

To specify the value to set on the column, click the Update To field
for the Condition column and type "Must be Retired"

To execute the action, in the Navigation Pane, double-click Mark
the Cars to Retire

You will receive a message box

Read it and click Yes

You will receive a second message box

Read it and click Yes

In the Navigation Pane, double-click the Cars table to review it.
Notice that all cars whose years are set to before 2006 have a new value
in the Condition

Close the table

The Delete Query

If you have a few records that need to be removed from
a table, you can delete them and Microsoft Access provides various
techniques to do it. In the SQL, to delete one or more records, you use the
DROP TABLE operator. The syntax is:

DROP TABLE TableName;

The DROP TABLE command is used only to delete
tables (and indexes), not forms or reports. The TableName parameter
must be a valid name of a table of the current database.

There are two big issues with the DROP TABLE
command: it does not warn you and it is not reversible.

Besides or instead of deleting a table, you may want to
delete individual records of a table. Microsoft Access provides an easy
mechanism of performing such an operation. 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 do not 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. Like most other action queries,
the action of a Delete Query is irreversible.

In SQL, to delete a column, the syntax to use is:

ALTER TABLE TableName DROP COLUMN ColumnName;

The ALTER TABLE and the DROP COLUMN
expressions are required. The TableName factor is the name of the
table that holds the column you want to delete. The ColumnName is the
name of the column you want to remove from the table.

Practical
Learning: Deleting Database Records

The Bethesda Car Rental1 should still be opened.In the
Navigation Pane, double-click the Cars table to open it.Notice that
a few records are set as must be retired

To set the criteria for the cars to be deleted, click the Criteria
field of the Condition column and type "Must be Retired"

To see the SQL code, right-click the query and click SQL View

DELETE Cars.Condition
FROM Cars
WHERE (((Cars.Condition)="Must be Retired"));

Close the query

When asked whether you want to save it, click Yes

Set the name to Remove Old Cars From the Application as the
name of the query

Click OK

To execute the action, in the Navigation Pane, double- click
Remove Old Cars From the Application

You will receive a message box

Read it and click Yes

You will receive a second message box

Read it and click Yes

In the Navigation Pane, double-click the Cars table to review it.
Notice that there is no more cars set to be retired

Close the table

Lesson Summary

Exercises

World Statistics

Open the World Statistics1 database

Open the Countries form and show only the countries that got their
independence in 1960

Show the countries that have their national holiday in July

Do not save anything

US Senate

Open the US Senate1 database

Create a query that includes the name of a senator and another new
column named Years in Office. The Years in Senate field would
have an expression that subtracts the year the senator was elected from
the current year to get the number of years the senator has been in
office. Save the query as Years in Senate