Chapter AE, Problem AE5.1

In some cases, users want to use Access and Excel together. They 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.

FIGURE AE-3 Data Displayed in Pie-Chart Format

Source: Microsoft Corporation

Download the Access file Ch05Ex01_U10e.accdb. Open the database and select DATABASE TOOLS/Relationships. As you can see, there are three tables: Product, Vendor-ProductInventory, and Vendor. Open each table individually to familiarize yourself with the data.

For this problem, we will define InventoryCost as the product of Industry-Standard-Cost 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.

1. a. Sum this data by vendor and display it in a pie chart like that shown in Figure AE-3 (your totals will be different from those shown). Proceed as follows:
   1. (1) Open Excel and create a new spreadsheet.
   2. (2) Click DATA on the ribbon and select From Access in the Get External Data ribbon category.
   3. (3) Navigate to the location in which you have stored the Access file Ch05Ex01_U10e.accdb.
   4. (4) Select the query that contains the data you need for this pie chart.
   5. (5) Import the data into a worksheet.
   6. (6) Format the appropriate data as currency.
   7. (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.

h. Follow a similar procedure to create the bar chart shown in Figure AE-4. Again, your data will be different. Place the data and the chart in separate worksheets and name them appropriately.

FIGURE AE-4 Data Displayed in Bar-Chart Format

Source: Microsoft Corporation