Database Lab:
Creating Queries and Reports
CSCI 107 Lab
Raw data is worthless unless it is used somehow. There are times when we
want just portions of the actual data values, for instance a listing of
all the customers names and address who have an outstanding balance at
the end of the month. At other times, we might want only summary results,
such as the number of outstanding balances and the total of all the outstanding
balances.
In the first part of this lab, we will create several different kinds
of queries. For our purposes we will just look at the results, but queries
are the basis for many other types of data processing, such as data entry
forms, reports, view forms, mailings, etc.
The second part of the lab will guide you though creating reports. We
will follow the outline below.
-
Queries
-
Adding tables to a query and creating links between related tables
-
Modifying queries by inserting/deleting fields, sorting by a selected field(s)
-
Creating new calculated fields
-
Reports
-
Creating headers and footers
-
Inserting fields in the detail band
-
Create report totals
-
Create report groups with subtotals
I Load the database file
Continue to use the AccessLab2007.accdb that you downloaded
previously.
II Create Queries on a Single table
Various lists of data can be created from queries. This is
a method to list only selected fields, or selected records or a combination
of the two,
-
Click the Create tab then the t
icon,
-
In the Show table form, Double click on CUSTER table and then
Close the Show Table window..
-
To insert fields to the view, hold the CTRL key down and click on each
of the following fields: FirstName, LastName, City, State, Destination,
and CostPerPerson
-
Now drag the selected fields from the table list and drop them into the
Field row
-
Click the icon
under the Design tab to see the result of the query
-
Click the icon
to return to the query design
-
To filter the records to display only certain records, we use the Query
By Example (QBE) method of typing in the Criteria row an example of the
data in that column. For instance, to display only the customers from California,
-
Type in CA in the criteria row in the State column
as below
-
go to the DataSheet view, then return to the Design view
-
To see the Californians or the Oregonians, type CA and OR
on separate lines in the Criteria under State
- To see the Californians going to England, type CA in the State
criteria
and England in the Destination criteria all in the same row
- To see the customers who paid more than $1200 for their trip, type >1200
in the Cost criteria|
- All the normal comparison symbols can be used with numbers and dates.
- <, <=, >, >=, <>, =
- Close this query WITHOUT saving it.
III Class Query Files
Create a different query file for each of the following.
-
View only the first and last names and
the states of the people who are going to Alaska. Sort by last names by
selecting Ascending in the Sort cell of Last Name. Name the query QBE1
-
View the names and states and destination of
all passengers going to England from either California or Arizona, Look
at your data list to make sure everyone is going only to England. IF not,
try to figure this out by your self if there are other destinations.. If
you cannot get it to work correctly, ask your instructor.
Name the query QBE2
-
View the names, Destination, Number In
Party, CostPerPerson and create a new column that calculates the CostPerPerson
* [number in party] (the square brackets are only necessary for those
field names that contain spaces). View the results, then change the
column head of the calculated field from Exp1: to Customer Total: Name the query QBE3
IV Create a Query with linked tables
-
Create a new query and add both the tables Customer and Countries.
-
Link the two tables by dragging the Destination field of Customer
over the Destination field of Countries.
-
Now drag only the fields FirstName, LastName, Destination, Passport,
MaxPassengers, and MinPassengers on to the Field list.
-
Add the criteria that would display only the records of the people who
are going to a destination that requires a passport and save this query as QBE4.
CHECK POINT: Let Your instructor view
your queries
V Create Reports
All of the lists from tables and queries can produce printed output, but
these lists are rather drab looking and cannot produce headings, totals,
font changes or many other types of formatting options. Some of the lists
or query results have too many columns to print on one page.
Access as well as most DBMS contain some kind of report
generator that handles these missing options with methods that very closely
resemble those we used in creating forms. Our first simple report output
will look similar to the following:
. We will produce this report in this section by:
-
Selecting the table or query to use in the report
-
Creating the header, footer, detail and total bands
-
Creating the formatting and font changes
We will create the reports using the same Customer table. There are three
different ways of creating a report. In each methods you must specify what data
is to be used.
- Choose a Method to create the report
The first method is the quickest with only two clicks. But it uses EVERY field
in the table... not always the best for a table with many fields.
The second method is the hardest starting off with a completely
blank report where you must insert all the fields and do all the formatting
manually'
The third method is a little better having much of the reports bands ,
but no fields inserted
The fourth method is the one you will use in this lab where the Report
Wizard guides you through each step in the report process. So Click on the
Report Wizard icon
- Select the fields: Make sure the Customer table is
selected in the Tables/Queries Box, then select only FirstName,
LastName, Destination and CostPerPerson fields .Click Next.
- Group the data: We will not do any grouping
in this first report so click Next
- Sort the data: Sort first by LastName, then
within the LastNames by Firstname as shown below. Click Next
- Select the Report Format: Select the options as
shown below, then click Next
- Select the AutoFormat option: For this report select Civic. Click
Next
- Change the report title to Customer Destination Costs,
check the Preview the Report, then click Finish. Your
report should look like the one below.
- Edit the Report format: Click the
icon
- You now see the general format of all reports. Each band contains text
and fields that appear in the report
The Report Header band appears only on the first page and the
Report Footer band appears only on the last page of the report - The
Page Header and Page Footer bands appears on every page.
- The Detail band prints data for each record
- Change the spacing between the lines to create less white space between
the bands
- Point to the bottom of the report header band until the double headed arrow
appears, then drag the band up a bit
- Do the same for the detail band to get as close to the text boxes
as possible
- Right click on the Customer Destination Costs tab and select
Print Preview to see the results of the editing
- Change the spacing between the text boxes in the Detail band.
- Click on the Destination text box and drag the right side to the left a bit.
- Similarly drag the right side of the FirstName text box a
bit to the left
- Look at the Print Preview to see the results.
It should appear similar to the following:
- Right click on the Customer Destination Costs tab and
save
- CHECK POINT: Let your instructor view
your report.
VI Create a report that groups data
When you
created the previous report with the Report Wizard, the Grouping section was
skipped. This report will now group the data by destination and also include
the totals cost for each group and the total for all the groups..
- Open the report Customer Destination Costs by double clicking on the
report
- Return to the Design View with the various report bands, then
under the Design tab, click on the
icon.
- Since the data is going to be grouped by Destination, it must be
sorted by destination first, then sorted by Lastname and finally FirstName. You
will notice at the bottom of the Design View it shows the correct Sort
status.. Click on the
icon.
Then drag the Sort by destination box by the four dot box above the
LastName sort. as shown below
- Now we can create a group by , clicking on the
icon and
select the Destination Field.. Drag the Group on Destination
box up in front of the Sort by Destination box as below
- Look at the Print View to see how things look so far., then come
back to the Design View again.
- Now you need to add the totals for each group and the grand total of
all groups
- Click on the More of the Group on Destination box and
notice the options
- Click on the triangle beside with no totals and select
the options as shown below
- Notice that two new bands have been added of Destination
Footer and Report Footer each containing a field
that sums up the CostPerPerson
- Look at the Print View to see what things look like. Notice the
new totals under each group are not formatted as currency. Go back
to the Design View to fix this
- Right click on the sum box in the Destination Footer and
select Properties
- In the Properties pane on the right, click on the Format
tab, then the triangle of the Format option in the first line
- Select the Currency option
- To identify that as a group total, In the Design tab, click on
the icon
and then drag a text box to the left of the sum box in the Group
footer band.
- Type Group Total
- You can move it around by grabbing the handle in the upper left
corner of the label box
- Do the same for the sum box in the Report Footer
- Lets make the group totals stand out a little more by making them in Red
Text and the Grand total Bold black
- In the Design View select both the text box and the sum box in the
Destination Foot band, then
- Right click on the selected area and select Font/Fore Color as Red
- Do the same with the Grand total and make it Blue
- The final Design View should look similar to below
- The final print view should look similar to below
-
See if you can make this report a little better by putting a
Destination field in the Destination Header and removing the
Destination field from the Detail band
- CHECK POINT: Let your instructor view
your report
- If your instructor approves of your database
application, close Access. It is not necessary to save it in your CS account.