0

Inserting a bar chart object into a Microsoft Access form brings the data into view with a sort order I did not preset when I first designed the underlying query.

enter image description here

I can see that the unwanted sort order pops up in a fixed, uneditable Transformed Row Source property that overrides the Row Source property.

`Row Source`: SELECT * FROM SumOfUtilizationByPersonnelAndDate WHERE [Month] >= 7 AND [Month] <= 7 AND [Year] >= 2019 AND [Year] <= 2019 ORDER BY [SumOfUtilization] DESC , [Created By] DESC; 

`Transformed Row Source`: SELECT [Created By], Sum([SumOfUtilization]) AS [SumOfSumOfUtilization] FROM (SELECT * FROM SumOfUtilizationByPersonnelAndDate WHERE [Month] >= 7 AND [Month] <= 7 AND [Year] >= 2019 AND [Year] <= 2019 ORDER BY [SumOfUtilization] DESC , [Created By] DESC) GROUP BY [Created By] ORDER BY [Created By]

As soon as I select my prepared, properly sorted query in the Row Source property, that immediately causes the Transformed Row Source property to get written--and the ORDER BY clause in it seems to have a mind of its own, choosing columns and sort sequencing that I didn't want.

Also, the Transformed Row Source property is totally uneditable! Attempting to manually edit the Transformed Row Source property's SQL back the way I want causes the error message,

"You can't assign a value to this object.

The object may be a control on a read-only form.

The object may be on a form that is open in Design view.

The value may be too large for this field."

What can I do to get this bar chart to sort the data how I want? The query does display the data in the right order in View mode. Why can't the bar chart just do the same?

I've tried Bar Clustered, Bar Stacked, and Bar Stacked 100% chart type and all end up with the same issue. Note that I do need this to remain a bar chart.

UPDATE: No comments made below up to Sept. 17th, 2019, have helped, other than update me with the understanding that Microsoft Access has recently introduced Modern Charts which differ from Classic Charts. What I am using is a Modern Chart. The comments up to Sept 17th have only had me try to recreate this as a Classic Chart with no better outcomes, but rather more disadvantages.

UPDATE 2 (9/21/2019): Now I tried removing my ORDER BY clause in my Row Source query but the sorting still gets set wrong. However, I learned through that exercise that the Axis (Category) Data Source property under Chart Settings is what defines/selects that ORDER BY field list--which marries both sort order and category data selection in the chart--a very unfortunate marriage indeed because I do NOT want that! Whatever fields I check for Axis Category, they will appear in the Transformed Row Source ORDER BY clause AND ALSO be cast as part of my category axis! Not good! I want the Created By field ONLY to be the Axis (Category), and the sort order (ORDER BY) clause to be set to something completely separate, which is SumOfUtilization! This seems to be a Microsoft bug that I am not sure a workaround exists for yet! I've submitted Feedback to Microsoft via the Microsoft Access menu for now.

enter image description here

|improve this question
  • The Transformed Row Source property is readonly, but generated from a combination of the RowSource property and selected axis fields. – ComputerVersteher Sep 4 at 6:41
  • Right, I get that. But is there a way to set its ORDER BY clause how I want it? – ShieldOfSalvation Sep 4 at 6:45
  • Change axix fields? I don't know. Add query, axix fields, chart type, etc ... Maybe someone knows. What about using Excel? – ComputerVersteher Sep 4 at 7:02
  • Maybe Changing the order of items helps. – ComputerVersteher Sep 4 at 7:05
  • 1
    I suggest to roll up the relevant comments into the post and clean them out. It's too tiring to go through all this stuff in order to understand what you already tried so we don't duplicate your efforts. Especially as you need the answer fast. Show also an example of the data and of the sort order you are seeing. – harrymc Sep 18 at 7:55
1

You are sorting/ordering by individual "SumOfUtilization", but presenting/selecting "Sum([SumOfUtilization])".

Why don't you just add a GROUP BY clause (to the [Created By] field) in your original query and aggregate by "Sum([SumOfUtilization])" so the ORDER BY matches the Axis?

SELECT [Created By], Sum([SumOfUtilization]) AS [SumOfSumOfUtilization] FROM SumOfUtilizationByPersonnelAndDate WHERE [Month] >= 7 AND [Month] <= 7 AND [Year] >= 2019 AND [Year] <= 2019 GROUP BY [Created By] ORDER BY Sum([SumOfUtilization]) DESC , [Created By] DESC;

You can ORDER BY an aggregate value by re-stating an aggregate for that field ("ORDER BY Sum([SumOfUtilization])"), or by referring to it by its Select Ordinal in the ORDER BY clause ("ORDER BY 2").

Or you could query your original query as a sub-query doing the same GROUP BY aggregation shown above.

If necessary, there are several ways you can swap the Axis display once you get the data organized correctly. Specifically, you could adjust the Charts display settings, swap the Major/Minor Axis, or manipulate the order of SELECT from the query.

DETAILS

As I recall, an embedded ORDER BY clause OVERRIDES all object level sorting. Once the ORDER BY is out of SQL, the presentation objects are able to apply their own sort ordering.

To override this use the original query as a Data Source for a sub-query which you use as your real Data Source. This is what the chart object is doing to you here.

Chart API Reference: "https://docs.microsoft.com/en-us/office/vba/api/access.chart.transformedrowsource"

This [Transformed Row Source] string is generated from a combination of the RowSource property and selected axis fields.

Looking at your Row Source SELECT it looks like the order you are selecting in is what the chart is defaulting to as the AxisOrder. You can explicitly select your values to sequence the Chart manually the way you want.

As in, SELECT [SumOfUtilization], [Created By] ...

`Row Source`: SELECT [SumOfUtilization], [Created By], * FROM SumOfUtilizationByPersonnelAndDate WHERE [Month] >= 7 AND [Month] <= 7 AND [Year] >= 2019 AND [Year] <= 2019 ORDER BY [SumOfUtilization] DESC , [Created By] DESC;

Edit: clean up.

|improve this answer
  • Well, the ORDER BY clause in my Row Source property is exactly how I want it. I want SumOfUtilization to be the first column to sort by, followed by the Created By column. The trouble is that the Transformed Row Source property gets set by MS Access and I can't touch it, and it gets somehow hard-coded to sort by the Created By column--overriding what's in Row Source. This is happening at the chart object level, and my intended sort order as spelled in Row Source gets overridden. Now I tried removing my ORDER BY clause in my Row Source query but the sorting still gets set wrong. – ShieldOfSalvation Sep 21 at 5:01
  • It's the Modern Bar Chart new in recent version s of Access. Mine is v1902, coming with Office 365. – ShieldOfSalvation Sep 23 at 21:15
  • If I change the order of the field list (SELECT clause) in the query, the new field sequence does appear in the Chart Settings | Axis (Category) pane, but I am still at square one because the moment I check Utilization first, Access throws it in as the category text. I need the Created By field to be charted as category, but the Utilization values to be used for the sort. Clicking the Utilization checkbox only causes the category axes to display Utilization data instead of my intended Created By field, throwing everything off once again. – ShieldOfSalvation Sep 24 at 6:13
  • Are you trying to get the chart shown in your picture to be sorted top-to-bottom by the total utilization (not the "created by" person name) ? Name (Created By) labels down the left side, sorted by their total utilization, and utilization percent across the bottom? – AenAllAin Sep 24 at 14:29
  • Yes, I am trying to get the chart shown in my picture to be sorted top-to-bottom by the SumOfUtilization (not the "Created By" person name). – ShieldOfSalvation Sep 24 at 15:00
0

BTW, because time had run out without a satisfactory solution in Access, what I ended up doing was creating an Excel spreadsheet that linked up to my Access query using Excel's Get Data button, generating a PivotTable out of that inside Excel, and creating a chart based off that PivotTable. Excel's charting capabilities are far more excellent and capable than what seems currently available in Access, and I got my sort order to come up just like I wanted. Not the solution I was hoping for since now I am forcing my client to use two separate Office products, but it works.

PS: Thanks to everyone who tried to help. I lost 50 points in reputation that I would have gladly given to someone as a bounty if they could help me but the time ran out per superuser.com. Nothing I could do to assign those points to anyone now and I don't get them back either.

|improve this answer
-2

I got the chart sorted as I liked by dragging the field to the first column in the underlying query.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.