Access Project 4

1. Access Project 4 Reports, Forms, and Combo Boxes

2. Objectives • Create a report using the Report Wizard • Use sorting and grouping in a report • Move controls • Change properties • Add totals and subtotals to a report

3. Objectives • Align and format controls • Remove controls • Change labels and column headings • Use multiple tables in a report • Remove unwanted controls

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

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

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

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

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

9. Creating a Report Using the Report Wizard

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

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

12. Using Sorting and Grouping

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

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

15. Changing the Can Grow Property

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

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

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

19. Adding Totals and Subtotals

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

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

22. Aligning Controls

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

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

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

26. Formatting Controls

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

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

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

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

31. Creating a Report that Invokes Multiple Tables

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

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

34. 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 SHIFT+ENTER to extend the headings over two lines • Using the same technique, change all the two word headings

35. Changing the Column Headings

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

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

38. 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 Paid]+[Current 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

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

40. Adding a Calculated Field

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

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

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

44. Placing a Combo Box that Selects Values from a List

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

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

47. Adding a Title

48. Summary • Create a report using the Report Wizard • Use sorting and grouping in a report • Move controls • Change properties • Add totals and subtotals to a report

49. Summary • Align and format controls • Remove controls • Change labels and column headings • Use multiple tables in a report • Remove unwanted controls

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