Microsoft Office Tutorials and References
In Depth Information
5. 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 TeacherID 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
987.654.3210 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. Test the input mask by typing over an existing Phone field value,
being sure not to change the value permanently by pressing the Esc key after you
type the last digit in the Phone field.