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.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more