|
Access |
|
You will need to save this file to your pc. Adventure Works 04.mdb
Queries give Microsoft Access the ability to deliver information to people
in a virtually unlimited number of ways. A
query
is a database object that allows you to extract fields and
records from tables, based on criteria that you provide. Creating a query
is like asking Access a question. When you run the query, Access provides
the answer.
In Access, you can create a query either in Design view or by usin
the Simple Query Wizard. If you
know the information that you want to extract and want to create the query
without using or knowing the conventions for creating a query, the Simple
Query Wizard is the easier approach. After you understand how queries are
composed and structured, you might find it easier and more powerful to
create queries in Design view. A query can be modified in Design view
after being created, regardless of how it is created.
After you create a query, you run it by clicking the Run button on the
Query Design toolbar. When the data that meets the query specifications
has been pulled from the data sources (tables or other queries) Access
will display the query results in a datasheet of rows and columns, just
like a table.
Be sure to start Access before beginning this exercise.
Before beginning this exercise, open the Adventure Works 04
database file from the folder on your hard disk.
Using the Simple Query wizard to create a query
The Simple Query Wizard is an Access feature that allows you to quickly
and easily create a select query. You will use the Adventure Works 04
database and the Simple Query to create a query. The query you create here
will extract only the FirstName, LastName, and HomePhone fields from the
tblEmployees table and display them in datasheet format.
On the Objects bar, click Queries, if necessary.
The options to Create Query In Design View and Create Query By
Using Wizard appear. There are currently no queries in the Adventure Works
04 database.
Double-click Create Query By Using Wizard.
The first Simple Query Wizard dialog box appears. In this dialog
box you select the table or query on which this query is to be based.
After selecting the data source, you then select the fields to include
from that source.
Click the Tables/Queries down arrow, and click Table:
tblEmployees.
The fields in tblEmployees
appear in the Available Fields list.
Click FirstName in the Available Fields list, and click the >
(Add) button.
Access adds the FirstName field
to the Selected Fields list.
Click LastName in the Available Fields list, and click the >
(Add) button.
Access adds the LastName field
to the Selected Fields list.
Click HomePhone in the Available Fields list, and click the >
(Add) button.
Access adds the HomePhone field
to the Selected Fields list. You have now selected all of the fields to be
used in this query
Click Next.
The next, and
final, Simple Query Wizard dialog box appears.
In the What Title Do You Want For Your Query? box, type
qryEmployeePhoneList, and
verify that the Open The Query
To View Information option is selected, and then click Finish.
The query appears in Datasheet view. Note that only the
FirstName, LastName, and HomePhone fields from tblEmployees appear in
qryEmployeePhoneList.
Close qryEmployeePhoneList.
You can refine query results by setting
criteria
for the query. Query criteria are the rules that the query
follows to determine what information to extract. When you set criteria
for a query, Access extracts only fields and records that match your
criteria.
*Matches any one or more character(s).
? Matches any one alphabetic character.
[*] Matches any character(s) within the brackets
[!*] Matches any character(s) not in the brackets.
[*-*] Matches any character in a range. The range must be in and ascending
order (A to Z, not Z to A).
Wildcards are a convenient way to extract information that follows a
pattern or for which you remember only part of the value. For example, an
asterisk instructs Access to find fields with one or more characters in
that part of the field. Entering
A*
as the criterion for a FirstName
field will locate records containing first names that begin with
A;
Amy, Alice, Alex, Andrew, and
A
(by itself) all meet the
criterion. You can also use an asterisk at the beginning or middle of a
criterion statement. For example, to find all first names that contain the
letter
a, you would type
*a*.
Make sure the Database window is displayed and Queries is
selected on the Objects bar.
Creating a query in Design view
You used the Simple Query Wizard in the previous exercise to create a
query. You can also create queries in Design view, as you will do now. You
will create a query using the tblHumanResourcesData table to extract a
list of all employees who have the text Marketing in their job title
Double-click Create Query In Design View.
The Query Design view window and the Show Table dialog box
appear. The Show Table dialog box lets you add tables (or queries) to the
Query Design window.
In the Show Table dialog box, click tblHumanResourcesData, and
click Add.
A field list displaying the
fields in the tblHumanResourcesData table appears at the top of the Query
Design view window.
Close the Show Table dialog box by clicking the Close button.
The Show Table dialog box closes, and the Query Design view
window becomes completely visible.
In the tblHumanResourcesData field list, click EmployeeID, and
drag the field name to the first blank cell in the Field row in the Design
grid.
Access displays the EmployeeID field name in the Field row, and
also note that Access adds the table, or source, name below it.
Repeat step 4 to add the DateHired, Title, and Salary fields to
the blank cells in the Field row.
Your
design grid should now contain four fields
In the DateHired column, click in the Criteria row, and type
*/*/93.
When run, the query will find only records for employees who were
hired during 1993.
In the Title column, click in the Criteria row, type
*Marketing*, and
then press Enter.
When run, the query will find
only records for employees who were hired during 1993
and
whose job title includes the
word
Marketing.
On the Query Design toolbar, click the Run button.
The query results appear in datasheet format, displaying records
for any employee hired during 1993 whose job title includes the word
Marketing.
Close the query saving changes.
For fields containing numbers, you can use a
comparison
operator
in a criterion to define a range
of values for one or more fields, such as data greater than, less than, or
equal to a specific value. A comparison operator is a character or series
of characters that defines the values that the query should find. For
example, if you are modifying a table that tracks orders placed by
Adventure Works, you could use a comparison operator in a query to extract
all orders for which the price is $500 or more. If you were searching an
academic database, you could search for all students with a 4.0 GPA for a
specified school term.
You have created a couple of simple queries so far using two different
methods. You will now create a query that uses comparison operators to
extract a range of data. You will now create a query based on tblAllOrders
that will return a list of orders for which the price per unit was $100 or
more. Then you will restrict the query to find only orders for more than
one unit.
Double-click Create Query In Design View.
The Query Design view window and the Show Table dialog box
appear; tblAllOrders is already selected in the Show Table dialog box
because it is the first table in the database.
In the Show Table dialog box, click Add, and then click Close.
The Show Table dialog box closes, and the field list for
tblAllOrders appears in the top section of the Query Design view window.
In the tblAllOrders field list, double-click OrderNo.
The OrderNo field appears in the first blank cell in the Field
row in the Design grid.
Repeat step 3 for the OrderDate, NoOfUnits, and Price fields.
Your design grid should now contain four fields.
In the Price column, click in the Criteria row, and type
>=100.
When run, the query will select and display only records for
items with a price equal to or more than $100.
On the Query Design toolbar, click the Run button.
The query results appear in a datasheet showing all records in
the table that have items with a price equal to or more than $100
On the Query Datasheet toolbar, click the View button.
The query appears in Design view.
In the NoOfUnits column, click in the Criteria row, and type
>1.
The query will display records of orders for more than one unit
that also have a unit price of more than $100.
On the Query Design toolbar, click the Run button.
The query results appear, showing only records for items with a
price equal to or more than $100 and for more than one unit.
Close the query saving changes.
Email your completed file as an attachment
to me. Good job well done. |