New Perspectives Access 2013
Tutorial 7: SAM Project 1a
Physical Therapy Specialists, P.C.
creating and enhancing reportsName
PROJECT DESCRIPTION
Jennifer Christie created a report that she uses to list the managers and employees at each location of Physical Therapy Specialists. She wants you to make some changes to this report to add subtotals and totals for the billing at each location, and to enhance the report's format. Then she wants you to create a custom report to list the current billing for each therapist, along with a grand total of all billing.
GETTING STARTED
· Download the following file from the SAM website:
o NP_Access2013_T7_P1a_FirstLastName_1.accdb
· Open the file you just downloaded and save it with the name:
o NP_Access2013_T7_P1a_FirstLastName_2.accdb
o Hint: If you do not see the .accdb file extension in the Save file dialog box, do not type it. Access 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 rptEmployeesAndManagers report, while completing Steps 1–7.
Figure 1: Last Page of rptEmployeesAndManagers Report
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
1. Open the rptEmployeesAndManagers report in Layout View, and then change the report title to Employees and Managers.
2. With the rptEmployeesAndManagers report still open in Layout View, change the Format property for the MonthlySalary field values to Currency.
3. With the rptEmployeesAndManagers report still open in Layout View, add a subtotal that sums the MonthlySalary field values for each location, and add a grand total for all locations.
4. With the rptEmployeesAndManagers report still open in Layout View, make the following updates:
a. Change the third column heading to Employee First Name, and change the fourth column heading to Employee Last Name.
b. Resize all of the column headings (and their text boxes) so that all values are fully visible.
5. Switch to viewing the rptEmployeesAndManagers report in Design View, and then update the report as described below:
a. Use a text box control in the Report Footer section to display the number of employees that appear in the report based on the EmployeeID field. (Hint: Use the COUNT function in the calculated control.)
b. Position the left edge of the text box at approximately the 2-inch mark on the horizontal ruler and 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.)
c. Left-align the label control with the Manager ID label control (which is located in the LocationName Header section).
d. Change the border style for the text box to Transparent.
e. Change the label's caption to Number of Employees.
6. With the rptEmployeesAndManagers report still open in Design View, make the following updates:
a. In the LocationName Footer section, move the subtotal of the Monthly Salaries 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.)
b. Draw a line above the subtotal of the Monthly Salaries text box control in the LocationName Footer section, using Figure 1 as a guide.
c. In the Report Footer section, move the grand total of the Monthly Salaries 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.)
d. Draw a line above the grand total of the Monthly Salaries text box control in the Report Footer section, using Figure 1 as a guide.
e. Align the lines and the text box controls below them on their right sides, and make sure that their lengths are approximately equal.
7. With the rptEmployeesAndManagers report still open in Design View, remove the alternate back color from all sections of the report.
8. With the rptEmployeesAndManagers report still open in Design View, right-align the page number text box with the right edge of the MonthlySalary text box. Save the report, preview both pages of the report, and then close the report.
9. Create a mailing label report according to the following instructions:
a. Use the tblPatient table as the record source.
b. Use Avery C2160 labels, and use the Calibri font, a font size of 11, the normal font weight, standard Black (1st column, 6th row in the Basic Color Palette) as the text color, and no special font styles.
c. In the first line of the prototype label, add the PatientFirst field, a space, and the PatientLast field.
d. In the second line of the prototype label, add the Address field.
e. 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 on the following page.
f. Sort the report by the Zip field and then by the PatientLast field.
g. Enter the report name rptPatientMailingLabels.
Close the report.
Figure 2: Prototype Label
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
While creating the custom report in Steps 10–14, refer to Figure 3 below (which shows the first page of the rptBillingByTherapist report) and Figure 4 on the following page (which shows the second page of the rptBillingByTherapist report).
Figure 3: First Page of rptBillingByTherapist Report
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
Figure 4: Second Page of rptBillingByTherapist Report
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
10. Create a blank report, and then set the report's record source to the qryBilling query. Save the report as rptBillingByTherapist without closing the report.
11. In Layout View, make the following changes to the rptBillingByTherapist report:
a. Add all the fields from the qryBilling query to the report in the following order: TherapistID, First, Last, PatientID, PatientFirst, PatientLast, Sessions, Amount.
b. Remove all the controls from the control layout.
c. Use the TherapistID field as a grouping field, and set the Keep Together property so that the whole group is together on one page.
d. Add a subtotal for each therapist and a grand total for all therapists 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.)
e. Delete the TherapistID control and label from the report.
f. Use the Last field as a sort field in ascending order.
Save the report.
12. View the rptBillingByTherapist report in Design View, and then complete the following steps:
a. Select all controls and labels in the report and, as a group, move the controls until the left edge of the First text box control (in the Details section) touches the left edge of the report.
b. Drag the right edge of the report to approximately the 7.5-inch mark on the horizontal ruler.
13. With the rptBillingByTherapist report still open in Design View, make the following updates:
a. Move the First and Last text box controls into the TherapistID Header section, as shown in Figure 5 on the following page.
b. Delete the Therapist First and Therapist Last labels in the Page Header section.
c. Increase the size of the TherapistID header slightly, and then move the First and Last text box controls so that there is one set of grid lines above and below the controls (see Figure 5).
d. Draw lines in the TherapistID Header section above and below the First and Last 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: Header Section of rptBillingByTherapist Report
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
14. With the rptBillingByTherapist report still open in Design View, make the following updates:
a. Move the subtotal control in the TherapistID Footer section down so that there is one row of gridlines above the control. (Hint: This will resize the TherapistID Footer section.)
b. 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.)
c. Add a line above the subtotal control (in the TherapistID Footer section) and above the grand total control (in the Report Footer section) in the approximate location shown in Figure 6 on the following page. The controls and the lines all should have the same length.
d. Add a label to the left of the subtotal control in the TherapistID Footer section, so that the left edge of the label is located approximately at the 5-inch mark. Change the label text to Therapist Total (without a period).
e. 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 5-inch mark. Change the label text to Grand Total (without a period).
Figure 6: Footer Section of rptBillingByTherapist Report
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
15. With the rptBillingByTherapist report still open in Design View, make the following updates:
a. For all sections of the report, set the label and text box border style to Transparent.
b. Remove the alternate back color from all group sections.
Save the report without closing it.
16. With the rptBillingByTherapist report still open in Design View, make the following updates:
a. Add the title Billing by Therapist 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.
b. Center-align the report title.
c. Add the date to the Report Header section, using the format 3/1/2016. 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.
d. 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.
e. 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 exit Access. Follow the directions on the SAM website to submit your completed project.
No comments:
Post a Comment