Microsoft Access 2010 Chapter 2 Lab 1: Querying the ECO Clothesline Database

Microsoft Access 2010 Chapter 2 Lab 1: Querying the ECO Clothesline Database

Problem: The management of ECO Clothesline has determined a number of questions it wants the database management system to answer. You must obtain answers to the questions posed by management.
Instructions: Use the database modified in the In the Lab 1 of Chapter 1 on page AC 66 for this assignment, or see your instructor for information on accessing the files required for this book.
Perform the following tasks:
1. Open the ECO Clothesline database and create a new query for the Customer table that includes the Customer Number, Customer Name, Amount Paid, and Sales Rep Number fields in the design grid for all customers where the sales rep number is 49. Save the query as Lab 2-1 Step 1 Query.
2. Create a query that includes the Customer Number, Customer Name, and Amount Paid fields for all customers located in Virginia (VA) with a paid amount greater than $1,000.00. Save the query as Lab 2-1 Step 2 Query.
3. Create a query that includes the Customer Number, Customer Name, Street, and City fields for all customers whose names begin with T. Save the query as Lab 2-1 Step 3 Query.
4. Create a query that lists all cities in ascending order. Each city should appear only once. Save the query as Lab 2-1 Step 4 Query.
5. Create a query that allows the user to enter the city to search when the query is run. The query results should display the Customer Number, Customer Name, Balance, and Amount Paid fields. Test the query by searching for those records where the client is located in Ashton. Save the query
as Lab 2-1 Step 5 Query.
6. Include the Customer Number, Customer Name, and Balance fields in the design grid. Sort the records in descending order by the Balance field. Display only the top 25 percent of the records in the query result. Save the query as Lab 2-1 Step 6 Query.
7. Join the Sales Rep and the Customer table. Include the Sales Rep Number, First Name, and Last Name fields from the Sales Rep table. Include the Customer Number, Customer Name, and Balance from the Customer table. Sort the records in ascending order by sales rep’s last name and customer name. All sales reps should appear in the result even if they currently have no customers. Save the query as Lab 2-1 Step 7 Query.
8. Open the Lab 2-1 Step 7 Query in Design view and remove the Sales Rep table. Add the Amount Paid field to the design grid. Calculate the total of the balance and amount paid amounts. Assign the alias Total Amount to the calculated fi eld. Change the caption for the Balance field to Due. Save the query as Lab 2-1 Step 8 Query.
9. Create a query to display the average balance amount for all customers. Save the query as Lab 2-1 Step 9 Query.
10. Create a query to display the average balance amount for sales rep 51. Save the query as Lab 2-1 Step 10 Query.
11. Create a query to display the average balance amount for each sales rep. Save the query as Lab 2-1 Step 11 Query.
12. Create the crosstab shown in Figure 2 – 92. The crosstab groups the total of customers amount paid amounts by state and sales rep number. Save the crosstab as State-Sales Rep Crosstab.
13. Submit the revised database in the format specified by your instructor.