ACC2: How to Create Fixed-Scale Charts in Reports/Forms

• Microsoft Access version 2.0

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you create a graph in Microsoft Access, Microsoft Graph version 5.0 automatically adjusts the scale of the value axis for each record. This means the displayed range of values may vary from record to record. This article demonstrates how you can manually create a graph with consistent, fixed-scale values for every record.

MORE INFORMATION

To create a graph with fixed-scale values, follow these steps:

1. Open the sample database NWIND.MDB, and create a report based on the Sales By Category query.

2. If the Sorting And Grouping dialog box is not displayed, choose Sorting And Grouping from the View menu. In the Field/Expression box, select Category Name and set the GroupHeader property to Yes.

3. If it is not displayed, choose Field List from the View menu, and drag the Category Name field to the Category Name header section. Set the detail section's Height property to 0.

4. Click the Graph tool in the toolbox, and add a new graph to the Category Name header section. When the Microsoft Access Graph Wizard starts, follow these steps:

       a. Select the Queries option button.
   

       b. In the "Where do you want your graph to get its data?" box,
          choose the Sales By Category query, and then choose the Next
          button.
   

       c. Add Product Name and Product Sales from the Available Fields box
          to the Fields For Graph box, and then choose Next.
   

       d. Select the Add (Sum) The Numbers option button, and then choose
          Next.
   

       e. When prompted "Do you want to link the date in your graph to a
          field on the report?" choose the Yes button.
   

       f. In the Fields On Form box, select Category Name. In the Fields In
          Graph box, select Category Name. Choose the <=> button.
   

       g. When prompted "What type of graph do you want?" choose the
          horizontal bar graph (the first button in the second row),
          and then choose the Finish button.
   

5. Save the report as Report1 and then preview the report. For each category, you see a graph of the total sales per product. Note that the value scale of the horizontal axis varies from record to record.

6. View the Report1 report in Design view and double-click the graph object to open Microsoft Graph. In Microsoft Graph, double-click the horizontal axis to open the Format Axis Patterns dialog box. Choose the Scale tab.

7. In the Format Axis Scale dialog box, remove the check mark from the first four Auto check boxes, type the following values, and then click OK:

          Minimum: 0
          Maximum: 45000
          Major Unit: 5000
          Minor Unit: 1000
   

8. From the File menu, choose Exit and Return to Microsoft Access. When you are prompted whether you want to update the graph in Microsoft Access, choose Yes.

9. Preview Report1. In the graph for each category, you should see a consistent range of values from 0 to 450000 along the horizontal axis.

REFERENCES