New Perspective on microsoft access 2010 comprehensive tutoral 5 case problem 1

New Perspective on microsoft access 2010 comprehensive tutoral 5 case problem 1

1. Open the Contract database, which is located in the Access2Case1 folder providedwith your Data Files.2. Change the ?¦rst record in the tblStudent table datasheet so the First Name and LastName columns contain your ?¦rst and last names. Close the table.3. Create a query to ?¦nd all records in the tblStudent table in which the Phone ?¦eldvalue begins with 541. Display the FirstName, LastName, City, and Phone ?¦elds inthe query recordset; and sort in ascending order by LastName. Save the query asqry541AreaCodes, run the query, and then close it.4. Make a copy of the qryCurrentLessons query using the new name qrySelectedLessons.Modify the new query to delete the existing condition for the ContractEndDate ?¦eldand to include a list-of-values criterion that ?¦nds all records in which the LessonType?¦eld value is Cello, Flute, or Violin. Save and run the query, and then close it.Create a query to ?¦nd all records in the tblStudent table in which the City ?¦eldvalue is not equal to Portland. Display the FirstName, LastName, City, and Phone?¦elds in the query recordset; and sort in ascending order by City. Save the query asqryNonPortland, run the query, and then close it.6. Create a query to display all records from the tblTeacher table, selecting all ?¦elds,and sorting in ascending order by LastName and then in ascending order byFirstName. Add a calculated ?¦eld named TeacherName as the second column thatconcatenates FirstName, a space, and LastName for each teacher. Set the Captionproperty for the TeacherName ?¦eld to Teacher Name. Do not display the FirstNameand LastName ?¦elds in the query recordset. Save the query as qryTeacherNames, runthe query, resize the Teacher Name column to its best ?¦t, and then save and closethe query.7. Create a parameter query to select the tblContract table records for a LessonType?¦eld value that the user speci?¦es. If the user doesnt enter a LessonType ?¦eld value,select all records from the table. Include all ?¦elds from the tblContract table in thequery recordset. Save the query as qryLessonTypeParameter. Run the query andenter no value as the LessonType ?¦eld value, and then run the query again and enterGuitar as the LessonType ?¦eld value. Close the query.8. Create a crosstab query based on the tblContract table. Use the LessonType ?¦eldvalues for the row headings, the LessonLength ?¦eld values for the column headings,and the count of the ContractID ?¦eld values as the summarized value, and includerow sums. Save the query as qryLessonTypeCrosstab. Change the column headingfor the row sum column to Total Number of Lessons, and change the column head-ings for the [LessonLength] columns to Number of 30-Minute Lessons and Numberof 60-Minute Lessons. Resize the columns in the query recordset to their best ?¦t, andthen save and close the query.9. Create a ?¦nd duplicates query based on the tblContract table. Select StudentID and LessonType as the ?¦elds that might contain duplicates, and select all other?¦elds in the table as additional ?¦elds in the query recordset. Save the query asqryMultipleLessonsForStudents, run the query, and then close it.10. Create a ?¦nd unmatched query that ?¦nds all records in the tblStudent tablefor which there is no matching record in the tblContract table. Display all?¦elds from the tblStudent table in the query recordset. Save the query asqryStudentsWithoutContracts, run the query, and then close it.11. In the tblContract table, change the TeacherID ?¦eld data type to Lookup Wizard.Select the TeacherName and TeacherID ?¦elds from the qryTeacherNames query,sort in ascending order by TeacherName, resize the lookup columns to their best ?¦t,select TeacherID as the ?¦eld to store in the table, and accept the default label for thelookup column. View the tblContract table datasheet, resize the Teacher ID columnto its best ?¦t, and then save and close the table.12. Use the Input Mask Wizard to add an input mask to the Phone ?¦eld in the tblStudenttable. The ending input mask should use periods as separators, as in XXX-XXX-XXXXwith only the last seven digits required; do not store the literal display characters, ifyou are asked to do so. Update the Input Mask property everywhere the Phone ?¦eldis used. Resize the Phone column to its best ?¦t, test the input mask by typing over anexisting Phone ?¦eld value, being sure not to change the value permanently by press-ing the Esc key after you type the last digit in the Phone ?¦eld.


Comments are closed.