Microsoft Office Tutorials and References
In Depth Information
Access Chapter 3 Maintaining a Database
In the Lab continued
6. Create the following validation rules for the Client table and save the changes.
a. Specify the legal values MAN, RET, and SER for the Client Type ﬁ eld. Include validation text.
b. Assign a default value of RET to the Client Type ﬁ eld.
c. Ensure that any letters entered in the Client Number ﬁ eld appear as uppercase.
d. Specify that the billed amount must be less than or equal to $1,500.00. Include validation text.
7. Open the Client table and use Filter By Form to ﬁ nd all records where the client is located in
Anderson and has a client type of SER. Delete these records.
8. JMS has signed up a new retail store, Cray Meat Market (Client Number CR21) and needs to add
the record to the Client table. The Meat Market is at 72 Main in Anderson, TX 78077. The phone
number is 512-555-7766. Lee Torres is the technician assigned to the account. To date, they have
not been billed for any services. Create a split form for the Client table and use this split form to
add the record.
9. Specify referential integrity between the Technician table (the one table) and the Client table (the
many table). Cascade the update but not the delete.
10. Compact the database and then back up the database.
11. Submit the revised database in the format speciﬁ ed by your instructor.
In the Lab
Lab 2: Maintaining the Hockey Fan Zone Database
Problem: The management of the Hockey Fan Zone store needs to change the database structure, add
validation rules, and update records.
Instructions: Use the database created in the In the Lab 2 of Chapter 1 on page AC 68 for this
assignment, or see your instructor for information on accessing the ﬁ les required for this topic.
Perform the following tasks:
1. Open the Hockey Fan Zone database and then open the Item table in Design view.
2. Add a Lookup ﬁ eld, Item Type to the Item table. The ﬁ eld should appear after the Description
ﬁ eld. The ﬁ eld will contain data on the type of item for sale. The item types are CAP (caps and
hats), CLO (clothing), and NOV (Novelties).
3. Make the following changes to the Item table:
a. Change the ﬁ eld size for the On Hand ﬁ eld to Integer. The Format should be ﬁ xed and the
decimal places should be 0.
b. Make Description a required ﬁ eld.
c. Specify the legal values CAP, CLO, and NOV for the Item Type ﬁ eld. Include validation text.
d. Specify that number on hand must be between 0 and 75. Include validation text.
4. Save the changes to the table design. If a dialog box appears indicating that some data may be lost,
click the Yes button.
5. Using a query, assign the value NOV to the Item Type ﬁ eld for all records. Save the query as
6. Delete the split form for the Item table that you created in Chapter 1. The form does not include
the Item Type ﬁ eld. Recreate the split form for the Item table.