PHS CyberSchool

Computer Studies SG 

Taxi Advertising Database - Maintaining the database 

The Advertising Sales manager of the Cape Town City Taxi Company would like to make some changes to the database created in Taxi Advertising Database - Basic and worked on in Taxi Advertising Database - Queries.  Another field must be added to the database, and the size of the First Name field must be increased.  Because several different individuals update the data, the manager would like to add some validation rules to the database.  Finally some additions and deletions are to be made to the database.

Use the Cape Town City Taxi Company database for this task.  Perform the following instructions:

1. Open the database and open the Advertiser table in design view.  
   1. Create an index for the Name field.  Be sure to allow duplicates. 
   2. Create an index on the combination of the Province and Postal Code fields.  Name the index Provincepostal.  Save the changes.
   3. Change the field width of the Name field to 30.
   4. Change the name of the account CS46 to Supa Quick Tyres and Shocks.
   5. Add the field Ad Type to the Advertiser table.  
      1. Insert the Ad Type field after the Postal Code field.  This field will contain data on the type of advertising account.  
      2. Define the field as text with a width of 3.  
      3. Advertisers are classified as retail (RET), service (SER) and dining (DIN).  Using a query, change the entries in the Ad Type column to RET.  This will be the type of most accounts.    (Update query)
      4. Display and save the query.
   6. Create and save the following validation rules for the Advertiser table.
      1. Make the Name field a required field. 
      2. Specify the legal values RET, SER and DIN for the Ad Type field.  Include validation text. 

       

      3. Ensure that any letters entered in the Advertiser Id, Province and Ad Type fields are displayed as uppercase. 
      4. Specify that the Balance must be less than or equal R450.00.  Include validation text. 

       

      5. Save the changes.

2. Update the Advertiser form to include the new Ad Type field.  Change the tab order as required.
3. Open the Advertiser form or table, and then add the following record to the table: 

5. Use Filter by Form to find all the records in the table where the account has the Ad Type of RET and the Postal Code of 7785.  Delete these records.
6. Print the Advertiser table on ONE sheet.
7. Join the Sales Rep table and the Advertiser tables using Enforced Referential Integrity between the Sales Rep table (the one table) and the Advertiser table (the many table).  Print the Relationships windows by making sure the Relationships window is open, clicking File on the menu bar, and then clicking Print Relationships.

1. Save the database for future tasks.
2. 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.