CSCI A114 / INFO I111 Lab Notes Eight First semester 2000-2001

PROJECT FOUR: REPORTS, FORMS, AND COMBO BOXES.

Bavant Marine Services has realized several benefits from its database of marinas and technicians. The management and staff of Bavant Marine Services greatly appreciate, for example, the ease with which they can query the database. They hope to realize additional benefits using two custom reports that meet their specific needs.

The first report includes the number, name, address, city, state, zip code and total amount (warranty amount plus non-warranty amount of each marina). The second report groups the records by technician number. Subtotals of the warranty and non-warranty amounts display after each group, and grand totals display at the end of the report.

They also want to improve the data entry process by using a custom form. In addition to a title, the form will contain the fields arranged in two columns and display the total amount, which will be calculated automatically by adding the warranty and non-warranty amounts.

To assist users in entering the correct marina type, users should be able to select from a list of possible marina types. To assist users in entering the correct technician number, users should be able to select from a list of existing technicians.

Your task is to help Bavant Marina Services with this project.

This project creates two reports and a form. Grouping means creating separate collections of records sharing some common characteristic. A total for a group is called subgroup.

Custom form: it will have a title and the fields will be rearranged in two columns. It will display the total amount, calculated automatically. It will contain combo boxes, which are boxes that allow the user to select entries from a list.

Start by downloading and opening the database for this project.

Report Creation

The simplest way to create a report design is to use the Report Wizard. In somecases it will help you produce exactly the desired report. In other cases you will need to use the Report Wizard to produce a report that is as close as possible to the desired report, then you will use the Report Window to modify the report and transform it into the correct report.

If a report uses only the fields in a single table, use the table as a basis for the report. If the report uses extra fields (such as Total Amount), however, the simplest way to create the report is to create a query first, that contains only the fields required for the report.

Steps: To Create a Query

1. In the Database window click Tables on the Objects bar, if necessary, then click Marina. Click the New Object: AutoForm button arrow on the Database window toolbar. Click Query. Be sure Design View is selected, and then click the OK button. Maximize the Query1: Select Query window. Resize the upper and lower panes and the Marina field box so that all the fields in the Marina table display.

2. Double-click Marina Number. Select Ascending as the sort order for the field. Double-click the names of the fields to include the Name, Address, City, State, and Zip Code fields in the design grid. Right-click in the Field row of the column for the additional field (the field after the Zip Code field). The shortcut menu for the extra field displays. Point to Zoom on the shortcut menu.

3. Click Zoom on the shortcut menu. Type

   Total Amount: [Warranty] + [Non-warranty]

   in the Zoom dialog box and point to the OK button.

4. Click the OK button. Click the Close button for the Select Query window and then click the Yes button.

5. Type Marina Amount Query as the name of the query and then click the OK button. The query is saved, and the Select Query window closes.

1. In the Database window, click Queries on the Objects bar, if necessary, and then click Marina Amount Query. Click the New Object: AutoForm button arrow on the Database window toolbar, and then point to Report.

2. Click Report. Click Report Wizard. The New Report dialog box displays. If necessary, click the "Choose the table or query where the object's data comes from" down arrow, and then click Marina Amount Query. The Marina Amount Query is selected.

3. Click the OK button. The Report Wizard dialog box displays, requesting the fields for the report. To add the selected field to the list of fields on the report, use the Add Field button. To add all fields, use the Add All Fields button. Point to the Add All Fields button.

4. Click the Add All Fields button to add all the fields, and then click the Next button. The next Report Wizard dialog box displays, requesting the field or fields for grouping levels. This report will not include grouping levels.

5. Click the Next button. The next Report Wizard dialog box displays, requesting the sort order for the report. The query already is sorted in the appropriate order, so you will not need to specify a sort order.

6. Click the Next button. The next Report Wizard dialog box displays, requesting your report layout preference.

7. Be sure the options selected in the Report Wizard dialog box on your screen match those shown below:

   Layout: Tabular
   Orientation: Portrait
   "Adjust the field width so all fields fit on a page" is selected

   and then click the next button. The next Report Wizard dialog box displays, requesting a style for the report. If Formal is not already selected, click Formal to select it. The Formal style is now selected. Point to the Next button.

8. Click the Next button. The next Report Wizard dialog box displays, requesting a title for the report. Type

   Marina Amount Report

   as the report title.

9. Click the Finish button. The report design is complete and displays in Print Preview. (If your computer displays an entire page of the report, click the portion of the report displaying the mouse pointer).

10. Click the Close button of the window containing the report to close the report. The report no longer displays. To see the report with sample data use Print Preview. To modify the design (layout) of the report use Design View.

Steps: To Move to Design View and Dock the Toolbox

1. Click the Reports object in the Database window, right-click Marina Amount Report, and then click Design View on the shortcut menu. If a field box displays, click its Close button. The report now displays in Design view.

2. If necesarry, click the Toolbox button on the report Design toolbar to display the toolbox. If the toolbox is not docked at the bottom of the screen dock it there by dragging its title bar to the bottom of the screen. Now the field box no longer displays, and the toolbar is docked at the bottom of the screen.

Report Sections

Each portin of the report is described in what is termed a section. Notice the following sections:

• Report Header section
• Page Header section
• Detail section
• Page Footer section, and
• Report Footer section

Changing Properties

Steps: To Change the Can Grow Property

1. Point below the section selector for the Detail section.

2. Right-click and then point to Properties on the shortcut menu. The shortcut menu displays. All the controls in the Detail section are selected.

3. Click Properties and then click the All tab, if necessary, to ensure that all available properties display. The Multiple selection property sheet displays. All the properties display on the All sheet. Now click the Can Grow property, click the Can Grow box, and then click Yes in the list that displays. The value for the Can Grow property has been changed to Yes.

4. Close the property sheet by clicking its Close button, and then point to the Print Preview button on the Repprt Design toolbar.

5. Click the Print Preview button. A portion of the report displays. The names now display completely by extending to a second line. (If your computer displays an entire page, click the portion of the report displaying the mouse pointer in the figure).

Steps: To Print a Report

1. In the Database window, if necessary, click the Reports object. Right-click Marina Amount Report. The shortcut menu for the Marina Amount Report displays. Point to Print on the shortcut menu.

2. Here you'd be clicking Print, if you realy wanted to print it, but do you? Maybe later.

Now close the Report window and click Yes to save it.

Grouping in a Report

Grouping arranges the records in your report.

Steps: To Create a Second Report

1. In the Database window, click the Reports object and then right-click Create report by using wizard. Click Design view on the shortcut menu. The Report Wizard dialog box displays, requesting the fields for the report. When the Report Wizard dialog box displays, click the Tables/Queries arrow and select Technician. Fields from the Technician table display. The Tech Number field is selected. Point to the Add Field button.

2. Click the Add Field button to add the Tech Number field. Add the First Name field by clicking it and then clicking the Add Field button. Add the Last Name field in the same manner. The Tech Number, First Name, and Last Name fields are selected. Select the Marina table in the Tables/Queries list box and then point to the Add Field button. The fields from the Marina table display in the Available Fields box.

3. Add the Marina Number, Name Warranty, and Nonwarranty fields by clicking the field and then clicking the Add Field button. Click the Next button. The next Report Wizard dialog box displays. Because the Technician and Marina tables are related,the wizard is asking you to indicate how the data is to be viewed; that is, the way the report is to be organized. The report may be organized by Technician or by Marina.

4. Because the report is to be viewed by technician and by Technician is already selected, click the Next button. Because no additional grouping levels are required, click the Next button a second time. The next Report Wizard dialog box displays, requesting the sort order for detail records in the report; that is, the way in which records will be sorted within each of the groups. Click the box 1 arrow and then click the Marina Number field. The Marina Number field is selected for the sort order, indicating that within the group of marinas of any technician, the marinas will be sorted by marina number. Point to the Summary Options button.

5. Click the Summary Options button. The Summary Options dialog box displays. This dialog box allows you to indicate any statistics you want calculated in the report by clicking the appropriate check box. Point to the Sum check box in the row labelled Warranty.

6. Click the Sum checkbox in the Warranty row and the Sum checkbox in the Non-warranty row. Click the OK button in the Summary Options dialog box, and then click the Next button. The next Report Wizard dialog box displays, requesting your report layout preference. The Stepped layout, which is the correct one, already is selected. To see the effect of any of the others, click the appropriate option button. Click the Landscape option button. Landscape orientation is selected.

7. Be sure the options selected in the Report Wizard dialog box on your screen match those listed below:

   Layout Stepped
   Orientation Landscape
   "Adjust the field width so all fields fit on a page" checked.

   and then click the Next button. The next Report Wizard dialog box displays, requesting a style for the report. If necessary click Formal to select it. The Formal style is selected.

8. Click the Next button and then type

   Technician/Marina Report

   as the report title. The next Report Wizard dialog box displays, requesting a title for the report. Technician/Marina Report is typed as the title. Point to the Finish button.

9. Click the Finish button. The report design is complete and displays in the Print Preview window.

10. Close the report by clicking the Close button for the window containing the report.

Reviewing the Report Design

To complete the report design we must make a few changes.

We start by removing unwanted controls.

Steps: To Remove Unwanted Controls

1. Be sure the Reports object is selected in the Database window, right-click Technician/Marina Report, and then click Design View on the shortcut menu. If a field box displays, click its Close button. Point to the control that begins,

   ="Summary for"

2. Click the control to select it, and then press the DELETE key to delete it. In a similar fashion, delete the control below that states Sum, and then delete the control that begins with the word, Grand. The controls should be removed now.

Next, we change some column headings, but before that we need to enlarge the Page Header.

Steps: To Enlarge the Page Header Section

1. Point to the bottom border of the Page Header section. The mouse pointer shape changes to a two-headed vertical arrow with a crossbar.

2. Drag the mouse pointer down to enlarge the size of the Page Header section twice. Then drag the bold line in the Page Header section down to about the same position.

Steps: To Change the Column Headings

1. Point immediately in front of the N in Number in the heading for the first field. Click the column heading for the first field to select it. Click it a second time to produce an insertion point in front of the N, and then press the SHIFT + ENTER keys. Click imediately after the h in Tech and then type nician to complete the word, Technician, on the first line. The heading should now be split over two lines, and the heading has been changed to Technician Number.

2. Use the same technique to split the headings for the First Name, Last Name, and Marina Number fields over two lines.

Finally move the Warranty and Non-warranty fields to make room for enlarging the Name field.

Steps: To Move and Resize Controls

1. Click the Non-warranty control in the Page Header section to select it. Hold down the SHIFT key and click the Non-warranty control in the Detail section, the control for the sum of the Non-warranty amounts in the Tech Number footer section, and the control for the sum of the Non-warranty amounts in the Report footer section. Release the SHIFT key. Point to the border of the Non-warranty control in the Page Header section but away from any handle. The mouse pointer shape should change to a hand.

   Multiple controls are selected, and the mouse pointer changes to a hand.

2. Drag the Non-warranty control in the Page Header section more to the right. Drag the right sizing handle of the Non-warranty control in the Page Header section to change the size of the control and make it wider.

3. Use the same technique to move the controls for the Warranty field to the right, and change its size. Do the same thing with Name, and make the field (control) wider.

Now you can preview and (perhaps) print your report.

Close and save your report.

Report Design Considerations

When designing and creating reports keep in mind the following guidelines.

1. The purpose of any report is to provide specific information. Ask yourself if the report conveys that information effectively.

2. Be sure to allow sufficient white space between the groups.

3. You can use different fonts and sizes by changing the appropriate properties. It is important not to overuse them, however.

4. Be consistent when creating reports. Once you have decided on a general style, stick with it.

Steps: To Begin Creating a Form

1. Make sure the Tables object is selected and then click Marina. Click the New Object button arrow, click Form, and then click Form Wizard. The Form Wizard dialog box displays. The Marina Number field is selected. Click the OK button and then point to the Add Field button.

2. Use the Add Field button to add all the fields except the Marina Type and Tech Number fields. Then click the Next button. When asked for a layout, be sure Columnar is selected, and then click the Next button again. The Form Wizard dialog box displays, requesting a form style.

3. Be sure Standard is selected, click the Next button, and then type

   Marina Update Form

   as the title for the form. Click the Finish button to complete and display the form. The form displays.

4. Click the Close button for the Marina Update form window to close the form.

Next you will be changing your form's design a little.

Steps: To Modify the Form Design

1. In the Bavant Marine Services Database window, click the Forms object. Right-click Marina Update Form and then click Design View on the shortcut menu. Maximize the window, if necessary. If a field box displays, click its Close button. Be sure the toolbox displays, and is docked at the bottom of the screen. (If it is not, drag the title bar of the toolbox below the scroll bar at the bottom of the screen and release the left mouse button).

2. Click the control for the Warranty field, and then move the mouse pointer until the shape changes to a hand. (You will need to point to the border of the control but away from any handle). Move handles display, indicating the field is selected. the shape of the mouse pointer changes to a hand.

3. Drag the Warranty field to the approximate position shown by the lab instructor (move to the right top of form). The form will expand automatically in size to accomodate the new position for the field.

4. Use the same steps to move the Non-warranty field to the position shown by your instructor (underneath the Warranty field).

Steps: To Add a New Field

1. Point to the Text Box button in the toolbox.

2. Click the Text Box button in the toolbox, and then move the mouse pointer, which has changed shape to a small plus symbol accompanied by a text box, to the position shown by your instructor in class (right under the bottom left corner of the Non-warranty field).

3. Click the position to place a text box and type

   =[Warranty]+[Non-warranty]

   as the expression in the text box. Click the field label (the box that contains the word Text) twice, once to select it and a second time to display an insertion point. Use the DELETE key or the BACKSPACE key to delete the surrent entry. Type

   Total Amount

   as the new entry. The expression for the field has been entered and the label has been changed to Total Amount.

4. Click outside the Total Amount control to deselect it. Then, click the control to select it once more. Handles will display around the control. Move the label portion so its left edge lines up with the labels for the Warranty and Non-warranty fields by dragging the move handle in its upper-left corner. Click outside the control to deselect it.

Steps: To Change the Format of a Field

1. Right-click the control for the Total Amount field (the box containing the expression) to produce its shortcut menu and then click Properties on the shortcut menu. Click the All tab, if necessary, so all the properties display, and then click the Format property. Point to the Format box arrow. The property sheet for the field displays in the Text Box window.

2. Click the Format box arrow to produce a list of available formats. Scroll down so Currency displays and then click Currency. Close the property sheet by clicking its Close button. The values in the Total Amount field will display in Currency format, which includes a dollar sign and two decimal places.

Steps: To Place a Combo Box that Selects Values from a List

1. Make sure the Control Wizards button in the toolbox is recessed. Point to the Combo Box button in the toolbox.

2. Click the Combo Box button, and then move the mouse pointer, whose shape has changed to a small plus symbol accompanied by a combo box, to the position shown by your instructor (under the Total Amount field).

3. Click the position chosen to place a combo box there. The Combo Box Wizard dialog box displays, requesting that you indicate how the combo box is to receive values for the list.

4. If necessary click the "I will type min the values that I want" option button to select it. Click the Next button in the Combo Box Wizard dialog box, click the first row of the table (under Col1) and then type BIR. Press the DOWN ARROW key and then type IWO. Press the DOWN ARROW key again and then type RSO. Point to the Next button. The list of values for the combo box is entered.

5. Click the Next button. Click Store that value in this field. Click the Store that value in this field box arrow, and then click Marina Type. Point to the Next button.

   The Store that value in this field option button is selected, and the Marina Type field is selected.

6. Click the Next button. Type

   Marina Type

   as the label for the combo box and point to the Finish button.

   The label is entered.

7. Click the Finish button. Click the label for the combo box, and then move the label so that its left edge aligns with the left edge of the labels for the Warranty, Non-warranty, and Total Amounts fields. Select the label and then expand it by double-clicking the handle on its right edge so the entire Marina Type label displays.

Steps: To Place a Combo Box that Selects Values from a Related Table

1. With the Control Wizards button in the toolbox recessed, click the Combo Box button in the toolbox, and then move the mouse pointer, whose shape has changed to a small plus symbol accompanied by a combo box, to the position shown by your instructor (under the Marina Type).

2. Click to place a combo box. In the Combo Box wizard click "I want the combo box to look up the values in a table or query" if it is not already selected. Click the Next button, click the Technician table, and then point to the Next button. The Technician table is selected as the table to provide values for the combo box.

3. Click the Next button. Click the Add Field button to add the Tech Number as a field in the combo box. Click the First Name field and then click the Add Field button. Click the Last Name field and then click the Add Field button. Point to the Next button.

   The Techm Number, First Name, and Last Name fields are selected for the combo box.

4. Click the Next button. Point to the Hide key column (recommended) check box.

   The next Combo Box Wizard dialog box displays. You can use this dialog box to change the sizes of the fields. You also can use it to indicate whether the key field, in this case the Tech Number field, should be hidden.

5. Click Hide key column (recommended) to remove the check mark to ensure the Tech Number field displays along with the First Name and Last Name fields. Resize each column to best fit the data by double-clicking the right-hand border of the column heading. Click the Next button.

   The Combo Box Wizard dialog box displays, asking you to choose a field that uniquely identifies a row in the combo box. The Tech Number field, which is the correct field, is already selected.

6. Click the Next button. Click "Store that value in this field". Click the "Store that value in this field" box arrow, scroll down, and then click Tech Number. Click the Next button. Type

   Technician Number

   as the label for the combo box.

7. Click the Finish button. Click the label for the combo box, and then move the label so its left edge aligns with the left edge of the Marina Type, Warranty, Non-warranty, and Total Amount fields. Select the label and then expand it by double-clicking the handle on its right edge so the entire Technician Number label displays. Click anywhere outside the label to deselect the label.

Steps: To Add a Title

1. Point to the bottom border of the Form Header. The mouse pointer changes shape to a two-headed vertical arrow with a crossbar.

2. Drag the bottom border of the Form Header to the approximate position shown by your instructor (just make it slightly larger) and then point to the Label button in the toolbox.

3. Click the Label button in the toolbox and move the mouse pointer, whose shape has changed to a small plus symbol accompanied by a label, into the position shown by your instructor (1.5" from left border or so).

4. Drag the pointer to the opposite corner of the Form Header to form a label as wide as 3".

5. Type

   Marina Update Form

   as the form title. The title should now be entered.

Steps: To Enhance a Title

1. Click somewhere outside the label containing the title to deselect the label. Deselecting is required or right-clicking the label will have no effect. Next, right-click the label containing the title. Point to the Properties on the shortcut menu.

   The shortcut menu for the label displays.

2. Click Properties. If necessary, click the All tab on the property sheet. Click Back Color and then point to the Build button (the button with the three dots).

   The property sheet for the label displays. The insertion point displays in the Back Color property.

3. Click the Build button and then point to the color light blue in the Color dialog box that displays.

4. Click the color light blue, and then click the OK button. Scroll down the property sheet, click the Special Effect property, and then click the Special Effect box arrow.

   The list of available values for the Special Effect property displays.

5. Click Raised. Scroll down the property sheet and then click the Font Size property. Click the Font Size box arrow. Click 14 in the list of font sizes that displays. Scroll down and then click the Text Align property. Click the Text Align box arrow.

   The list of available values for the Text Align property displays.

6. Click Distribute. Close the Property sheet by clicking its Close button. If necessary, use the sizing handles to resize the label so that the entire title displays. Click outside the label to deselect it.

Steps: To Change a Tab Stop

1. Right-click the Total Amount control, and then click Properties on the shortcut menu. Click the down scroll arrow until the Tab Stop property displays, click the Tab Stop property, click the Tab Stop box arrow, and then point to No.

2. Click No, and then close the property sheet.

   The modifications to the control are complete. With this change, tabbing through the controls on the form will bypass the total amount.

Now close and save your form. Using a Form

This form will be sued just as the form in the previous project, with two differences:

1. Access will not allow changes to the Total Amount

2. The other difference is that this form contains combo boxes.

Form Design Considerations

As you design and create custom forms keep in mind the following guidelines:

1. Avoid cluttering.

2. Place the fields in logical groupings.

3. If data entered comes from a paper form try to make the form resemble the paper form as closely as possible.

SUMMARY

In Project 4, you created two reports and a form. To create the reports, you learned the purpose of the various sections and how to modify their contents. You created the first report from a query that used a calculated field. You used grouping in the second report. Then, you created and used a custom form. Steps and techniques were presented showing you how to move controls, create new controls, add combo boxes, and add a title. You changed the characteristics of various objects in the form. You also learned general principles to help you design effective reports and forms.