Data File needed for this Case Problem: Pinehill.accdb (cont. from Tutorial 2) Pine Hi...

Data File needed for this Case Problem: Pinehill.accdb (cont. from Tutorial 2)

Pine Hill Music School After reviewing the Pinehill database, Yuka Koyama wants to modify some records and then view specific information about the students, teachers, and contracts for her music school. She asks you to update and then query the Pinehill database to perform these tasks. Complete the following:

1. Open the Pinehill database located in the Access1\Case1 folder, and then click the Enable Content button next to the Security Warning, if necessary.

2. In the Teacher table, change the following information for the record with TeacherID 55-5310: Degree is BM and Hire Date is 3/12/2012. Close the table.

3. In the Student table, find the record with the StudentID HAV7535, and then delete the related record in the subdatasheet for this student. Delete the record for StudentID HAV7535, and then close the Student table.

4. Create a query based on the Student table that includes the LastName, FirstName, and MobilePhone fields, in that order. Save the query as StudentPhoneList, and then run the query.

5. In the results of the StudentPhoneList query, change the mobile phone number for Andrea Barreau to 503-579-2277. Close the query.

6. Use Design view to create a query based on the Teacher and Contract tables. Display the LastName field from the Teacher table, and the StudentID, EndDate, LessonType, LessonLength, and LessonCost fields, in that order, from the Contract table. Sort in ascending order first on the teacher’s last name, and then in ascending order by the StudentID. Save the query as LessonsByTeacher, and then run it.

7. Display Backstage view, and then save the LessonsByTeacher query as CurrentLessons.

8. Modify the CurrentLessons query to display all contracts that end on or after 7/1/2013. Save your changes, and then run the query.

9. Display Backstage view, and then save the CurrentLessons query as CurrentGuitarLessons.

10. Modify the CurrentGuitarLessons query to display only those records for guitar lesson contracts that end on or after 7/1/2013. Do not include the LessonType fi eld values in the query results. Run and save the query.

11. In the CurrentGuitarLessons query datasheet, calculate the total monthly amount for current guitar lessons.

12. Change the alternate row color in the CurrentGuitarLessons query datasheet to the Theme Color named Purple, Accent 4, Lighter 60% (third row, eight column), and then change the font size to 12. Resize all columns in the datasheet to fi t the data, and then save and close the query.

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