Solved by Expert Tutors
Solved by Expert Tutors
Question

Problem Solving #5
ITM310 Problem Solving #5 – More Database Queries in Access (25pts) Calculation &amp; Aggregation
This assignment is to be done individually. Using the AdventureWorks database and Microsoft
Access, construct a query to answer each question. This assignment will provide you some
experience with building queries using the MS Access Query Tool. You may use Access 2007,
2010 or 2013.
Follow these steps to complete &amp; submit your solutions on this document.
Open both AdventureWorks.accdb and ProbSlvg#5_MoreQueries.doc. Access will
present a security warning. It is ok to approve “Enable this content.”
2. Save this Word document as ProbSlvg5_yourlastname.docx
3. For each question use Access Query Design (not the Wizard):
a. Create and run your queries in the AdventureWorks.accdb database file &amp; save a
copy for later use
b. When the query is working correctly, highlight the results table by selecting by the top
left corner (arrow symbol), then copy it to the Clipboard (Ctrl-C).
c. Switch to ProbSlvg5_yourlastname.docx (Alt-Tab to switch). Place the cursor under
the question#, and Paste the results (Ctrl-V).
1. ProbSlvg#5 Query Exercises – Begin after reviewing linked notes &amp; Access podcasts Your internship supervisor has asked you to find the answers to several questions she
needs for a weekly management report. Develop the answers she needs by running
queries on various tables in the database &amp; be sure the output contains enough detail
Follow the processes below &amp; think about the tables you need to get the data you need.
Format your answers (2 decimal places), and paste the Query Results into this document
under the appropriate question; submit to Blackboard Assignments
Note: No Query Should Have More Than 20 Lines of Output Key Points About Making Calculated Fields in Queries (See also, More About Access
Queries Tutorial)
a. Place the calculation in the Field row of the query design matrix.
b. To assign a helpful name—known as an “Alias”, type in the new name first, followed by
a colon, followed by the calculation. It is a good practice to NOT use spaces in field or
table names.
c. To use a field in a calculation, you must spell it exactly as it is in the table. Also, you may
use one calculated field in another calculation by referring to its Alias.
d. Remember about the order of precedence for mathematical operators. Or, if you are not
sure, place every sub-calculation in parentheses. For example: (price-cost)/price Last updated: 11/03/2014
Page 1 Problem Solving #5 e. If the name or a table, field or alias has spaces in it, then you must enclose the name in
square brackets [xx qq] and make sure to spell it exactly as it was written. Last updated: 11/03/2014
Page 2 Problem Solving #5
Problem Solving #5 – More Database Queries in Access
PS5-Q1 Using Calculated Fields
Start by building a query that shows sales and product detail for the SalesOrderID = #43661. In
addition to the sales order ID, we would like to see the product’s name, and standardcost. For
the sales order, we also need to see the unit price, order quantity, and line total. Hint: Two
tables are needed for this query.
In order to see our profitability, we will add a few calculations to this query and make sure that
each has a name (alias) that makes sense.
a. b. c. d.
e. First, calculate the Unit_Margin, which is the profit per unit sold. The calculation is
simply the unit price minus the standard cost. (UnitPrice-StandardCost) If used in an
aggregation, you need to apply the AVG (average) on the Total row.
Second, we would like to see the Gross_Margin. This is the unit_margin times the
quantity ordered. (UnitPrice-StandardCost)*OrderQty If used in an aggregation, you
need to apply the SUM on the Total row.
Make sure that you have provided Alias names for fields that are understandable to the
manager user. Also, check all fields to ensure results are appropriately formatted as
currency, numbers without or with decimals, or percentages.
Create your calculated fields and test the query. Save query as PS5-Q1.
Copy and paste your formatted query output into the space below. You may have to
adjust margins &amp; font size to fit on this Word page. &lt;Paste results from PS5-Q1 here. You may have to adjust margins &amp; font size to fit the Word
page.&gt; PS5-Q2 Simple Aggregation
So far we have been retrieving data records, perhaps with some filtering, sorting or
calculations, but the output is always at the record level of detail. Often we want
information totaled across all the records, or totaled for sub-groups of records. That
is called Aggregation.
Your manager would like to know how much our total sales and margins were for
a. Create a new query using SalesOrderDetail and Product tables.
b. Press the Totals icon (Epsilon symbol) to turn on aggregation. You will now see a “Total”
row added to the query details area.
c. Include the field SalesOrderDetailID, and use the aggregation function called ‘Count’.
d. Include Unit Price and Standard Cost, which should both use the ‘Average’ function.
e. Include Order Quantity and Line Total, which both use the ‘SUM’ function.
f. Include the calculated Unit Margin and Gross Margin fields. Unit Margin will use the
‘Average’ function, and Gross Margin uses the ‘SUM’ function. Last updated: 11/03/2014
Page 3 Problem Solving #5
View your results (should be a single line). You may need to reformat one or more of the
fields before you are done.
h. All of these fields will require appropriate Alias names that are understandable to the
manager user. Also, check all fields to ensure results are appropriately formatted as
currency, numbers without or with decimals, or percentages.
i. Create your calculated fields and test the query. Save query as PS5-Q2.
g. &lt;Paste results from PS5-Q2 here&gt;
Please Note: After you have saved a field that holds an aggregated calculation, Access will reorganize it automatically, though it will work exactly the same. The Total row will change to the
aggregated function called ‘Expression’, and the function you had used (Sum or Avg) will be
written into the field calculation. PS5-Q3 Grouped Aggregation
Next your manager would like to see these totals broken down by territory, with the percentage
margin added. This should be an extremely easy problem!
a. Start by copying the query PS5-Q2 and rename it PS5-Q3
b. Add the TerritoryID and the Name of the Territory as the leftmost fields.
i.
(HINT-1: you will need to include other tables.)
ii.
(HINT-2: Territory Name may display in Chinese looking characters. To make it
display in English, change the function from ‘Group By’ to ‘First’.)
c. Create your calculated fields and test the query. Save query as PS5-Q3.
&lt;Paste results from PS5-Q3 here. You may have to adjust margins &amp; font size to fit the Word
page.&gt; PS5-Q4 On Your Own - Meeting Sales Quotas
meeting coming up this afternoon. I’d like to see data on each sales person. The data should
include: number of orders, total sales revenue, and gross margin. Especially important, I need
to see their SalesQuota, and their calculated Commission Earned.”
(HINT: Commission Earned is the salesperson’s CommissionPct multipled by their total
sales revenue. The CommissionPct field in access was incorrectly formatted as
currency.)
Build a query that will give your manager useful data for the meeting. Make sure the results
are very readable. Save query as PS5-Q4.
&lt;Paste results from PS5-Q4 here. &gt;
PS5-Q5 On Your Own - The Big Kahunas Last updated: 11/03/2014
Page 4 Problem Solving #5
Another email arrives from your manager: “I need some good news for the Board meeting. For
our top 5 customers who brought in the most revenue, show me a few other key revenue and
profitability measures, plus their territory and salesperson.”
Hints: To limit your results to the Top 5, sort the query by Sum of LineTotal (Descending) and
open the property sheet for the overall Query, and change the property called ‘Top
Values’ to 5.
You should always use the Name instead of the ID if it is available. But remember that
for some Names you need to take the ‘First’ instead of the ‘Group By’ function. &lt;Paste results from PS5-Q5 here: ONLY PASTE THE RESULTS FOR THE FIVE TOP
CUSTOMERS&gt; End of PS#5 – Save your Access Database file with the queries and keep it for future assignments. Submit ONLY this completed Word doc into the Problem Solving 5 assignment in
Blackboard. Last updated: 11/03/2014
Page 5

ur laoreet. Nam risus ante, dapib

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 dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit a

ipiscing elit. Nam lestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Do sum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec

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