Data Files needed for this Case Problem: Pinehill.accdb (cont. from Tutorial 3) and Pi...

Data Files needed for this Case Problem:

Pinehill.accdb (cont. from Tutorial 3) and Piano.bmp Pine Hill Music School Yuka Koyama wants to use the Pinehill database to track and view information about the classes her music school offers. She asks you to create the necessary forms and a report to help her manage this data. Complete the following:

1. Open the Pinehill database located in the Access1\Case1 folder provided with your Data Files.

2. Use the Form Wizard to create a form based on the Student table. Select all the fields for the form and the Columnar layout. Specify the title StudentData for the form.

3. Apply the Clarity theme to the StudentData form only.

4. Edit the form title so that it appears as “Student Data” (two words), and change the font color of the form title to the Blue-Gray, Accent 4, Lighter 40% theme color.

5. Use the Find command to display the record for Jeff Tealey, and then change the Address field value for this record to 304 Forest Ave.

6. Use the StudentData form to add a new record to the Student table with the following field values: Student ID: NEL7584

First Name: Kayla

Last Name: Nelson

Address: 15540 Belleview Dr

City: Portland

State: OR

Zip: 97229

Home Phone: 541-563-3156

Mobile Phone: 541-563-8882

Birth Date: 10/13/2003

Gender: F

7. Save and close the StudentData form.

8. Use the Form Wizard to create a form containing a main form and a subform. Select all the fi elds from the Teacher table for the main form, and select the ContractID, StudentID, and LessonType fi elds from the Contract table for the subform. Use the Datasheet layout. Specify the title ContractsByTeacher for the main form and the title ContractSubform for the subform.

9. Change the form title text for the main form to Contracts by Teacher.

10. Change the line type for the field value boxes—except the Takes Beginners check box—to Dots.

11. Resize all columns in the subform to their best fi t, and then move through all the records in the main form and check to make sure that all subform field values are fully displayed, resizing the columns as necessary.

12. Save and close the ContractsByTeacher form.

13. Use the Report Wizard to create a report based on the primary Student table and the related Contract table. Select the StudentID, FirstName, LastName, and HomePhone fi elds from the Student table, and select all fields from the Contract table except StudentID, StartDate, and EndDate. Do not select any additional grouping levels, and sort the detail records in ascending order by ContractID. Choose the Outline layout and Landscape orientation. Specify the title StudentContracts for the report.

14. Apply the Clarity theme to the StudentContracts report only.

15. Resize the report title so that the text is fully displayed; edit the report title so that it appears as “Student Contracts” (two words); and change the font color of the title to the Blue-Gray, Accent 4, Lighter 40% theme color. 16. Resize the following controls in the report in Layout view, and then scroll through the report to make sure all field values are fully displayed:

a. Resize the Contract ID field label on its left side, increasing its width so the label is fully displayed.

b. Resize the Lesson Length field label on its left side, increasing its width so the label is fully displayed.

c. Scroll to the bottom of the report and check to make sure the page number is completely within the page border. If necessary, resize the control on its right side, reducing its width so that the page number text is roughly right-aligned with the text in the report.

17. Insert the Piano picture, which is located in the Access1\Case1 folder provided with your Data Files, in the report. Move the picture to the right of the report title.

18. Apply conditional formatting so that any LessonCost field value greater than 200 appears as bold and with the Maroon 5 color applied.

19. Preview the entire report to confirm that it is formatted correctly. If necessary, return to Layout view and make changes so that all field labels and field values are completely displayed. (Hint: You might need to resize the Teacher ID field label so it is fully displayed; if so, resize the Contract ID field label to the left more, to make room for resizing the Teacher ID field label.) When you are finished, save the report, print its first page (only if asked by your instructor to do so), and then close the report.

20. Compact and repair the Pinehill database, and then close it.