New Perspectives Access 2010
Tutorial 3 - Case Problem 1
Pine Hill Music School
Skills
Save a database with a new filename
Edit data in a table datasheet
Find data
Delete a record from a subdatasheet
Delete a record from a datasheet
Create a query using the Simple Query Wizard
Run a query
Edit data in a query datasheet
Create a query in Design view
Sort a query on multiple fields in Design view
Save a copy of a query as a new query
Use a comparison operator in a query
Use text criteria in a query
Add a Total row to a datasheet
Format a query datasheet
Project overview
After reviewing the Pine Hill Music School 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 database to perform these tasks.
STUDENT start FILE
NP_Access2010_T3_CP1a_FirstLastName_1.accdb (Note: Download your personalized start file from www.cengage.com/sam2010)

Instructions
1. Open the file NP_Access2010_T3_CP1a_FirstLastName_1.accdb and save the file as NP_Access2010_T3_CP1a_FirstLastName_2.accdb by clicking the "Save Database As" command in Backstage view. (Note: Verify that your name appears in the first record in the _SAMProjects table before you begin. If it does not, then please download a new copy of the start file from the SAM Web site.)

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 Student ID HAV7535, and then delete the related record in the subdatasheet for this student. Delete the record for Student ID 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 run the query to confirm it works.

5. In the results of the StudentPhoneList query, change the 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 run the query to confirm it works.

7. Display Backstage view, and then save the LessonsByTeacher query as CurrentLessons. Modify the CurrentLessons query to only display contracts that end on or after 7/1/2013. This should be done by adding a single criteria using the >= operator. Save your changes to the CurrentLessons query, and then run the query to confirm it works.

8. Display Backstage view, and then save the CurrentLessons query as CurrentGuitarLessons. Modify the CurrentGuitarLessons query criteria to display only those records for Guitar lesson contracts that end on or after 7/1/2013. Run the query to confirm it works, and then save the query.

9. Modify the CurrentGuitarLessons query so that it does not show the lesson type.

10. In the CurrentGuitarLessons query datasheet, display the Total row and show the total amount for the lesson monthly cost.

11. Change the alternate row color in the CurrentGuitarLessons query datasheet to the Theme Color named Purple, Accent 4, Lighter 60% (third row, eighth column), and change the font size to 12. Save and close the query.

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