New Perspectives Access 2016 | Module 7: SAM Project 1b GRMH Recreation Centers Creating And Enhancing Reports   GETTING STARTED Open the file NP_AC16_7b_FirstLastName_1

Subject:MS ExcelPrice:19.87 Bought3

Share With

New Perspectives Access 2016 | Module 7: SAM Project 1b

GRMH Recreation Centers

Creating And Enhancing Reports

GETTING STARTED

• Open the file NP_AC16_7b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC16_7b_FirstLastName_2.accdb by changing the “1” to a “2”.
  • If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
  • Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

Refer to Figure 1 below, which shows the last page of the rptLocationsAndBilling report, while completing Steps 1–8.

Figure 1: Design View of the rptLocationsAndBilling Report

1. Open the rptLocationsAndBilling report in Layout View, and then change the report title to Locations and Billing. (Hint: Do not type the period.)
2. With the rptLocationsAndBilling report still open in Layout View, change the Format property for the Amount field values to Currency.
3. With the rptLocationsAndBilling report still open in Layout View, add a subtotal that sums the Amount field values for each location, and then add a grand total for all locations.
4. With the rptLocationsAndBilling report still open in Layout View, make the following updates:
   1. Change the first column heading to Member Last Name and the second column heading to Member First Name. (Hint: Do not type the period.)
   2. Set the value for the Can Grow property for the Member Last Name field to Yes.
   3. Resize all of the column headings (and their text boxes) so that all values are fully visible. (Hint: Values may already be visible.)
   4. Switch to viewing the rptLocationsAndBilling report in Design View, and then update the report as described below:
      1. Use a text box control in the Report Footer section to display the number of records that appear in the report based on the LastName field. (Hint: Use the COUNT function in the calculated control.)
      2. Position the left edge of the text box at approximately the 1.75" mark on the horizontal ruler, and then position the top edge of the text box approximately in the second row of grid dots in the Report Footer section. (Hint: You may need to slightly increase the height of the Report Footer section.)
      3. Left-align the label with the Location Name label (which is located in the LocationName Header section).
      4. Change the border style for the text box to Transparent.
      5. Change the label’s caption to Number of Invoices. (Hint: Do not type the period.)
      6. With the rptLocationsAndBilling report still open in Design View, make the following updates:
         1. In the LocationName Footer section, move the subtotal of the Amount text box control down so that its top edge is approximately in the second row of grid dots in this section. (Hint: You will need to slightly increase the height of the LocationName Footer section.)
         2. Draw a line above the subtotal of the Amount text box control in the LocationName Footer section, using Figure 1 as a guide.
         3. In the Report Footer section, move the grand total of the Amount text box control down so that its top edge is approximately in the second row of grid dots in this section. (Hint: You may need to slightly increase the height of the Report Footer section.)
         4. Draw a line above the grand total of the Amount text box control in the Report Footer section, using Figure 1 as a guide.
         5. Align the lines and the text box controls below them on their right sides, and make sure that their lengths are approximately equal.
         6. With the rptLocationsAndBilling report still open in Design View, remove the alternate back color from all sections of the report.
         7. With the rptLocationsAndBilling report still open in Design View, right-align the page number text box with the right edge of the Amount text box. Save the report, preview both pages of the report in Print Preview, and then close the report.
         8. Create a mailing label report according to the following instructions:
            1. Use the tblMember table as the record source.
            2. Use Avery C2160 labels, and use the Georgia font, the 11 pt. font size, the Semi-bold font weight, standard Black (1^st column, 6^th row of the Basic Color Palette) as the text color, and no special font styles.
            3. In the first line of the prototype label, add the FirstName field, a space, and the LastName field.
            4. In the second line of the prototype label, add the Address field.
            5. In the third line of the prototype label, add the City field, a comma, a space, the State field, a space, and the Zip field. Confirm the prototype label matches Figure 2 below.
            6. Sort the report by the City field and then by the LastName field.
            7. Use rptMemberMailingLabels as the name of the report.

Save and close the report.

Figure 2: Prototype Label

While creating the custom report in Steps 10–16, refer to Figure 3 on the following page (which shows the first page of the rptBillingByTrainer report) and Figure 4 on page 5 (which shows the second page of the rptBillingByTrainer report).

Figure 3: First Page of the rptBillingByTrainer Report

Figure 4: Second Page of the rptBillingByTrainer Report

1. Create a blank report, and then set the report’s record source to the qryBilling query. Save the report as rptBillingByTrainer without closing the report.
2. In Layout View, make the following changes to the rptBillingByTrainer report:
   1. Add all the fields from the qryBilling query to the report in the following order: TrainerID, tblTrainer.FirstName, tblTrainer.LastName, MemberID, tblMember.FirstName, tblMember.LastName, Sessions, Amount.
   2. Remove all the labels and controls from the control layout.
   3. Use the TrainerID field as a grouping field, and then set the Keep Together property so that the whole group is together on one page.
   4. Add a subtotal for each trainer and a grand total for all trainers using the Amount field and the SUM function. (Hint: Remember to check the Show subtotal in group footer option so that the subtotals are visible in the report.)
   5. Delete the TrainerID control and label from the report.
   6. Sort the report by the tblMember.LastName field in ascending order.

Save the report.

1. View the rptBillingByTrainer report in Design View, and then complete the following steps:
   1. Select the Amount control and label, the subtotal control in the TrainerID Footer section, and the grand total control in the Report Footer section. Reduce their width to approximately 0.8” by dragging their right edges to the left using the mouse.
   2. Select all controls and labels in the report, and, as a group, move the labels and controls until the left edge of the tblTrainer.FirstName text box control (in the Detail section) touches the left edge of the report. (Hint: The left edge of the tblTrainer.FirstName control should be at the 0” mark on the horizontal ruler).
   3. Drag the right edge of the report to approximately the 7.75" mark on the horizontal ruler.
   4. With the rptBillingByTrainer report still open in Design View, make the following updates:
      1. Move the tblTrainer.FirstName and tblTrainer.LastName text box controls into the TrainerID Header section, as shown in Figure 5 on the following page.
      2. Delete the tblTrainer.FirstName and tbl.Trainer.LastName labels in the Page Header section.
      3. Increase the size of the TrainerID Header slightly, and then move the tblTrainer.FirstName and tblTrainer.LastName text box controls so that there is one set of grid lines above and below the controls (see Figure 5).
      4. Draw lines in the TrainerID Header section above and below the tblTrainer.FirstName and tbl.Trainer.LastName text boxes as shown in Figure 5. The lines should begin at the left edge of the report. The end of the lines should align with the right edge of the Amount label in the Page Header section.

Save the report without closing it.

Figure 5: rptBillingByTrainer Report in Design View

1. With the rptBillingByTrainer report still open in Design View, make the following updates:
   1. Move the subtotal control in the TrainerID Footer section down so that there is one row of gridlines above the control. (Hint: This will resize the TrainerID Footer section.)
   2. Move the grand total control in the Report Footer section down so that there is one row of gridlines above the control. (Hint: This will resize the Report Footer section.)
   3. Add a line above the subtotal control (in the TrainerID Footer section) and above the grand total control (in the Report Footer section) in the approximate location shown in Figure 5 above. The controls and the lines all should have the same length.
   4. Add a label to the left of the subtotal control in the TrainerID Footer section, so that the left edge of the label is located approximately at the 6" mark. Change the label text to Trainer Total. (Hint: Do not type the period.)
   5. Add a label to the left of the grand total control in the Report Footer section, so that the left edge of the label is located approximately at the 6" mark. Change the label text to Grand Total. (Hint: Do not type the period.)
   6. With the rptBillingByTrainer report still open in Design View, make the following updates:
      1. For all sections of the report, set the label and text box border style to Transparent.
      2. Remove the alternate back color from all sections of the report.

Save the report without closing it.

1. With the rptBillingByTrainer report still open in Design View, make the following updates:
   1. Add the title Billing by Trainer to the Report Header section, and then remove the control layout from the title. Move the title label so that the left edge of the control touches the left edge of the report.
   2. Center-align the report title.
   3. Add the date to the Report Header section, using the format 3/21/2019. Do not include the time. Move the control so that the right edge of the date text box aligns with the right edge of the Amount label in the Page Header section.
   4. Add the page number with the format Page N of M to the Page Footer section. Do not change the default center alignment, and do not uncheck the check box associated with showing page numbers on the first page.
   5. Align the right side of the page number text box with the right side of the Amount label in the Page Header section.

Save the report, preview both pages of the report in Print Preview, and then close the report.

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.