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.
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.