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

1. Open the Contract database, which is located in the Access2\Case1 folder provided

 

with your Data Files.

 

2. Change the first record in the tblStudent table datasheet so the First Name and Last

 

Name columns contain your first and last names. Close the table.

 

3. Create a query to find all records in the tblStudent table in which the Phone field

 

value begins with 541. Display the FirstName, LastName, City, and Phone fields 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 field

 

and to include a list-of-values criterion that finds all records in which the LessonType

 

field value is Cello, Flute, or Violin. Save and run the query, and then close it.

 

Create a query to find all records in the tblStudent table in which the City field

 

value is not equal to Portland. Display the FirstName, LastName, City, and Phone

 

fields 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 fields,

 

and sorting in ascending order by LastName and then in ascending order by

 

FirstName. Add a calculated field named TeacherName as the second column that

 

concatenates FirstName, a space, and LastName for each teacher. Set the Caption

 

property for the TeacherName field to Teacher Name. Do not display the FirstName

 

and LastName fields in the query recordset. Save the query as qryTeacherNames, run

 

the query, resize the Teacher Name column to its best fit, and then save and close

 

the query.

 

7. Create a parameter query to select the tblContract table records for a LessonType

 

field value that the user specifies. If the user doesn’t enter a LessonType field value,

 

select all records from the table. Include all fields from the tblContract table in the

 

query recordset. Save the query as qryLessonTypeParameter. Run the query and

 

enter no value as the LessonType field value, and then run the query again and enter

 

Guitar as the LessonType field value. Close the query.

 

8. Create a crosstab query based on the tblContract table. Use the LessonType field

 

values for the row headings, the LessonLength field values for the column headings,

 

and the count of the ContractID field 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 fit, and

 

then save and close the query.

 

9. Create a find duplicates query based on the tblContract table. Select StudentID and LessonType as the fields that might contain duplicates, and select all other

 

fields in the table as additional fields in the query recordset. Save the query as

 

qryMultipleLessonsForStudents, run the query, and then close it.

 

10. Create a find unmatched query that finds all records in the tblStudent table

 

for which there is no matching record in the tblContract table. Display all

 

fields 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 field data type to Lookup Wizard.

 

Select the TeacherName and TeacherID fields from the qryTeacherNames query,

 

sort in ascending order by TeacherName, resize the lookup columns to their best fit,

 

select TeacherID as the field 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 fit, and then save and close the table.

 

12. Use the Input Mask Wizard to add an input mask to the Phone field 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 field

 

is used. Resize the Phone column to its best fit, test the input mask by typing over an

 

existing Phone field value, being sure not to change the value permanently by press-

 

ing the Esc key after you type the last digit in the Phone field.