Neil at FlexTime used his studio database with Excel to obtain the data that he needs. A more common scenario is to use Microsoft Access with Excel: Users process relational data with Access, import some of the data into Excel, and use Excel’s tools for creating professional looking charts and graphs. You will do exactly that in this exercise.

Download the Access file Ex22 from www.pearsonhighered.com/kroenke. Open the database, select Database Tools/Relationships. As you can see, there are three tables: Product, VendorProductInventory, and Vendor. Open each table individually to familiarize yourself with the data.

For this problem, we will define InventoryCost as the product of IndustryStandardCost and QuantityOnHand. The query InventoryCost computes these values for every item in inventory for every vendor. Open that query and view the data to be certain you understand this computation.

Open the other queries as well so that you understand the data they produce.

a. Sum this data by vendor and display it in a pie chart. Proceed as follows:

(1) Open Excel and create a new spreadsheet.

(2) Click Data on the ribbon and select Access in the Get External Data ribbon category.

(3) Navigate to the location in which you have stored the Access file Ex22.

(4) Select the query that contains the data you need for this pie chart.

(5) Import the data into a table.

(6) Format the appropriate data as currency.

(7) Select the range that contains the data, press the function key, and proceed from there to create the pie chart. Name the data and pie chart worksheets appropriately.

b. Follow a similar procedure to create a bar chart. Place the data and the chart in separate worksheets and name them appropriately.