1. Open the Contract database, which is located in the Access2\Case1 folder provided;with your Data Files.;2. Change the?rst record in the tblStudent table datasheet so the First Name and Last;Name 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?eld;value begins with 541. Display the FirstName, LastName, City, and Phone?elds in;the query recordset, and sort in ascending order by LastName. Save the query as;qry541AreaCodes, 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?eld;and 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?eld;value 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 as;qryNonPortland, 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 by;FirstName. Add a calculated?eld named TeacherName as the second column that;concatenates FirstName, a space, and LastName for each teacher. Set the Caption;property for the TeacherName?eld to Teacher Name. Do not display the FirstName;and LastName?elds in the query recordset. Save the query as qryTeacherNames, run;the query, resize the Teacher Name column to its best?t, and then save and close;the 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 doesn?t enter a LessonType?eld value;select all records from the table. Include all?elds from the tblContract table in the;query recordset. Save the query as qryLessonTypeParameter. Run the query and;enter no value as the LessonType?eld value, and then run the query again and enter;Guitar as the LessonType?eld value. Close the query.;8. Create a crosstab query based on the tblContract table. Use the LessonType?eld;values 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 include;row sums. Save the query as qryLessonTypeCrosstab. Change the column heading;for 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 Number;of 60-Minute Lessons. Resize the columns in the query recordset to their best?t, and;then 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 as;qryMultipleLessonsForStudents, run the query, and then close it.;10. Create a?nd unmatched query that?nds all records in the tblStudent table;for which there is no matching record in the tblContract table. Display all;?elds from the tblStudent table in the query recordset. Save the query as;qryStudentsWithoutContracts, 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 the;lookup column. View the tblContract table datasheet, resize the Teacher ID column;to 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 tblStudent;table. The ending input mask should use periods as separators, as in XXX-XXX-XXXX;with only the last seven digits required, do not store the literal display characters, if;you are asked to do so. Update the Input Mask property everywhere the Phone?eld;is used. Resize the Phone column to its best?t, test the input mask by typing over an;existing 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.
Paper#71213 | Written in 18-Jul-2015Price : $27