PROJECT DESCRIPTION

Amy Rosario wants to streamline the data-entry process at PRN Nursing Services by creating forms that her staff can use to update and view data about contractors and client billing. She also wants to create a report to display client billing data. You’ll create two forms and a report to help Amy with her requests.

GETTING STARTED

• Download the following file from the SAM website:
  • NP_Access2013_T4_P1b_FirstLastName_accdb
• Open the file you just downloaded and save it with the name:
  • NP_Access2013_T4_P1b_FirstLastNameaccdb
  • 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.
• To complete this Project, you will also need to download and save the following support file from the SAM website:
  • jpg
• 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

1. Use the Form tool to create a form based on the Staff table, and then save the form as StaffList.
2. In Layout View, make the following changes to the StaffList form:
   1. Remove the form title from the control layout, and then change the form title to Staff List.
   2. Apply the Office theme to the StaffList form only.
   3. Save the form.

3. In Form View, use the StaffList form to navigate to the third record (with Staff ID 908), and then change the Staff Last field value to Johnston. Navigate to the last record (with Staff ID 999), and then change the Title field value to Billing Manager. Close the form.
4. Use the Form Wizard to create a form based on the Billing Include all fields in the form, use the Columnar layout, and specify the title BillingData.
5. In Form Layout View, change the form title of the BillingData form to Billing Data(two words). Change the font color of the form title to Dark Blue, Text 2 (1^st row, 4^thcolumn in the Theme Colors section of the Font Color gallery).
6. Insert the logo jpg, available for download from the SAM website, into the Form Header. Remove the picture from the control layout, and then move the picture to the right of the form title. Save the form.
7. Switch to Form View and use the Find command in the BillingData form to find the record with Billing ID 2044. Change the total hours to 30 and the hourly rate to $20.00.
8. Add a new record to the Billing table using the BillingData form, add the data shown in Figure 1 below, and then close the form.

Figure 1: BillingData Form

9. Use the Form Wizard to create a form containing a main form and a subform as follows:
   1. Select all fields from the Contractor table for the main form.
   2. Select the ClientID, StartDate, and EndDate fields from the Billing table for the subform.
   3. Choose the option to view the data by contractor.
   4. Select the Datasheet layout for the subform.
   5. Specify the titles ContractorsAndBilling for the main form and BillingSubform for the subform.
10. In Form Layout View, change the title in the main form to Contractors and Billing, and then change the font color of the title to Dark Blue, Text 2 (1^st row, 4^th column in the Theme Colors section of the Font Color gallery).
11. Resize the Client ID, Start Date, and End Date columns in the subform to best fit the data they contain. Resize the width of the subform as shown in Figure 2

Figure 2: ContractorsAndBilling Form

12. Use the ContractorsAndBilling form (in Form View) and the Find command to find the record with Contractor ID 1032, and then change the Credentials field value to MA. Save and close the form.

13. Use the Report Wizard to create a report based on the primary Client table and the related Billing table, as follows:
    1. Select the ClientID, ClientFirst, ClientLast, and ClientSince fields from the Client
    2. Select all fields from the Billing table except for the ClientID
    3. Do not select any additional grouping levels for the report.
    4. Sort the details records in ascending order by BillingID.
    5. Select the Outline layout and the Landscape orientation for the report.
    6. Specify the report title ClientBilling. (Hint: Do not include the period.)
14. In Report Layout View, apply the Office theme to the ClientBilling report only. Change the report title for the ClientBilling report to Client Billing (two words).
15. Resize and reposition the following objects in the ClientBilling report in Layout View, and then scroll through the report to make sure all field labels and field values are fully displayed:
    1. Resize the Client ID, Client First, Client Last, and Client Since field label boxes on their right sides, decreasing their widths so that the field label boxes are as wide as the values they contain.
    2. Move the Client ID, Client First, Client Last, and Client Since field value boxes to the left, so they are closer to their field labels, to decrease the amount of white space between the field label and the field value boxes for each field.
    3. Resize the Client Since field value box on its right side, decreasing its width so that the field value box is as wide as the values it contains.
    4. Resize the Hourly Rate field label box and the Hourly Rate field value box on their right sides to decrease their widths to approximately 1?.
    5. Scroll to the bottom of the report, and then resize the control that contains the page number on its right side to decrease its width so that it is only as wide as the “Page 1 of 1” value it contains. Use an arrow key to move the page number control to the left so that its right edge aligns with the right edge of the Hourly Rate field in the report.
    6. Save the report.
16. Use conditional formatting in the ClientBilling report to format amounts in the Total Hours column that are less than or equal to 40 in a bold, Dark Blue (1^st row, 4^thcolumn in the Standard Color Palette) font, as shown in Figure 3 on the following page. Display the report in Print Preview and review its pages, and then save and close the report.

Figure 3: ClientBilling 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.

Need assistance with this?

CLICK BUTTON TO ORDER NOW