PHS CyberSchool

Computer Studies SG 

Taxi Advertising Database - Queries 

The advertising sales manager has determined a number of questions that he wants the database management system to answer.  You must obtain answers to the questions posed by the manager.  Open the database file Cape Town City Taxi Company you created in Taxi Advertising Database - Basic.

Simple and parameter queries

Create the  following queries for the Advertiser table.  (Use the tip sheets for assistance.)  Give each query a clear name such as c-City begins with P. 

1. Display and save the Advertiser Id, Name, Balance and Amount Paid fields for all the records in the table.  (Simple query)
2. Display and save the Name and Balance fields for all the advertisers where the Balance is greater than R200.  (Simple query)
3. Display and save the Name and City fields for all the advertisers where the City begins with the letter P.  (Simple query)
4. Display and save the Name and Sales Rep Number fields for all the advertisers where the Sales Rep Number is 24.  (Simple query)
5. Display and save only the Name and Balance fields for all the advertisers where the Sales Rep Number is 29 AND the Amount Paid is greater than R1000.  Do NOT show the Sales Rep Number and Amount Paid fields.  (Simple query)
6. Display and save only the Name and Balance fields for all the advertisers where the Sales Rep Number is 29 OR the Balance is less than R50.  Do NOT show the Sales Rep Number field.  (Simple query)
7. Display the Name and Balance fields for a parameter query where Balance is less than a certain amount.  (Parameter query)

8. Display only the Advertiser ID, Name and Amount Paid for a parameter query where the Amount Paid is more than a certain amount.  (Parameter query)

Sorting

1. Include only the Name, Postal Code and City fields in the design grid of the query.  Sort the records in ascending order by City within Postal Code.  Display and print the results.  The Postal Code field should display in the result to the left of the City field.  

2. Display and save just the Cities in ascending order.  Each City should display only once.  (Unique values)

Display and save the following statistics:  

1. Total Balance for all advertisers
2. Total Amount Paid for all advertisers
3. Total Balance and Total Amount Paid for all advertisers
4. Total Balance for advertisers of Sales Rep 29
5. Total Amount Paid for each Sales Rep

Different kinds of calculations in queries, and joining tables

Display and save print the First Name, Last Name and Comm Rate fields from the Sales Rep table, and Name and Balance fields from the Advertiser table.  

1. Calculate the Pending Commission [Balance]*[Comm Rate] for the Sales Rep table.  
2. Sort the records in ascending order by Last Name.
3. Format Pending Commission as currency.

1. Restrict the records retrieved above to only those whose advertisers that are in Table View.  Display and save.

1. Save the database for future tasks.
2. Consult the Access Help pages on Queries, then look at all the pages on Simple select query and Select query, and Parameter queries.
3. Consult the Access Help pages on Calculate, then look at Perform calculations in a query.  Examine all the pages referred to.

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