Microsoft Access 2010 Chapter 3 Lab 1 Maintaining the ECO Clothesline Database
Problem: ECO Clothesline is expanding rapidly and needs to make some database changes to handle the expansion. The company needs to know more about its customers, such as the type of business, and it needs to ensure that data that is entered is valid. It also needs to update the records in the database.
Use the database modified in the In the Lab 1 of Chapter 2 on page AC 132 for this assignment, or see your instructor for information on accessing the files required for this book.
Perform the following tasks:
1. Open the ECO Clothesline database and then open the Customer table in Design view.
2. Add a Lookup field, Customer Type, to the Customer table. The field should appear after the Postal Code field. The field will contain data on the type of customer. The customer types are SAL (Salons, Studios, Fitness Clubs), SPG (Sporting Goods), and WMN (Women’s Wear). Save these changes to the structure.
3. Using a query, change all the entries in the Customer Type column to WMN. Save the query as Customer Type Update Query.
4. Open the Customer table and make the following changes. You can use either the Find button or Filter By Selection to locate the records to change:
a. Change the customer type for customers CY12, FN19, LB20, RD03, TT21, and TW56 to SAL.
b. Change the customer type for customer WS34 to SPG.
c. Change the name of customer LB20 to Le Beauty Salon & Spa.
5. Change the alternate background color on the datasheet to white and remove the vertical gridlines. Save the changes to the layout of the table.
6. Create the following validation rules for the Customer table and save the changes.
a. Specify the legal values SAL, SPT, and WMN for the Customer Type field. Include validation text.
b. Assign a default value of WMN to the Customer Type field.
c. Ensure that any letters entered in the Customer Number field appear as uppercase.
d. Make Customer Name a required field.
7. Open the Customer table and use Filter By Form to find all records where the customer is located in Lowton and has a customer type of WMN, and then delete these records.
8. ECO Clothesline has signed up a sporting goods store, Ralph’s (Customer Number RA21) and needs to add the record to the Customer table. Ralph’s is at 72 Main in Lowton, TN 37084. Terry Sinson is the sales rep assigned to the account. To date, Ralph’s has not been billed nor does the company owe for any services. Create a split form for the Customer table and use this split form to add the record. Save the split form as Customer Split Form.
9. Specify referential integrity between the Sales Rep table (the one table) and the Customer table (the many table). Cascade the update but not the delete.
10. Add the Customer Type field to the Customer Balance Report. The field should follow the Customer Name field. Save the changes to the report.
11. Submit the revised database in the format specified by your instructor.