Microsoft KB Archive/109315

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

Article ID: 109315

Article Last Modified on 5/6/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q109315



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.

For Microsoft Access version 1.x:


 * 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:

 View the form in Design view. Select the Graph. From the View menu, choose Properties. 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];

 Type Order By SUM([Sales by Category].[Product Sales]) desc 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;

 Choose OK. View the form in Form view. Note that the Category Name records are now listed in descending order of sales.</li></ol>

Method 2:

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.

For Microsoft Access version 1.x:

<ol> Create a form with a graph using the above steps.</li> Make a copy of the Sales By Category query. Call the copy Sales By Category 2.</li>  Open the Sales By Category 2 query in Design view. Create the following fields in the query:

<pre class="fixed_text">     Field: Sales by Category:Category Name Total: Group By        Sort: 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. </li> Remove the Product Name field.</li> Save the query.</li> View the form in Design view. Replace the SQL text in the graph's RowSource property with the Sales By Category 2 query.</li> View the form in Form view.</li></ol>

The columns will be in descending order.

<div class="references_section">