CSCI A114 / INFO I111 Assignment Two First semester 2000-2001

Due date

At the end of lab 5.

Late policy

No late policy. You need to turn this on time.

Work policy

Working in groups is accepted but you need to turn in your own version of the assignment.

Task

Explained in detail below.

Grading

The AI will provide you with feedback in a week. Grades will be posted on-line.

Start Access and open the Sidewalk Scrapers database from your assignment 1.

Perform the following tasks:

1. Create a new query for the Customer table. Add the Customer Number, Name and Address fields, to the design grid. Restrict retrieval to only those records where the customer has an address on Secord. Run the query and check the results. Save the query as One.

2. Return to Design View and clear the grid. Add the Customer Number, Name, Telephone, and Balance fields to the design grid. Restrict retrieval to only those records where the balance is greater than $50. Run the query and check the results. Save the query as Two.

3. Return to Design view and clear the grid. Add the Customer Number, Name, Address, and Worker ID fields to the design grid. Restrict retrieval to only those records where the Worker ID is either 03 or 07. Run the query and check the results. Save the query as Three.

4. Join the Customer and Worker tables. Add the Customer Number, Name, and Worker ID fields from the Customer table and First Name and Last Name from the Worker table. Sort the records in ascending order by Worker ID. Run the query and check the results. Save the query as Four.

5. Write a query that lists all customers in the Sidewalk Scrapers database together with their current balance, last name and first name of their assigned worker. Save the query as Five.

6. Write a query that lists all customers that have a balance greater than $50.00 together with the full name of their assigned worker. Do not list the balance. Save the query as Six.

7. Write a query that lists all the workers and their cumulative balances. (A worker's cumulative balance is the sum of the balances of all the worker's customers). Save the query as Seven.

8. Eight: List the workers and their cumulative balances sorted by balance, in ascending order.

9. Nine: List the names of those workers whose average balance is greater than the average balance in the database.

10. Ten: List the names of those workers whose hourly rates are below average.

11. Eleven: List the average hourly rate for a worker.

12. Twelve: List the total balance (that takes into account all of the customer balances).