Solved by Expert Tutors
Solved by Expert Tutors
Question

# Hi, you helped me with my Access homework on friday, you could not

upload my access file back to me, is there anyway you could please do this Access homework also continuing from the Access file you couldn't upload back to me?

Problem Solving #6 Complex Queries in Access 1. PS6-Q1 On Your Own Run an aggregation query to show how our sales people are performing (total sales by rep) by territory. a. For each salesperson, show their ID, territory name, total sales revenue, commission % ( average ) and expected bonus ( average ). b. Sort by sales volume (high to low) c. Save as PS6-Q1 <Paste results from PS6-Q1 here> 2. PS6-Q2 (a&b) Nested Query (Query within a Query) The question we wish to answer is what percent of Territory 4’s total sales, are accounted for by one customer, #403. We need to use 2 queries, one nested within the other, to answer this. The reason is that when you run a query, it can only pass through the data a single time from the top of the data to the bottom. In this case we need two passes; the first pass finds the sum of total sales of customer #403. The second pass calculates the total of Territory 4 sales and calculates the percentage that #403 sales are of the territory total. PS6-Q2a Customer 403 total sales Design a new aggregated query, save it as PS6-Q2a, to sum total sales for customer #403 (Use Sum LineTotal, and give it the alias “C_403_total_sales”. Save it as PS6-Q2a. <Paste results from PS6-Q2a here> PS6-Q2b Territory total sales Design a second new, also aggregated, query. This one will calculate territory total sales and the customer 403’s percentage of territory 4 sales. a. This query will start with tables Sales_SalesOrderHeader and SalesOrderDetail. b. In addition, drag in the query PS6-Q2a as a third table. c. Select the fields TerritoryID, (Group By) and criteria, followed by Sum of LineTotal (called Territory_Sales”) then “C_403_total_sales” (Group By) d. Finally, calculate customer 403 sales divided by territory sales to determine the “Percent_Of_Territory_Sales (make this an Expression). e. Save this table as PS6-Q2b. f. Run the query. Make sure that the results make sense, and that all are properly formatted. <Paste results from PS6-Q2b here> Last Updated: 11/03/2014 2
Show entire document

sum
ce
ipsum dolor sit amet, conse

Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce

gue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, conse et, consectetur nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a inia pulvina

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents