ACC: How to Create Fixed-Scale Charts in Reports/Forms 95/97

• Microsoft Access versions 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

When you create a chart in Microsoft Access, Microsoft Graph 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 chart with consistent, fixed- scale values for every record.

NOTE: This article explains a technique demonstrated in the sample files, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0) and RptSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q145777
   TITLE     : ACC95: Microsoft Access Sample Reports Available on MSL

   ARTICLE-ID: Q175072
   TITLE     : ACC97: Microsoft Access 97 Sample Reports Available on MSL

MORE INFORMATION

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

1. Open the sample database Northwind.mdb.

2. Create a blank report based on the Sales By Category query.

3. On the View menu, click Sorting And Grouping to open the Sorting And Grouping dialog box.

4. In the Field/Expression box, select CategoryName and set the GroupHeader property to Yes.

5. If the field list is not displayed, on the View menu, click Field List, and then drag the CategoryName and CategoryID fields to the CategoryName header section, and set the visible property to No for the CategoryID field.

6. Set the detail section's Height property to 0 (zero).

7. On the Insert menu, click Chart.

8. Insert the chart in the group header section. When the Chart Wizard starts, follow these steps:

   a. Click the Queries button.

   b. In the "Which table or query would you like to use to create your chart?," select the Sales By Category query, and then click Next.

   c. Add ProductName and ProductSales from the Available Fields box to the Fields For Chart box, and then click Next.

   d. When prompted "What kind of chart do you want?," click the bar chart button, and then click Next.

   e. When prompted "How do you want to lay out the data in your chart?," click Next.

   f. When prompted "If you want the chart to change from record to record, select the fields that link the document to the chart?," select CategoryId for both the report fields and the chart fields, and click Next.

   g. When prompted "What title would you like for your chart?," click Finish.

9. Save the report as Report1, and then preview the report. Note that for each category, you see a chart of the total sales per product.

   NOTE: The value scale of the horizontal axis varies from record to record.

10. View the Report1 report in Design view and double-click the chart object to open Microsoft Graph.

11. In Microsoft Graph, double-click the horizontal axis to open the Format Axis Patterns dialog box. Click the Scale tab.

12. In the Format Axis Scale dialog box, click to clear the first four Auto check boxes, type the following values, and then click OK:

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

13. On the File menu, click Exit and return to Microsoft Report1: Report.

14. Preview the Report1 report. Note that in the chart for each category, you see a consistent range of values from 0 to 450000 along the horizontal axis.

REFERENCES

For more information about creating a chart, search the Help Index for "charts, creating and inserting," or ask the Microsoft Access 97 Office Assistant.

Keywords          : FmrHowto kbusage
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto