LAB # 07
Creating a Database using Access 2007
(Queries and Reports)
Objectives:
To learn about specific queries
and reports in MS Access 2007
Detail:
Specific
Queries
Select Query menu Design window. It should look like the image below. If you’re not then click the Design button in the upper
left corner of the screen (like
the one on the right).
Click-in the cell
to the right of Criteria: in
the State column. You will see a
flashing cursor (Make sure you are in
the State column.). Type-in the
abbreviation for one of the states you
entered in your Personnel Table. Your Query should look like the image below.
Now click-on (!). A new Query1 window will
appear. Only persons from the state you selected should show.
Now we'll
look for Favorite Numbers larger than
600. Type-in >600 in
the Criteria cell under the Favorite Number Column.
Click-on (!). Everyone with a favorite number
larger than 600 should show. If no one
is indicated you don't have a
person with a number larger than 600,
or you might have typed the >600 incorrectly.
Reports
Reports can be very complex. In this tutorial we'll cover the very basic steps of creating Reports. A good manual or some knowledgeable assistance will be essential to
mastering reports.
Your Access screen should look similar to the image
below.
Click the Create Tab and the Create Ribbon will appear. Look at the Ribbon on the right side. You will see the Create Reports Group
Click the Report Wizard selection in the Reports Group.
The
Report Wizard Menu screen will appear:
Click-on First Name,
then click-on > (notice how the First Name field went from the Available Fields: to Selected
Fields:). Now do the same with the Last Name, State, Gender and Salary fields. These are the fields that will appear in our first
report. Your Report Wizard menu screen should
look like the one below.
If it does,
click-on the Next> Button. If
not, use the << to bring all the fields back and try again.
Grouping
in Reports
This
Report Wizard menu screen asks if you want to add Grouping. Grouping simply “groups” records by an item in the
report you are designing. We’ll group by state. This means that “records” from a state will be in a “group”
(e.g. people from Virginia will be in one group, the folks from Washington in
another, and so on). This will be easy to see when we look at the report. So, click-on State, then click-on >.
If you make a mistake, no problem, just use the <. Your screen should now look like the one below.
Click-on Next> again. Another Report Wizard
menu screen will follow.
First, the above screen requests that you indicate a Sort Order. This simply means that within each “group, the alphabetic order in which you want the
fields sorted. We’ll sort by Last Name and
then First Name. This way you’ll
have the names, grouped by state, in Last Name order and, where you have
several people with the same Last Name, they’ll be sub-sorted in First Name
order. Notice the Ascending button
to the right of the Sort boxes. This indicates that the Field that you select
is in A to Z or ascending order. If you click-on this button, it will reverse
the order from Z to A, or descending order. Click-on the small down arrow to the right of the first box and
select Last Name. Leave the
order as Ascending. Now, select First Name in the second box. When you
are finished, your Report Wizard menu screen
should look like the one above.
This is a
report in Tabular (Columnar)
format. Your screen should look something like the one below
0 comments:
Post a Comment