ACC1x: Graphing Current Sales Compared with Accumulated Sales

• Microsoft Access versions 1.0, 1.1

SUMMARY

This article describes how to create a graph that displays each customer's total sales for all years compared with their sales for 1992. The data in the graph is also broken out according to the company from which the orders were shipped.

This example uses tables in the sample database NWIND.MDB.

MORE INFORMATION

How to Create the Queries

1. Create the following new query based on the Orders table, and then save the query as Total Sales:

         Field:  Customer ID   Ship Via   Order Amount
         Total:  Group By      Group By   Sum
   
   

2. Create the following new query based on the Orders table, and then save this query as 1992 Sales:

         Field:    Customer ID   Ship Via   Order Amount   Order Date
         Total:    Group By      Group By   Sum            Where
         Criteria:                                         >=#1/1/92#
   
   

3. Create a new query based on the Total Sales and 1992 Sales queries.

4. Join on the field [Customer ID]. Double-click the join line and select the option Include ALL Records From Total Sales.

5. Join on the field [Ship Via]. Double-click the join line and select the option Include ALL Records From Total Sales.

6. Drag [Customer ID], [Ship Via], and [SumofOrder Amount] from Total Sales to the query grid. Add the label Total: to [Sum of Order Amount] as follows:

         Total: [SumofOrder Amount]
   

7. Drag [SumofOrder Amount] from 1992 Sales and add the label 1992 as follows:

         1992: [SumofOrder Amount]
   

8. Run the query to verify that it works correctly and then save it as Sales Summary.

How to Create the Graph

1. Create a new, blank report based on the Customers table. Display the field list and drag the Customer ID field to the detail section.

2. Create a new graph in the detail section by selecting the Graph tool from the Toolbox and drawing an unbound object frame.

3. Make the following selections in the Graphing Wizard:

       a. Data source: Sales Summary
            Totals: Sum
   
       b. Which field(s) for data and labels: Ship Via, Total, and 1992
   
       c. What labels do you want: Ship Via
   
       d. What labels for the legend: Total and 1992
   
       e. Link the Graph to the Report: Yes
   
       f. Which fields will link: Customer ID = Customer ID
   
   

4. Save the report and then preview it. For each customer, the report should display a graph of that customer's total sales and 1992 sales organized by the shipping company.