Access Practice 5

Prefer to watch and learn? Check out this video tutorial:

Complete the following Practice Activity and submit your completed project.

For Access Practice 5, we will continue to work with the same database that manages student and faculty data in the Honors program at Paradise Valley Community college. For this practice, we will focus on creating Reports for the database. We will create four reports:

1. 1. Student Contact Information
   2. Student Scholarships
   3. Faculty Contact Information
   4. Faculty Division Listing

Open Access, under open, select the database Lastname_Firstname_Access_Practice4. You may need to browse to your flash drive or other location that you saved this database to. In backstage view, select Save As, and save the database as Lastname_Firstname_Access_Practice5. Ensure the database is saved on your flash drive or another safe location.

1. If necessary, select Enable Content to show all of the database content.
2. Ensue the navigation pane is open, and all database objects are displayed.
3. On the Create tab, in the Reports group, notice there are several ways to create a report. We will focus on the following:
   • Report
   • Report Wizard
4. In the navigation pane select the Students table, but do not open it. On the Create tab, in the reports group, select Report. This will create a report with all of the fields on the Students table. We will modify this report to provide Student Contact Information.
5. You can close the navigation pane to allow more room to view the report. Notice that the Students report opens in Layout View. The Report Layout Tools contains four tabs:
   • Design
   • Arrange
   • Format
   • Page Setup
6. Select the Student ID label. It should be the first column of the report. On the Report Layout Tools, Arrange Tab, in the Rows & Columns group, select Select Column. Then press the Delete key on your keyboard to delete the entire column. Notice how the other columns shift automatically.
7. Select the Field of Interest label, then select the entire column. Right click and select Delete Column.
8. Use either one of the techniques you just learned to delete the following columns:
   • Graduation Year
   • Faculty ID
   • Scholarship
9. The only remaining columns should be Last Name, First Name, and E-Mail.
10. On the Design tab, in the Themes group, select the Office theme. Under Colors, select Blue. Under Fonts, select Arial.
11. Scroll to the bottom of the report, and delete the page count text box.
12. At the top of the report, double click title Students. Remove the text inside the label and type Student Contact Information.
13. Save the report as Student Contact Information and close it. Notice how the report shows up in the Navigation Pane under Reports.
14. On the Create tab, in the Reports group, select Report Wizard.
15. In the Report Wizard dialog box, under Tables/Queries select the Query: Scholarships Awarded.
16. Select all of the available fields by clicking the double arrow and moving them to the selected fields. Last Name, First Name and Scholarship should all be in the Selected fields box. Select Next.
17. Do not add any grouping levels, and select Next.
18. Add an ascending sort on the Last Name field, and then an ascending sort on the First Name field and then select Next.
19. For the report layout, select Justified, and portrait orientation. Check the box to Adjust the fields width so all fields fit on a page. Select Next.
20. Title your report Student Scholarships, and preview the report. Select Finish.
21. Notice that the report opens in Print Preview. Use the scroll bar at the bottom of the page to scroll through all 6 pages of the report.
22. Close the Print Preview. You may also want to close the Navigation pane so that you have more space to work with the report.
23. Ensure the report is in Design View. Notice there are several sections to this report:
    • Report Header
    • Page Header
    • Detail
    • Page Footer
    • Report Footer
24. In the Report Header section, double click inside the title Student Scholarships. Remove the text and type Honors Program Scholarship Awardees.
25. Turn on the Property Sheet. In the detail section, select the Last Name text box. Take care not to choose the label. The text box has a control source of Last Name. With the Last Name text box selected, on the format tab of the property sheet, change the height to .4” and press enter.
26. Select the First Name text box. Take care not to choose the label. The text box has a control source of First Name. With the Last Name text box selected, on the format tab of the property sheet, change the height to .4” and press enter.
27. In the detail section, select Scholarship and then press delete on your keyboard. Select the check box underneath, and press delete on your keyboard.
28. Resize the Detail section by selecting the outer box and dragging up about an inch to remove any excess space in the detail section of the report. Save and close the report.
29. On the Create tab, in the Reports Group, select Report Wizard. We will use the Report Wizard to write a report that provides Faculty Contact Information. In the Report Wizard dialog box, select the Table: Faculty.
30. In the Available Fields, select Last Name, First Name and E-Mail, and move it to the Selected Fields by using the single arrow, or double clicking each field. Select Next.
31. Do not add any grouping levels, and select Next.
32. Add an Ascending sort to the Last Name field, and then an Ascending sort to the First Name field. Select Next.
33. Choose the Tabular Report Layout with Portrait orientation. Check the box to Adjust the field width so all fields fit on a page.
34. Title the report Faculty Contact Information, and preview the report. Select Finish.
35. Take a moment to view the report in Print Preview and scroll through both pages of the report. Close the Print Preview.
36. Open the Faculty Contact Information report in Design View.
37. In the Report Header, change the title label to Honors Faculty E-Mail Addresses and manually center the title across the report page.
38. Resize the Report Header so there is about an inch of extra space under the title.
39. In the empty space under the report title, on the Design tab, in the Controls group, select Insert Image. Browse to find the image Access_Practice_5_Puma, which should be located in your data files.
40. Click once, or drag and draw to insert the image of the Puma in the Report Header. Center the Puma image under the report title.
41. In the Page Header, change the Label that reads E-Mail to say Faculty E-Mail Address*. Be sure to include the asterisk.
42. Select and Drag the line below the Report Footer to create about an inch of space in the Report Footer. You can also right click on the Report Footer, select Properties, and in the Property Sheet change the Height to 1’’.
43. On the Report Design Tools, Design Tab, in the Controls group, select Label. Click in the Report Footer to add the Label. Inside the Label type: *If a faculty email is missing from this report, please contact HR@maricopa.edu.
44. Save and close the Faculty Contact Information Report.
45. For our final report, we will use the Report Tool to create a report that provides the Faculty Division Listing. Select, but do not open the Faculty table. On the Create tab, in the Report Group, select Report. You may close the Navigation pane to allow more room to work on the report.
46. With the report in layout view, on the Report Layout Tools, Design Tab, in the Grouping Totals group, turn on the Group & Sort by clicking it one time. The Group, Sort, and Total dialog box will display at the bottom of the page.
47. In the Group, Sort and Total section, select Add a group. In the select a field box, select Division. Notice how the report now groups faculty based on the division they work in. Click More, and select the option to keep whole group together on one page. Close the Group, Sort and Total pane by clicking the X to Close Grouping Dialog Box. Take care not to remove the group that was just added.
48. Select the Faculty ID label, right click and select Delete Column. Use the same process to delete the E-Mail column.
49. Select the title of the report which reads Faculty. Change the report title to Faculty Division Listing.
50. Change the Report View to Design View. In the Report Header, select and delete the auto logo which looks like an image of a book.
51. Right click on the Report Header, and select Fill/Back Color. Choose a light gray color from the first column. Ensure you can still see the text after the fill color is applied.
52. In the Page Header, select the Division Label. On the Report Design Tools, Format Tab, in the Font group, select the arrow next to Background color. Under Theme Colors, select White, Background 1, Darker, 15%. Apply the same background color to the Division text box in the Division Header.
53. In the Page Footer, delete the page number count by selecting it, and then pressing delete on your keyboard.
54. In the Report Footer, add a label control and then type: ‘Please e-mail HR@maricopa.edu with any changes to this report.’ Resize the label control if necessary to ensure all of the words display.
55. In layout view, select the Division label, right click and select the entire column. Select the right outer edge of the column and drag it to the left until the column is about 2.5’’ wide. Another option is to turn in the property sheet, Format Tab, and verify the width is 2.5’’.
56. Change the view of the report to Print Preview. In the lower right hand corner, use the zoom scroll bar to zoom in and out of the report. Then, use the Page arrows to view all pages of the report. Ensure extra blank pages will not be printed, and make any modifications to your report as necessary.
57. Save the report as Faculty Division Listing and Close all open database objects. Ensure all database objects are closed and the Navigation Pane is open and displays All Access Objects.
58. Take a moment to verify you have the following database objects in your database.
    • Tables
      • Faculty
      • Students
    • Queries
      • Health Sciences Students
      • Honors Program Faculty and Students
      • Honors Student List
      • Scholarships Awarded
      • Students Missing Field of Interest
    • Forms
      • Faculty
      • Students
    • Reports
      • Faculty Contact Information
      • Faculty Division Listing
      • Student Contact Information
      • Student Scholarships
59. To exit out of the database, first close any database objects that are open. Select the File tab, and take note of where your database is saved. Then select close. Then, exit out of Access.
60. Submit your entire database for grading per your instructor’s instructions.