CSCI A114 / INFO I111
Lab Notes Twelve

First semester 2000-2001

PROJECT SEVEN:
CREATING A REPORT USING DESIGN VIEW

The management of Bavant Marine Services has determined that they need to expand their database. They want to include information on open workorders; that is, uncompleted requests for service.

These workorders are to be categorized by the requested type of service (for example, canvas repair). Once the workorders and service categories have been added to the database, they want a query created that enables them to find open workorders for all marinas, for a single marina, or for a range of marinas (for example, marinas whose number is between EL25 and FM22).

Management also wants a report that lists for each technician, each of the technician's marinas, along with all open workorders for the marina. Finally they want to be able to produce mailing labels for the technicians. Your task is to fulfill these requests.

This project creates a report. The report is organized by technician. It also creates mailing labels for the technicians. Before creating the report and labels you need to add two tables to the to the Bavant Marine Services database. These tables help Bavant track open workorders.

The first table is called Category.

Structure of Categories table
Field Name Data Type Field Size Description
Category Number Text 2 Category Number (Primary Key)
Category Description Text 50 Description of Category

Service Categories (data in the table) are listed below:

Categories table
Category Number Category Description
1 Routine engine maintenance
2 Engine repair
3 Air conditioning
4 Electrical systems
5 Fiberglass repair
6 Canvas installation
7 Canvas repair
8 Electronic systems (radar, GPS, autopilots, etc.)

The second table is called Open Workorders:

Structure of Open Workorders table
Field Name Data Type Field Size Description
Marina Number Text 4 Marina Number (Portion of Primary Key)
Location Text 6 Location (Remainder of Primary Key)
Category Number Text 2 Category Number
Description Memo - Description of Problem
Status Memo - Status of Work Request
Total Hours (est) Number - Estimate of Total Number of Hours Required
Hours Spent Number - Hours Already Spent on Problem
Next Service Date Date/Time - Date Scheduled for Next Service Related to Problem

Data in this table is listed below:

Open Workorders table
Marina Location Category Number Description Status Total Hours (est) Hours Spent Next Service Date
AD57 A21 3 Air conditioner periodically stops with code indicating low coolant level. Diagnose and repair. Technician has verified the problem. Air conditioning specialist has been called. 4 2 7/12/2001
AD57 B14 4 Fuse on port motor blown on two occasions. Find cause and correct problem. Open. 2 0 7/12/2001
BL72 129 1 Oil change and general routine maintenance (check fluid levels, clean sea strainers, etc.) Service call has been scheduled. 1 0 7/16/2001
BL72 146 2 Engine oil level has been dropping drastically. Find cause and repair. Open. 2 0 7/13/2001
EL25 11A 5 Open pockets at base of two stantions. Technician has completed the initial filling of the open pockets. Will complete the job after the initial fill has had sufficient time to dry. 4 2 7/13/2001
EL25 15A 4 Electric-flush system will periodically not function. Find cause and repair. Open. 3 0
EL25 43B 2 Engine overheating. Loss of coolant. Find cause and repair. Open. 2 0 7/13/2001
FB96 79 2 Heat exchanger not operating correctly. Technician has determined that the exchanger is faulty. New exchanger has been ordered. 4 1 7/17/2001
FM22 A21 6 Canvas was severly damaged in windstorm. New canvas needs to be installed. Open. 8 0 7/16/2001
FM22 D14 8 Install new GPS and chart plotter. Scheduled. 7 0 7/17/2001
FM22 D31 3 Air conditioning unit shuts down with HHH showing on the control panel. Technician not able to repeat the problem. Air conditioning unit ran fine through through multiple tests. Owner to notify technician if the problem repeats itself. 1 1
PM34 56 8 Both speed and depth readings on data unit are significantly less than the owner thinks they should be. Technician has scheduled appointment with owner to attempt to verify the problem. 2 0 7/16/2001
PM34 88 2 Engine seems to be making "clattering" (customer's description) noise. Technician suspects problem with either propeller or shaft and has scheduled the boat to be pulled from the water for further investigation. 5 2 7/12/2001
TR72 B11 5 Owner had accident and left large gauge in forward portion of port side. Technician has scheduled repair. 6 0 7/13/2001
TR72 B15 7 Canvas leaks around zippers in heavy rain. Install overlap around zippers to prevent leaks. Overleap has been created, but still needs to be installed. 8 3 7/17/2001

Start from this database.

We start by creating the new tables:

  1. Click Tables, right-click Create table in Design View, click Open on the shortcut menu.
  2. Enter the info for the fields in the Category table as indicated above (making Category number a primary key)
  3. Close the window, then click Yes to save the changes. Type Category as the name of the table, then click OK.
  4. Do the same for the Open Workorders table. Then select the row for Marina Number, press SHIFT, select Location, make the combination a primary key
  5. Close window, save table, call it Open Workorders, you're ready to import data.
Here's the data for the first table.

Follow the link, then save the file as category.txt on the desktop.

You will need to do the same thing for the other table, as openwork.txt also on the desktop.

Here's the data for the second table (Open Workorders).

Now you should have two text files on the desktop:

  1. category.txt
  2. openwork.txt
Import them this way:

  1. With the database open click File, Get External Data, Import.
  2. Choose Files of type: Text Files (at the bottom). Then locate the desktop.
  3. Select category.txt file, click Import.
  4. Click Delimited, then Next. Click Tab, First row contains field names, Next.
  5. Click In an Existing Table and select Category then click Next.
  6. Click Finish.
  7. Do the same for Opne Workorders (the format though will be comma delinited).

Now you have the new tables.

To change the layout of the tables do the following:

  1. Click Tables, right-click Category, click Open.
  2. Double-click the right boundary of the field selector for each field to resize columns to best fit data.
  3. Close window, save, do the same for the other table.
Add one more record to Marina: 
PM34, Peter's Marina, 453 Wilson, Torino, MI, 48268, BIR, 0, 0, 49
Now you can relate the tables.

To relate the tables do the following:

  1. Close any open datasheet click Relationships, right-click, Show Table.
  2. Add Category, and Open Workorders, click Close. Resize field boxes.
  3. Drag Marina Number from Marina to the Open Workorders, click Enforce Referential Integrity, Create.
  4. Drag Category Number from Category to Open Workorders, click Enforce Referential Integrity, Create.
  5. Organize the screen a bit, close, save changes.
To make the Category Number in Open Workorders a Lookup Wizard field do this:
  1. Click Tables, right-click Open Workorders, click Design View.
  2. Click Data Type for Category Number, choose Lookup Wizard.
  3. Click "I want the lookup column to to look up the values in a table or query", then click Next.
  4. Choose Category, click Next. Add all fields, click Next, then remove the check mark in "Hide key column (recommended)".
  5. Resize columns, click Next, Category Number should be selected, then click Next.
  6. Category Number should be the label, click Finish. Say 'yes' to save, close.
To create the query do the following:
  1. Click Tables, Marina, New Object: AutoForm, Query. Select Design View, click OK.
  2. Maximize the Query1: Select Query window, resize upper lower panes, and Marina field box.
  3. Add Open Workorders table to upper pane. Resize field box. Select
To change join properties do the following:
  1. Point to middle of join line right-click, Join Properties.
  2. Click option button 2 to include all records from the Marina table regardless of whether or not they match any open workorders. Click OK.
To change field properties do the following:
  1. Right-click Total Hours (est) column, choose Properties. Make sure Field Properties window displays.
  2. Change Format to Fixed., Decimal places to 2, Caption should be Est Hours. Close.
  3. Do the same for Hours Spent, with caption Spent Hours.
To run the query and change the layout do the following:
  1. Click Run (!), point to lower boundary of row selector for first row
  2. Drag boundary to change layout, and make it easier to see the contents of the rows/columns.
To filter the query's recordset do the following:
  1. Click Spent Hours field in query output to select (highlight) 0.00, then click Filter by.
  2. Only records with 0.00 spent hours show. Click Remove Filter. Close, save as Work Orders by Marina
To create a parameter query do the following:
  1. Click Queries, right Work Orders by Marina, Design View, maximize the window.
  2. Under Marina Number Zoom this Criterion in: 
    Between [Beginning marina number] and [Ending marina number]
  3. The click OK.
To run a parameter query do the following:
  1. Click Run (!), type AA00 for the beginning number, ZZ99 for the other.
  2. Click OK. Repeat with BL72, close, save changes.
Now add the Phone Number column to the Marina table.

To create the queries for the report do the following:

  1. Click Tables, Technician, New Object:, Query, Design View, maximize window.
  2. Add Marina. Select:
  3. Save as Technicians and Marinas
  4. Click Tables, Category. New Object: Query, Design View, maximize window.
  5. Add Open Workorders.
  6. Select the following fields:
  7. Close query and save as Workorders and Categories
To create the initial report do the following:
  1. Click Queries, Technicians and Marinas, New Object: Query, Report.
  2. Make sure Design View is selected. Click OK. Dock toolbox at the bottom if necessary.
  3. Be sure field box displays (or click Field List). Resize and move lower.
  4. Right-click in details section, choose Sorting and Grouping.
  5. In Field Expression choose Tech Number. Set Group Header to Yes.
  6. Close. You're now ready to add the fields.
To add the fields do the following:
  1. Drag tech Number to Tech Number section, then First Name and Last Name.
  2. Put the other fields in the Detail Section, then close field box.
To save the report do the following:
  1. Click Save then type Technician Master List as the name.
To add a subreport do the following:
  1. Be sure Control Wizards button is depressed.
  2. Click Subform/Subreport in toolbox, then click in Detail section.
  3. When the SubReport Wizard dialog box displays be sure the Use existing Tables and Queries option button is selected.
  4. Click Next, then click the Tables/Queries box arrow, and click Query: Workorders and Categories.
  5. Add all fields, then click Next. Make sure option Choose from a list is selected then click Next.
  6. Name the subreport Open Workorders for Marina then click Finish
  7. Close, then save changes.
To modify the subreport do the following:
  1. Reports selected, right click Open Workorders for Marina, Design View
  2. Drag lower boundary of Report Header lower.
  3. Organize the labels a bit (move them up).
  4. To extend a heading over two lines use SHIFT+ENTER
  5. Point to ruler on the left, click, all header labels are selected.
  6. Right-click any one of them and click Properties.
  7. Make Border Style = Solid, Text Align = Center, then close
  8. Do the same with the controls in ther Detail Section.
  9. Make Border Style = Solid, close.
  10. Deselect controls, select Total Hours (est) and Hours Spent. Properties.
  11. Make Format = Fixed, Decimal = 2, close.
  12. Right-click Category Description in detail, click Properties, make Can Grow: yes. Close.
To move the subreport do the following:
  1. Reports, Technician Master List, Design View.
  2. Drag subreport to desired position, drop it there.
To add a date do the following:
  1. Insert, Date and Time, Include Date should be checked.
  2. Include Time should not be checked.
  3. Date format should be the first of the three. Click OK.
  4. Move it to Page footer.
To add a page number do the following:
  1. Insert, Page Numbers
  2. N of M, Bottom of Page (Footer), Right Alignment, Show Number on First Page.
  3. Click OK, drag it to an adequate place.
To bold labels do the following:
  1. Select, right click, Properties, Font Weight: Bold.
To add a title do the following:
  1. Drag lower boundary page header.
  2. Add Technician Master List like for the forms in previous labs.
  3. Change the properties as you need.
To change the margins do the following:
  1. File, Page Setup, Margins, Left and Right to .5
  2. Close, Save.
To create the labels do the following:
  1. Tables, Technician, New Object: report, Label Wizard.
  2. Click OK, English, Filter by manufacturer: Avery.
  3. Product number is: 5095, click Next.
  4. Accept default font and colors: click Next.
  5. Make your selection, click Next, sort by Zip Code, click Next.
  6. Name it Labels Technicians then Finish.

Last updated: October 9, 2000 by Adrian German for A114/I111