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 ITM310 Problem Solving #6 – Complex Queries in Access & Simple Reports (25pts) This assignment is to be done individually. Using the AdventureWorks database and Microsoft Access , construct a query to answer each question. This assignment problem solving with Access queries, and requires you to choose your own tables. Review the table relationship tool in Access to refresh on what tables are available to you. Creating Your Answers Sheet to Turn In Follow these steps to complete & turn in this document. 1. Open both AdventureWorks.accdb and ProbSlvg6_ComplexQueries.doc . Access will present a security warning. It is ok to approve “Enable this content.” 2. Resave this Word document as ProbSlvg6_yourlastname.docx 3. For each question build Access queries to get your answers: a. Create and test your query in AdventureWorks.accdb b. When the query is working correctly, display the View Results. Highlight the results table by selecting by the top left corner (arrow symbol), then copy it to the Clipboard (Ctrl-C). c. Switch to ProbSlvg6_yourlastname.docx (Alt-Tab to switch). Place the cursor under the Exercise question, and Paste the results there (Ctrl-V). d. Save your query. ProbSlvg#6 Query Exercises – Begin after reviewing linked notes & Access podcasts Your internship supervisor has asked you to find the answers to several questions she needs for a weekly sales management report. Do your best to come up with the answers she needs by running queries on tables available to you from the database. Carefully consider which tables you need to work with to get the data you need by looking at tables with likely names & the fields they contain. Paste the Query Results into this document under the appropriate question & submit to Blackboard Assignment link. You should never have results that exceed 20 records! If you do, you need to review your tables, relationships, criteria, etc It is up to you to identify the correct Tables and Fields to use. Remember to appropriately name your output fields and set their formats. You will lose points if the output is not clear and understandable. Last Updated: 11/03/2014 1
Background image of page 1
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
Background image of page 2
Show entire document
Sign up to view the entire interaction

Step-by-step answer

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

ProbSlvg6_ComplexQueries_F14-REV (1).docx
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

Subscribe to view the full answer

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
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Let our 24/7 Microsoft Office tutors help you get unstuck! Ask your first question.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes