ACC: How to Change the Order of Columns in a Chart (1.x/2.0)

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

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

By default, a graph sorts the X-axis fields in alphabetical or numeric order. This article describes two ways to sort the X-axis fields in a different order.

MORE INFORMATION

There are two ways to change the ordering of fields in a graph. The first way is to add an Order By clause to the SQL statement in the graph's RowSource property. The second way is to create a query that orders the fields the way you want them, and then use the query for the graph's RowSource property.

How to Create a Sample Graph

For Microsoft Access version 2.0:

1. Open the sample database NWIND.MDB.

2. Create a new, blank form, and then add a graph object to the form.

3. In the GraphWizard dialog box, select the Sales By Category query as the data source for the graph.

4. Select the Category Name field in the Available Fields box and then choose the ">" button. Then select the Product Sales field and choose the ">" button. Click Next three times, and then click Finish.

5. View the form in Form view. Note that the Category Name records are listed in alphabetical order.

1. Open the sample database NWIND.MDB.

2. Create a new, blank form, and then add a graph object to the form.

3. In the Graph Wizard dialog box, select the Sales By Category query as the data source for the graph. Then, click the Bar Chart button, and then choose Next.

4. Select the Category Name field in the Available Fields box and then choose the ">" button. Then select the Product Sales field and choose the ">" button. Choose Next.

5. Choose Design.

6. View the form in Form view. Note that the Category Name records are listed in alphabetical order.

How to Change the Graph's Sorting Order

Method 1:

The following example demonstrates how to add an Order By clause to the SQL statement in a graph's RowSource property:

1. View the form in Design view.

2. Select the Graph.

3. From the View menu, choose Properties.

4. Select the RowSource property, and then press SHIFT+F2 to zoom the window. The RowSource property looks like:

         SELECT DISTINCTROW [Category Name] AS [Sales by Category],
         SUM([Sales by Category].[Product Sales]) AS [Product Sales]
         FROM [Sales by Category]
         GROUP BY [Category Name];
   

5. Type "Order By SUM([Sales by Category].[Product Sales]) desc" (without the quotation marks) before the semicolon at the end of the SQL statement. The SQL statement should look like:

         SELECT DISTINCTROW [Category Name] AS [Sales by Category],
         SUM([Sales by Category].[Product Sales]) AS [Product Sales]
         FROM [Sales by Category]
         GROUP BY [Category Name]
         Order By SUM([Sales by Category].[Product Sales]) desc;
   

6. Choose OK.

7. View the form in Form view. Note that the Category Name records are now listed in descending order of sales.

The following example describes how to create and use a query for the graph's RowSource property:

For Microsoft Access version 2.0:

1. Create a form with a graph using the above steps.

2. View the form in Design view.

3. With your secondary mouse button, click the graph object, and then choose Properties.

4. With your secondary mouse button, click the Row Source property and then choose Build.

5. For the Product Sales field, change the sort order to Descending.

6. Close the Query window and save the changes.

7. Switch to Form view.

1. Create a form with a graph using the above steps.

2. Make a copy of the Sales By Category query. Call the copy Sales By Category 2.

3. Open the Sales By Category 2 query in Design view. Create the following fields in the query:

         Field: Sales by Category:Category Name
            Total: Group By
            Sort: <leave blank>
         Field: Product Sales
            Total: Sum
            Sort: Desc
   
      Note that the first field is named "Sales by Category:Category Name"
      because the name of the first field becomes the graph's title.
   
   

4. Remove the Product Name field.

5. Save the query.

6. View the form in Design view. Replace the SQL text in the graph's RowSource property with the Sales By Category 2 query.

7. View the form in Form view.

REFERENCES

For more information about ordering fields, search for "order by," and then "ORDER BY Clause (SQL)" using the Microsoft Access Help menu.

For more information about this topic in Microsoft Access 95 and 97, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q141235
   TITLE     : ACC: How to Change the Order of Columns in a Chart (95/97)