Microsoft Office Tutorials and References
In Depth Information
11. In the tblProduct table, change the CompanyID field to a lookup field. Select the
CompanyName and CompanyID fields from the tblCompany table, sort in ascending
order by the CompanyName field, do not hide the key column, make sure the
Company Name column is the leftmost column, resize the lookup columns to their
best fit, select CompanyID as the field to store in the table, and accept the default
label for the lookup column. View the tblProduct table datasheet, resize the Com-
pany ID column to its best fit, test the lookup field without changing a value perma-
nently, and then save and close the table.
12. Use the Input Mask Wizard to add an input mask to the Phone field in the
tblCompany 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 by pressing the Esc key after you type the last
digit in the Phone field.
13. Add a Memo field named CompanyComments as the last field in the tblCompany
table. Set the Caption property to Company Comments and the Text Format property
to Rich Text. In the table datasheet, resize the new column to its best fit, and then
add your city and state in bold, italic font to the Memo field in the first record. Save
and close the tblCompany datasheet.
14. Designate the Level.02\Review folder as a trusted folder. ( Note: Check with your
instructor before adding a new trusted location.)
15. Close the Products database without exiting Access, make a backup copy of the
database, open the Products database, compact and repair the database, close the
database, and then exit Access.
| Case Problem 1
Use the skills you
learned in the tutorial
to work with the data
contained in a data-
base for a small music
Data File needed for this Case Problem: Contract.accdb
Pine Hill Music School Yuka Koyama owns and runs the Pine Hill Music School in
Portland, Oregon. She and the qualified teachers who work for her offer instruction in
voice, violin, cello, guitar, percussion, and other instruments. Yuka created an Access
database named Contract to store data about students, teachers, and contracts. You’ll
help Yuka create several new queries and make design changes to the tables. Complete
1. Open the Contract database, which is located in the Level.02\Case1 folder pro-
vided 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.