Access Project 4

1
Access Project 4

• Reports, Forms, and Combo Boxes

2
Objectives

• Report
• Create a report using the Report Wizard
• Use sorting and grouping in a report
• Report controls, move, align, format, remove
• Change report properties
• Add totals and subtotals to a report
• Use multiple tables in a report
• Understand report design considerations

3
Objectives

• Form
• Use the Form Wizard to create a form
• Add a calculated field, combo boxes, and a title
  to a form
• Understand form design considerations

4
Opening a Database

• Start Microsoft Office Access and open the Ashton
  James College database, which should be stored in
  the Data folder on your C drive

5
Creating a Report Using the Report Wizard

• If necessary, in the Database window, click
  Tables on the Objects bar and then click Client
• Click the New Object button arrow on the Database
  toolbar
• Click Report
• Click Report Wizard and then click the OK button
• Click the Client Type field and then click the
  Add Field button

6
Creating a Report Using the Report Wizard

• Using the same technique, select the Client
  Number, Name, Address, City, Amount Paid, and
  Current Due fields
• Click the Next button
• Click the Next button
• Click the Next button
• Select the Tabular layout and Portrait
  orientation. Be sure the Adjust the field width
  so all fields fit on a page check box is selected

7
Creating a Report Using the Report Wizard

• If Corporate is not already selected, click
  Corporate to select it
• Click the Next button and then type Client
  Account Summary as the report title
• Click the Finish button
• Click the Close button in the window containing
  the report to close the report

8
Creating a Report Using the Report Wizard
9
Using Sorting and Grouping

• Click the Sorting and Grouping button on the
  Report Design toolbar
• Click the down arrow in the Field/Expression box,
  and then click the Client Type field in the list
• Click the Group Header property box, click the
  Group Header box arrow, and then click Yes
• Click the Group Footer property box, click the
  Group Footer box arrow, and then click Yes

10
Using Sorting and Grouping

• Click the Keep Together property box, click the
  Keep Together box arrow, and then click With
  First Detail
• Click the second row in the Field/Expression
  column, click the arrow, and then select the Name
  field
• Close the Sorting and Grouping dialog box by
  clicking its Close button

11
Using Sorting and Grouping
12
Moving a Control

• Click the Client Type control in the Detail
  section
• Point to the border of the control, but not to a
  handle. The mouse pointer should change shape to
  a hand. Once you are pointing in the correct
  position, drag the control to the left edge of
  the Client Type Header section

13
Changing the Can Grow Property

• Right-click below the section selector for the
  Detail section
• Click Properties and then, if necessary, click
  the All tab to ensure that all available
  properties appear
• Click the Can Grow property, click the Can Grow
  property box arrow, and then click Yes in the
  list that appears
• Close the property sheet by clicking its Close
  button
• Click the Print Preview button

14
Changing the Can Grow Property
15
Adding Totals and Subtotals

• Click the Close button on the toolbar to return
  to Design view
• Click the Text Box tool in the toolbox, and then
  point to and click the position shown in Figure
  4-25 on page AC 209
• Type Sum(Current Due) in the control, and then
  press the ENTER key
• Click the label to select it, and then press the
  DELETE key to delete the label
• Use the Text Box tool to add a second control in
  the position shown in Figure 4-29 on pace AC 210

16
Adding Totals and Subtotals

• Type Sum(Amount Paid) in the control, and then
  press the ENTER key
• Click the label to select it
• Click the label a second time to produce an
  insertion point
• Use the DELETE or BACKSPACE key to delete the
  Text21 (your number might be different)
• Type Subtotal as the label

17
Adding Totals and Subtotals

• Click outside the label to deselect the label
• Click the label a second time to select it
• Move the label to the position shown in Figure
  4-31 on page AC 211 by dragging the Move handle
  in the upper-left corner
• Use these techniques to add the controls in the
  Report Footer second shown on the following
  slide. The only difference is that the label
  reads Grand Total rather than Subtotal. The
  expressions in both labels are the same as the
  expressions you entered earlier

18
Adding Totals and Subtotals
19
Aligning Controls

• Click the horizontal ruler above the Current Due
  controls
• Click Format on the menu bar and then point to
  Align
• Click Right on the Align submenu
• Click the Amount Paid control in the Page Header
  section to select it
• Press and hold the SHIFT key and then click the
  Amount Paid controls in the Detail, Client Type
  Footer, and Report Footer sections to select them

20
Aligning Controls

• Click Format on the menu bar, point to Align, and
  click Right on the Align submenu
• Click the left ruler below the section selector
  for the Client Type Footer section
• Click Format on the menu bar, point to Align, and
  click Top on the Align submenu
• Use the same techniques to top-align the controls
  in the Report Footer section

21
Aligning Controls
22
Formatting Controls

• Click the label containing the word, Subtotal, in
  the Client Type Footer section
• Press and hold the SHIFT key and then click the
  label containing the words, Grand Total, in the
  Report Footer section
• Click the Font Size arrow on the Formatting
  (Form/Report) toolbar and then click 8 as the new
  size
• Click the Italic button on the same toolbar
• Click the Font/Fore Color button arrow on the
  Formatting (Form/Report) toolbar

23
Formatting Controls

• Click the color in the second row and first
  column
• Click the control for the sum of Amount Paid in
  the Client Type Footer section
• Press and hold the SHIFT key and click the
  control for the sum of Amount Paid in the Report
  Footer section, the control for the sum of
  Current Due in the Client Type Footer section,
  and the control for the sum of Current Due in the
  Report Footer section

24
Formatting Controls

• Right-click any of the selected controls
• Click Properties on the shortcut menu
• Click the Format tab to display only the Format
  properties, click the Format property box, click
  the Format property box arrow, and then select
  Currency
• Close the property sheet by clicking its Close
  button

25
Formatting Controls
26
Creating a Report that Invokes Multiple Tables

• In the Database window, click the Tables object
  and then click Trainer
• Click the New Object button arrow on the Database
  toolbar
• Click Report, click Report Wizard, and then click
  the OK button
• Click the Add Field button to add the Trainer
  Number field
• Add the First Name field by clicking it and then
  clicking the Add Field button

27
Creating a Report that Invokes Multiple Tables

• Add the Last Name field in the same manner
• Click the Tables/Queries arrow, and then click
  Table Client in the Tables/Queries list box
• Add the Client Number, Name, Amount Paid, and
  Current Due fields by clicking the field and then
  clicking the Add Field button
• Click the Next button
• Because the report is to be viewed by trainer and
  by Trainer already is selected, click the Next
  button

28
Creating a Report that Invokes Multiple Tables

• Because no additional grouping levels are
  required, click the Next button
• Click the box arrow in the text box labeled 1 and
  then click the Client Number field in the list
• Click the Summary Options button
• Click the Sum check box in the Amount Paid row
  and the Sum check box in the Current Due row
• Click the OK button and then click the Next button

29
Creating a Report that Invokes Multiple Tables

• Select the Stepped layout and Portrait
  orientation
• If necessary, click Corporate to select it
• Click the Next button, and then type
  Trainer/Client Report as the report title
• Click the Finish button
• Close the report by clicking the Close Window
  button for the window containing the report

30
Creating a Report that Invokes Multiple Tables
31
Removing Unwanted Controls

• Be sure the Reports object is selected in the
  Database window, right-click Trainer/Client
  Report, and then click Design View on the
  shortcut menu
• If a field list appears, remove it from the
  screen by clicking its Close button
• If necessary, maximize the window
• Click the control at the top of the Trainer
  Number Footer section

32
Removing Unwanted Controls

• Press the DELETE key to delete it
• Click the control that reads SUM, then press the
  delete key to delete the control
• Click the control that reads Grand Total, then
  press the delete key to delete the control

33
Changing the Column Headings

• Point immediately after the second r in Trainer
  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 behind the r, press the DELETE key to
  delete the space between Trainer and Number, and
  then press SHIFTENTER to extend the headings
  over two lines
• Using the same technique, change all the two word
  headings

34
Changing the Column Headings
35
Begin Creating a Form

• Make sure the Tables object is selected and then
  click Client
• Click the New Object button arrow, click Form,
  and then click Form Wizard. Click the OK button
• Use the Add Field button to add all the fields
  except the Client Type and Trainer Number fields
• Click the Next button
• When asked for a layout, be sure Columnar is
  selected, and then click the Next button again

36
Begin Creating a Form

• Be sure Standard is selected, click the Next
  button, and then type Client Update Form as the
  title for the form
• Click the Finish button to complete and display
  the form
• Click the Close Window button for the Client
  Update Form window to close the form

37
Adding a Calculated Field

• Click the Text Box tool 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 in Figure 4-64 on page AC
  232
• Click the position to place a text box
• Click inside the text box and type Amount
  PaidCurrent Due as the expression in the text
  box
• Click the attached label twice, once to select
  it, and a second time to produce an insertion
  point

38
Adding a Calculated Field

• Use the DELETE key or the BACKSPACE key to delete
  the current entry
• Type Total Amount as the new entry
• Click outside the Total Amount control to
  deselect it, and then click the control to select
  it once more. Handles will appear around the
  control
• Move the label portion so its left edge lines up
  with the labels for the Amount Paid and Current
  Due fields by dragging the move handle in its
  upper-left corner

39
Adding a Calculated Field
40
Placing a Combo Box that Selects Values from a
List

• Click the Control Wizards tool in the toolbox to
  select it
• Click the Combo Box tool 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 in Figure 4-68 on page
  AC 235
• Click the position
• If necessary, click the I will type in 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, and
  then type EDU as the entry

41
Placing a Combo Box that Selects Values from a
List

• Press the DOWN ARROW key and then type MAN as the
  entry
• Press the DOWN ARROW key again and then type SER
  as the entry
• Click the Next button
• Click the Store that value in this field
  option button
• Click the Store that value in this field box
  arrow and then click Client Type

42
Placing a Combo Box that Selects Values from a
List

• Click the Next button
• Type Client Type as the label for the combo box
• Click the Finish button
• Click the label for the combo box, and then drag
  its move handle to move the label so its left
  edge aligns with the left edge of the labels for
  the Amount Paid, Current Due, and Total Amount
  fields

43
Placing a Combo Box that Selects Values from a
List
44
Placing a Combo Box that Selects Values from a
Table

• Click the Control Wizards tool in the toolbox to
  select it
• Click the Combo Box tool 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 in Figure 4-68 on page
  AC 235
• Click the position
• If necessary, click the I want the combo box to
  look up the values in a table or query option
  button to select it
• Click the Next button in the Combo Box Wizard
  dialog box, click to select the Trainer table.
• Click the Next button

45
Placing a Combo Box that Selects Values from a
List

• Select fields Trainer Number, First Name, and
  Last Name, click Next
• Sort by Trainer Number
• Uncheck Hide Key Column box, click Next
• Select Trainer Number as the available field
• Click the Store that value in this field
  option button
• Click the Store that value in this field box
  arrow and then click Trainer Number

46
Placing a Combo Box that Selects Values from a
List

• Click the Next button
• Type Trainer Number as the label for the combo
  box
• Click the Finish button
• Click the label for the combo box, and then drag
  its move handle to move the label so its left
  edge aligns with the left edge of the labels for
  the Amount Paid, Current Due, Total Amount
  fields, and Client Type

47
Adding a Title

• Point to the bottom border of the Form Header
  section. The mouse pointer changes shape to a
  two-headed vertical arrow with a crossbar
• Drag the bottom border of the Form Header section
  to resize the Form Header section to the
  approximate size shown in Figure 4-80 on page AC
  241
• Click the Label tool in the toolbox and then move
  the mouse pointer, whose shape has changed to a
  small plus symbol accompanied by a label, to the
  approximate position shown

48
Adding a Title

• Click the mouse pointer in the position in the
  figure, and then type Client Update Form as the
  contents of the label
• Click outside the label to deselect it
• Click the label to select it once again
• Drag the handle in the lower-right corner to
  expand the label to the size shown on the
  following slide
• Change text size to 14, center the text
• Change the cell color to Aqua

49
Adding a Title