Showing posts with label Microsoft Access Assignments. Show all posts

NP_Access2013_T7_P1a_FirstnameLastname_2

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.

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 (1^st column, 6^th 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

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

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

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

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.

NP_Access2013_T6_P1a_FirstnameLastname_2

New Perspectives Access 2013

Tutorial 6: SAM Project 1a

Physical Therapy Specialists, P.C.

creating forms to display dataName

PROJECT DESCRIPTION

Jennifer Christie wants to view data in the Physical Therapy Specialists database in specific ways, and she asks you to create several forms for her. First, you'll create a split form so she can see data in the tblTherapist table in a form and in a datasheet at the same time. Then she wants to view data about employees and their managers. Finally, she asks you to create a custom form to display therapists and their current invoices from the tblBilling table data. The custom form should include a control that lets her select the therapist's data to view by clicking the therapist's name in a list.

GETTING STARTED

· Download the following file from the SAM website:

o NP_Access2013_T6_P1a_FirstLastName_1.accdb

· Open the file you just downloaded and save it with the name:

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

PROJECT STEPS

1. Use the Split Form tool to create a split form based on the tblTherapist table, save the form as frmTherapistSplitForm, and then close the form.

2. Open the frmPatient form in Layout View and make the following changes:

a. Select the control layout (which contains the Patient ID, Patient First, Patient Last, Address, City, State, Zip, Phone, Birth Date, and Gender fields), and then change the control padding to Narrow.

b. Select the Patient ID control pair, and then change the control margins to Medium.

Save and close the form.

Refer to Figure 1a on the following page while completing Steps 3–5.

Figure 1a: frmEmployeesAndManagers Form in Form View

3. Open the frmEmployeesAndManagers form in Layout View. Change the title in the Form Header section to Employees and Managers, and then change the font color of the form title to Black (2^nd column, 1^st row in the Standard Colors palette). Save the form.

4. With the frmEmployeesAndManagers form still open in Layout View, make the following changes:

a. Remove the Location ID control pair from the stacked layout. (Hint: To make this control pair easier to manipulate, you may want to move it below the other controls on the form.)

b. Reduce the width of the Location ID text box to approximately 0.5 inches.

c. Anchor the Location ID control pair to the bottom right of the form.

Save the form, but do not close it.

5. Switch the view of the frmEmployeesAndManagers form to Design View. Change the tab order for the fields in the Detail section so the LocationID field is the last field in the tab order. (Hint: Refer to Figure 1b on the following page.) Test the form in Form View, confirm that it looks similar to Figure 1a, and then save and close the form.

Figure 1b: frmEmployeesAndManagers Form – Tab Order

Refer to Figure 2 on the following page while completing Steps 6–7.

Figure 2: frmLocationsandTherapists Form in Layout View

6. Create a blank form in Design View, and then make the following updates:

a. Add the Location ID, LocationName, Address, City, State, ZipCode, and Phone fields (in that order) from the tblLocation table to Detail section of the form.

b. Reposition the controls to the approximate location shown in Figure 2. (Hint: The left edge of the text box controls should be at the 2-inch mark on the horizontal ruler.)

c. Select the LocationName and Address text box controls (but not the labels) and resize them to match Figure 2. (Hint: The right edge of the controls should be at approximately the 4-inch mark on the horizontal ruler.)

d. Save the form with the name frmLocationsandTherapists, but do not close the form.

7. Add a subform to the frmLocationsandTherapists form (using the Subform Wizard) by completing the following steps:

a. Insert the subform in the approximate location shown in Figure 2.

b. Include the TherapistID, First, Last, and Specialty fields (in that order) from the tblTherapist table in the subform.

c. Accept the default link using the common LocationID field between the form and subform.

d. Save the subform with the name frmTherapistSubform.

e. Delete the label associated with the subform.

f. If necessary, resize and reposition the subform to the approximate location shown in Figure 2.

g. Switch to Layout View. Resize the Therapist First, Therapist Last, and Specialty fields in the subform to best fit the data they contain.

Save and close the frmLocationsandTherapists form.

Refer to Figure 3 and Figure 4 while completing Steps 8–15.

Figure 3: frmTherapistBilling Form in Form View

Figure 4: frmTherapistBilling Form in Design View

8. Open the frmTherapistBilling form in Design View and complete the following steps:

a. Delete the Therapist Last label and change the caption for the Therapist First label to Therapist.

b. Select the First and Last controls, and then align their left edges.

Save, but do not close, the form.

9. With the frmTherapistBilling form still open in Design View, complete the following steps:

a. Resize the width of the First, Last, and Specialty controls (in the Detail section of the form) so that the right edges of the controls are approximately at the 4-inch mark on the horizontal ruler.

b. Resize the width of the TherapistID, LocationID, and Certification controls (in the Detail section of the form) so that the right edges of the controls are approximately at the 2.5-inch mark on the horizontal ruler.

10. With the frmTherapistBilling form still open in Design View, move the Accepts Minors label to the approximate position shown in Figure 3. Save the form.

11. Add a title to the frmTherapistBilling Form Header section. Use Therapists and Billing for the title instead of the default text.

12. Make sure the form's Record Source property is set to tblTherapist, and then add a combo box (using the Combo Box Wizard) in the Form Header section by completing the following steps:

a. Add a combo box to the approximate location shown in Figure 4. (Hint: Position the pointer at the top of the Form Header and at the 5-inch mark on the horizontal ruler when inserting this combo box.)

b. The combo box should find a record in the form based on a selected value.

c. Select the TherapistID, Last, and First fields (in that order) for the combo box.

d. Do not hide the key column.

e. Resize the columns to best fit in the combo box.

f. Use the label Therapist ID for the combo box.

g. If necessary, resize and reposition the combo box control to approximately match the location shown in Figure 4.

Save the form and close it.

13. Open the frmBillingSubform form in Design View. Add a calculated control to the frmBillingSubform Form Footer section by completing the following steps:

a. Insert a text box in the Form Footer section at the approximate location shown in Figure 5 on the following page. (Hint: Position the pointer at approximately the 0.5-inch mark on the vertical ruler and at approximately the 1-inch mark on the horizontal ruler.)

b. Enter an expression in the text box control that displays the total amount of the invoices displayed in the form. (Hint: The calculated control should contain the SUM function and the Amount field.)

c. Set the Name property of the calculated control to txtSumAmount.

Save and close the frmBillingSubform.

Figure 5: frmBillingSubform Form in Design View

14. Return to viewing the frmTherapistBilling form in Design View. Add calculated controls to the Detail section of the form by following the directions below:

a. Insert a text box control in the approximate location shown in Figure 4.

b. Change the Caption property of the label associated with the text box to Number of Invoices.

c. Add an expression that displays the txtCountBilling control value from the frmBillingSubform subform.

d. Add a second text box control (below the control you just added) in the approximate location shown in Figure 4.

e. Change the Caption property of the label associated with the second text box to Total Amount.

f. Add an expression to the second text box that displays the txtSumAmount control value from the frmBillingSubform subform.

g. Set the format for this calculated control to Currency.

h. Resize and position the calculated controls as shown in Figure 4.

i. Set the Tab Stop property for both calculated controls to No.

Save the form without closing.

15. Draw a rectangle around the calculated controls and their labels, as shown in Figure 3. Set the rectangle's properties to match those listed below:

a. Set the Border Width property to 3 pt. (Hint: The border width is the same as the Shape Outline's Line Thickness.)

b. Set the Special Effect property to Shadowed.

Switch to Form View and test the form, and then save and close it.

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.

Assignment_NP_Access2013_T3_P1a

New PerspectivesAccess 2013

Tutorial 3: SAM Project 1a

PHYSICAL THERAPY SPECIALISTS, P.C.

USING QUERIES TO UPDATE AND RETRIEVE INFORMATIONName

PROJECT DESCRIPTION

Jennifer Christie needs to update some of the data in the Therapist and Location tables to reflect a contract change and the closure of a business location for Physical Therapy Specialists. She also needs to view specific data about patients, therapists, and billing. She asks you to help her maintain the data in the database and to create query objects to view the data she needs to review.

GETTING STARTED

· Download the following file from the SAM website:

o NP_Access2013_T3_P1a_FirstLastName_1.accdb

· Open the file you just downloaded and save it with the name:

o NP_Access2013_T3_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 _GradingInfoTabletable 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

1. Open the Therapist table in Datasheet view. Display the subdatasheet for the record with TherapistID 699, and then update the record with BillingID A84975 to include 3 sessions and an amount of $225. Close the Therapist table.

2. Open the Location table in Datasheet view. Change the font size for the datasheet to 12 pt.

3. Select and resize the Address column in the Location table datasheet to best fit the data it contains.

4. Delete the record with LocationID C from the Location table. Save and close the Location table.

5. Create a new query in Design view and based on the Patient table. Add the FirstName, LastName, BirthDate, and Gender fields, in that order, to the query design. Save the query as PatientBirthdays, and then run it.

6. In the PatientBirthdays datasheet, use Filter By Selection to select only those records for patients with an October birthday. (Tip: Select 10 in one of the records in the PatientBirthdays field and then select the Begins with 10 option in the Selection menu.) Redisplay all records in the datasheet, but do not clear the filter you just applied. Save and close the PatientBirthdays query.

7. Create a new query in Design view that is based on the Patient, Billing, and Therapist tables. Save the query as PatientsAndTherapists, and then do the following:

a. Add the LastName field from the Patient table to the query.

b. Add the LastName field from the Therapist table to the query.

c. Add the StartDate, EndDate, Sessions, and Amount fields, in that order, from the Billing table to the query.

d. Save and run the query, and then close it.

8. Use the Navigation Pane to copy the PatientsAndTherapists query, and then paste it as PatientsAndTherapistsMarchStart. Modify the PatientsAndTherapistsMarchStart query by adding a condition to the StartDate field to select records with contracts that begin on or after March 1,2016. Set the StartDate field so it does not appear in the query results, but remains in the query design. Save and run the query, and then close the query.

9. In the Navigation Pane, copy the PatientsAndTherapistsMarchStart query, rename the copied query as PatientsAndTherapistsMarch, and then do the following:

a. Add a new condition to the query to select records with contracts that start on or after March 1, 2016 and end on or before March 31, 2016.

b. Sort the records in ascending order by the StartDate field.

c. Change the StartDate field so it appears in the query results, and then move the StartDate field so it follows the LastName field in the query design.

d. Save and run the query, and then close it.

10. Create a new query in Design view that uses the Therapist table. Add the LastName, Specialty, Certification, HireDate, and Minors fields, in that order, to the query design. Add a condition to the Certification field to select records that contain the value MPT. Save the query as MPT, run it, and then close it.

11. In the Navigation Pane, copy the MPT query, rename the copied query as MPTOrMinors, and then add a new condition to the MPTOrMinors query to select a record with a Certification field that contains the value MPT or a record that indicates that the therapist accepts patients who are minors. Save and run the query, and then close it.

12. Create a new query in Design view that is based on the Patient and Billing tables. Save the query as PatientAmounts, and then do the following:

a. Add the LastName and FirstName fields from the Patient table to the query.

b. Add the Sessions and Amount fields from the Billing table to the query.

c. Save and run the query.

13. Add the Total row to the PatientAmounts datasheet, and then use a function to calculate the total number of sessions and the total of all contract amounts. Save and close the query.

14. Create a new query in Design view that is based on the Patient and Billing tables. Save the query as ContractDays, and then do the following:

a. Add the LastName field from the Patient table to the query.

b. Add the Sessions, StartDate, and EndDate fields from the Billing table, in that order, to the query.

c. In Design view, create a calculated field named NumberOfDays in the fifth column of the query design grid that determines the number of days in each contract, by creating an expression that subtracts the StartDate field value from the EndDate field value. Set the Caption property for the calculated field to Number of Days. (Hint: Refer to pages AC 157-159 in the textbook for help.)

d. Sort the values in the NumberOfDays field in descending order.

e. Save and run the query. Resize the Number of Days column to best fit the data it contains.

f. Save and close the query.

15. Create a new query in Design view that is based on the Billing and Therapist tables. Save the query as TherapistTotals, and then do the following:

a. Add the TherapistID and LastName fields from the Therapist table to the query.

b. Add the Sessions and Amount fields from the Billing table to the query.

c. For each therapist, use an aggregate function to calculate the total number of sessions and the total amount billed for those sessions. For these two columns, use the field names NumberOfSessions and TotalAmount, and the captions Number of Sessions and Total Amount, respectively.

d. Save and run the query. Resize the Number of Sessions and Total Amount columns to best fit the data they contain.

e. Save and close the query.

Compact and repair your database, save your database, and exit Access. Follow the directions on the SAM website to submit your completed project.

Homework, Assignments, Quizzes and Papers that will help you get A+ Score

Pages

NP_Access2013_T7_P1a_FirstnameLastname_2

PROJECT DESCRIPTION

GETTING STARTED

PROJECT STEPS

Figure 1: Last Page of rptEmployeesAndManagers Report

Figure 2: Prototype Label

Figure 3: First Page of rptBillingByTherapist Report

Copyright © 2014 Cengage Learning. All Rights Reserved.

Figure 5: Header Section of rptBillingByTherapist Report

Figure 6: Footer Section of rptBillingByTherapist Report

NP_Access2013_T6_P1a_FirstnameLastname_2

PROJECT DESCRIPTION

GETTING STARTED

PROJECT STEPS

Assignment_NP_Access2013_T3_P1a

PROJECT DESCRIPTION

GETTING STARTED

PROJECT STEPS