PHS CyberSchool

Computer Studies SG 

School Supporter's Database - Enhancing the database

The Supporter's Club has found that the database needs to maintain additional data on suppliers.  They need to know the last date when they placed an order with a supplier.  They would also like to store some notes about each supplier's return policy as well as store the URL of each supplier.  The club requires a form that displays information about the supplier as well as the products the supplier sells.  The database needs to be compacted so that the space used by deleted objects is made available.

Open the database file School Stock.you created in School Supporter's Database - Basic, and worked on in School Supporter's Database - Queries and School Supporter's Database - Maintaining the database.  Perform the following tasks:

1. In design view add the fields Last Order Date, Notes and Web Page to the Supplier table structure.

2. Add the data shown below to the Supplier table.  Adjust the row and column spacing for the table.  Add a screen tip to each web page URL. (Use your imagination.)   Print the table on one page.  

    

   Supplier Code	Last Order Date	Notes	Web Page
   AG	5/20/2001	Can return only those items ordered for the first time.  Charges a fee.	www.aheadgear.co.za
   EP	06/17/2001	Can return all unsold merchandise. No extra charges.	www.execpromgifts.com
   YourInitials	08/24/2001	Can return all unsold merchandise.  Charges a fee.	www.youdecide.co.za

 



 

3. Query the Supplier table to show all suppliers that allow all unsold merchandise to be returned.  Include the Supplier Code and Name in the query.  Save and print the query.  (You must decide what field to query and the criteria.)
4. Create a report called Supplier Report YourInitials with Name/Address and Notes as illustrated below.  In design view place Address below the Name in the Detail section.  Change the label accordingly.  Save and print the report.

5. Create the Supplier Master Form YourInitials as illustrated below.  
   1. First make sure you have the Supplier form with all the fields laid out as illustrated below.
   2. Then make sure you have the Items form created in datasheet design.  
   3. Check that a relationship with enforced referential integrity was created between the two tables.
   4. Then put the two forms together with Supplier as the main form and Item as the subform to create the new form.
   5. Use Supplier Master Form YourInitials as the name of the form and Items of Supplier as the name of the subform.  
   6. Print the form of your firm.  To print the form, open the form, click File on the menu bar, click Print, and then click Selected Record(s) as the Print Range.

6. Close the database.  Check the file size of the database.  Open the database and compact it.  Save and close.  Check the file size of the database again.
7. Save the database for future tasks.

(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.