Chart Sort Order (2 Viewers)

[MattBaldry]

Hi All,

I am trying to do a modern chart and want to specify the sort order of the month. But Access in all it's wisdom keeps sorting the months alphabetically, not by number. When I edit the Transformed Row Source, I get an error message stating "You can't assign a value to this object"

I have added a field, MonthSequence, which works when the MonthName field is not selected.

As soon as I add the MonthName, it fails.

On my table, the MonthSequence field is before the MonthName, but this seems to make no difference.

I don't suppose anyone has a solution for this? I want to move away from my charts in Excel really.

~Matt

 

[Gasman]

Try using MonthName() for the axis

 

[MattBaldry]

Try using MonthName() for the axis

If I do this, it sorts alphabetically, and not in the correct order

~Matt

 

[MattBaldry]

Figured this out from https://isladogs.co.uk/modern-charts-code,

Thanks Colin

~Matt

 

[Gasman]

We

Figured this out from https://isladogs.co.uk/modern-charts-code,

Thanks Colin

~Matt

Well, please post the solution and not have people have to go looking for it¿

 

[Gasman]

No, I was

If I do this, it sorts alphabetically, and not in the correct order

~Matt

No, thinking of the monthname() function, so that the order is by month number, but happens to show the name.
I must admit, I have not played with charts much, but that is what I would try, given no other replies ATM.

 

[MattBaldry]

We

Well, please post the solution and not have people have to go looking for it¿

Apologies. The Basics of this is that the chart sort order is the last field in the axis, not the first. So to sort by month number, I have to have this after the month name in the query.

Quite why MS decided this I have no idea, but the 2nd video on the link from Colin said this, so I tried, and it worked.

~Matt

 

[Gasman]

Apologies. The Basics of this is that the chart sort order is the last field in the axis, not the first. So to sort by month number, I have to have this after the month name in the query.

Quite why MS decided this I have no idea, but the 2nd video on the link from Colin said this, so I tried, and it worked.

~Matt

Thank you.

 

[isladogs]

For info, not only the sort order but the gridline appearance is based on the final series (not the first series)
There is a popup message that tells you this but its easy to miss:

Whilst you can enter values for other series, they will normally be overwritten

In this case, I have the x-axis in date order using MMM 'YY format but this was the record order so I did this without sorting

If I sort then the values will be sorted ascending/descending and the months-years will be scrambled

The exception to the final series rule is you can set the final series gridlines to major in one colour and the previous series to minor in a different colour, though this can lead to hideous results as deliberately done here.

BTW: The transformed row source is read only and set automatically by Access from the row source used for the chart.

 

[MattBaldry]

For info, not only the sort order but the gridline appearance is based on the final series (not the first series)
There is a popup message that tells you this but its easy to miss:

View attachment 121757
Whilst you can enter values for other series, they will normally be overwritten

In this case, I have the x-axis in date order using MMM 'YY format but this was the record order so I did this without sorting

View attachment 121758

If I sort then the values will be sorted ascending/descending and the months-years will be scrambled

The exception to the final series rule is you can set the final series gridlines to major in one colour and the previous series to minor in a different colour, though this can lead to hideous results as deliberately done here.

View attachment 121759

BTW: The transformed row source is read only and set automatically by Access from the row source used for the chart.

Thanks Colin,

I found your page very informative for what I was aiming to do. What I was wondering is, can you base a chart on a crosstab query directly to save the TRANSFORM part?

I am designing a sales dashboard and our company owner wants the graph to display all months in our financial year but display the empty ones with 0. I can make this happen in a crosstab query, but can't seem to get this to work in a chart.

I am tempted to generate a local table for this, but wondered if there was a solution before I do this.

~Matt

 

[isladogs]

Not with a modern chart as it always transforms the row source. Why not use the original table/query on which your crosstab is based?

 

[MattBaldry]

Not with a modern chart as it always transforms the row source. Why not use the original table/query on which your crosstab is based?

That will work with 3 of the charts I have, but one of the is a Cumulative one, and I can't figure out how to get this one to work for the months that haven't happened yet in our work financial year. I'll have a tinker and see what I can come up with.

~Matt

 

[Gasman]

That will work with 3 of the charts I have, but one of the is a Cumulative one, and I can't figure out how to get this one to work for the months that haven't happened yet in our work financial year. I'll have a tinker and see what I can come up with.

~Matt

Is this a case where you have to supply the column headings yourself?

How to Fix Missing Fields in a Crosstab Query in Access

www.quackit.com

Crosstab query problem

I have a couple of crosstab queries for days of the week. My problem is that if, say week 2, has no data. The report doesn't recognize the field and the report doesn't print. I am making the crosstab query off another query that gets all the records that have a type of "personal" and where...

www.access-programmers.co.uk