New Perspective on microsoft access 2010 comprehensive tutoral 5 case problem 1mulung
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
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.
- 7 years ago
Purchase the answer to view it