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. 


  1. Queries
  2. Reports

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,
  1. Click the Create tab then the t  icon, 
  2. In the Show table form, Double click on CUSTER table and then Close the Show Table window..
  3. 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
  4. Now drag the selected fields from the table list and drop them into the Field row
  1. 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,
  2. To see the Californians or the Oregonians, type CA and OR on separate lines in the Criteria under State

  3. To see the Californians going to England, type CA in the State criteria and England in the Destination criteria all in the same row

  4.  
     
  5. To see the customers who paid more than $1200 for their trip, type >1200 in the Cost criteria|
     
  6.  Close this query WITHOUT saving it.

III  Class Query Files

Create a different query file for each of the following.
  1. 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
  2. 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
  3. 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

  1. Create a new query and add both the tables Customer and Countries.
  2. Link the two tables by dragging the Destination field of Customer over the Destination field of Countries.
  3. Now drag only the fields FirstName, LastName, Destination, Passport, MaxPassengers, and MinPassengers on to the Field list.
  4. 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:

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.
  1. Choose a Method to create the report
     
  2.  

    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

     

     

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

     
  4.  Group the data: We will not do any grouping in this first report so click Next
  5. Sort the data: Sort first by LastName, then within the LastNames by Firstname as shown below. Click Next

  6. Select the Report Format: Select the options as shown below, then click Next
  7. Select the AutoFormat option:  For this report select Civic. Click Next
  8. Change the report title to  Customer Destination Costs, check the Preview the Report, then  click Finish. Your report should look like the one below.
  9. Edit the Report format: Click the icon

  10. You now see the general format of all reports. Each band contains text and fields that appear in the report
    1. The Report Header band appears only on the first page and the Report Footer band appears only on the last page of the report 
    2. The Page Header and Page Footer bands appears on every page.
    3. The Detail band prints data for each record
  11. Change the spacing between the lines to create less white space between the bands
    1. Point to the bottom of the report header band until the double headed arrow appears, then drag the band up a bit
    2. Do the same for the detail band to get as close to the text boxes as possible
    3. Right click on the Customer Destination Costs tab and select Print Preview to see the results of the editing
  12. Change the spacing between the text boxes in the Detail band.
    1. Click on the Destination text box and drag the right side to the left a bit.
    2. Similarly drag  the right side of the FirstName text box a bit to the left
    3. Look at the Print Preview to see the results. It should appear similar to the following:

     

  13. Right click on the Customer Destination Costs tab and save
  14. CHECK POINT: Let your instructor view your report.  
  15. 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..
    1. Open the report Customer Destination Costs by double clicking on the report
    2. Return to the Design View with the various report bands, then under the Design tab, click on the icon.
    3. 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

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

    5. Look at the Print View to see how things look so far., then come back to the Design View again.
    6. Now you need to add the totals for each group and the grand total of all groups
      1. Click on the More of the Group on Destination box and notice the options
      2. Click on the triangle beside with no totals and select the options as shown below
      3. Notice that two new bands have been added of Destination Footer and Report Footer  each containing a field that sums up the CostPerPerson
      4. 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
    7. Right click on the sum box in the Destination Footer and select Properties
      1. In the Properties pane on the right, click on the Format tab, then the triangle of the Format option in the first line
      2. Select the Currency option
      3. 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.
      4. Type  Group Total
      5. You can move it around by grabbing the handle in the upper left corner of the label box
      6. Do the same for the sum box in the Report Footer
    8. Lets make the group totals stand out a little more by making them in Red Text and the Grand total Bold black
    9. In the Design View select both the text box and the sum box in the Destination Foot band, then
      1. Right click on the selected area and select Font/Fore Color as Red
      2. Do the same with the Grand total and make it Blue
    10. The final Design View should look similar to below
    11. The final print view should look similar to below
    12. 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