PHS CyberSchool

Computer Studies SG 

Taxi Advertising Database - Enhancing the database

The Cape Town City Taxi Company database needs to maintain additional data on advertising sales representatives.  The company needs to maintain the date a sales rep started as well as some notes concerning the representative's abilities.  They would also like to store a picture of the representative as well as a link to each representative's web page.  The company wants you to create a form that displays advertising sales representative information and the advertisers for which they are responsible.  The database needs to be compacted so that the space used by deleted objects is made available.

Open the Cape Town City Taxi Company database created in Taxi Advertising Database - Basic, and worked on in Taxi Advertising Database - Queries.and Taxi Advertising Database - Maintaining the database.   Perform the following tasks:

1. In design view add the Start Date, Notes, Picture and Web Page fields to the Sales Rep table structure.

2. Add the data shown below to the Sales Rep table.  Add suitable pictures and hyperlinks for each representative.  Adjust the row and column spacing for the table.  Print the table on one page.  Add a screen tip to each web page URL (Use your own imagination.)
   

   Sales Rep Number	Start Date	Notes	Picture	Web page
   24	05/20/2002	Excellent proofreader.	Add a suitable picture	www.youdecide.co.za
   29	06/17/2000	Also works as a freelance graphic artist.	Add a suitable picture	www.youdecide.co.za
   Your Age	08/24/1997	Works with non-profit organisations on public service advertising.	Add a suitable picture	www.youdecide.co.za

3. Query the Sales Rep table to find all the sales reps who also will work as freelance graphic artists.  Include the Sales Rep Number, Last Name and First Name in the query.  Display and save the query.  (You must decide what field to query and the criteria.)
4. Query the Sales Rep table to find all sales reps who started before 2001.  Include the Sales Rep Number, Last Name, First Name, Commission and Comm Rate in the query.  Display and save the query.
5. Create the Sales Rep Master Form YourInitials as shown below.  
   1. First make sure you have the Sales Rep form with all the fields laid out as illustrated below.
   2. Then make sure you have the Advertiser form created in datasheet design with only the fields illustrated below.  
   3. Check that a relationship with enforced referential integrity was created between the two tables.
   4. Then put the two forms together with Sales Rep as the main form and Advertiser as the subform to create the new form.
   5. Use Sales Rep Master Form as the name of the form and Advertiser Accounts as the name of the subform.  
6. Print a record of ALL your personal details (Sales Rep form) and ALL your accounts (Advertiser form) on ONE page, i.e. the Sales Rep Master Form for yourself, as illustraed below.
7. Save the database for future tasks.
8. 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.

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