PHS CyberSchool

Computer Studies SG 

School Supporter's Database - Maintaining the database 

The Supporter's Club would like to make some changes to the database created in School Supporter's Database - Basic and queried in School Supporter's Database - Queries.  They need to increase the size of the Description field and add an additional index.  Because several different individuals update the data, the club also would like to add some validation rules to the database.  Finally some items must be added to the database.

Use the School Stock database for this task.  Perform the following instructions:

1. Open the School Stock database and open the Item table in design view. (Use the tip sheets for assistance.)
2. Create and save the following validation rules for the Item table.
   1. Make the Description field a required field. 
   2. Ensure that any lower case letters entered in the Item Id field are displayed as uppercase. 
   3. Specify that the On Hand units must be between 0 and 50.  Include validation text. 

   

   4. Save the changes.
3. Open the Item form, and then add the following record to the item table: 

5. Open the Item table in datasheet view and sort the records in ascending order by Description. Print the table. Close the table.  If you are asked to save changes to the design of the table, click the No button.
6. Create a new query for the Item table. (Use the tip sheets for assistance.)
   1. Using a query, delete all records in the Item table where the Description starts with the letter T.  Save the query with a meaningful name.  (Delete query)
   2. Print the Item table. 
7. Open the Supplier table in design view, and add a new field to the end of the table.  Name the field Fax Number.  This new field has the same data type and length as Telephone Number.  Save the change to the table design.
8. Open the Supplier table in datasheet view, and then add the following data to the Fax number field.

9. Resize the Supplier Code, City, Province, Postal Code, Telephone Number and Fax Number columns to the best size.
10. Print the table.  If necessary, change the margins so the table prints on one page in landscape orientation.  Save the changes to the layout of the table. 
11. Join the Supplier (the one table) and Item (the many table) tables with Referential Integrity.  Print the Relationship window by making sure the Relationships window is open, clicking File on the menu bar, and then clicking Print Relationships. 
12. Save the database for future tasks.
13. Consult the Access Help pages on Update queries, Delete queries, Validation rules, Referential Integrity, Subdatasheets and Indexes.

(based on Access 2000 by Shelly, G.B., Cashman, T.J. and Pratt, P.J. published by Course Technology in 2000 www.scsite.com )

Return to Computer Studies lesson plan index. This work may be freely copied for non-commercial use only. Copyright © Pam Miller.