- New Perspectives Access 2010
- Tutorial 3 - Case Problem 1
- Pine Hill Music School
- 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)
- 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.
a guest Sep 22nd, 2011 2,734 Never
RAW Paste Data