Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Forms _ Secondary X-axis In Chart

Posted by: Ron50 May 23 2020, 09:51 AM

I have a made 3-D area chart on a form displaying several data series. The X-values are of the date/time data type and aggregated into weeks using a Total query. I use each week's maximum date values as the series X-values in the chart's row-source property. The data series are projected onto a single time scale X-axis. However, I would rather have a contiguous 3-D column chart, but this makes the columns very narrow as each week's column will be projected on only one day of the week, making it impossible to get a contiguous sequence of columns.

So what I would like to do is to use the week numbers as categorical X-values and at the same time use a Time scale X-axis in my chart. For this I will need to create a secondary X-axis in my chart, but I have found no way to do this. I have found an old thread here on this forum which says that this can be done on the Axis tab of the Format Data Series dialog box. There seem to be 5 tabs on the Format Data Series dialog box, but in my version of Access there are only 3 tabs: Patterns, Data Labels, and Options. There is no Axis tab. Or maybe I am missing something? Anyway, what can I do? I don't want to use the category scale for the week numbers as my X-axis, as this is less intuitive than a time scale axis.

Any help will be greatly appreciated.

Posted by: June7 May 23 2020, 12:24 PM

I have never heard of secondary X-axis in Access chart. Secondary Y-axis yes.

And IIRC, availability of second Y-axes depends on type of chart.

I analyze graph issues best working with data. If you want to provide db for analysis, follow instructions below.

Posted by: Ron50 May 24 2020, 04:52 AM

Thank you for reaching out, June7. You can find a stripped down version of the database in the attachment. I hope this helps.

Posted by: June7 May 24 2020, 01:20 PM

Pretty chart and probably most elaborate one I've ever seen. Apparently no secondary axis (x or y) is available for this chart type.

I did a test and changed to bar chart and secondary axis options show up greyed out on Chart Options window Titles tab. On Format Data Series window on Axis tab I see secondary axis option which activates a second Y axis. No option for X axis.

Activating second Y axis causes the options on chart Titles tab to no longer be greyed out. So second X axis title can be set to show at top but there is no secondary X axis line. Now select Axes tab on Chart Options and check the Secondary X axis option.

Why use form/subform? Why not a single form with search controls in form header?

Posted by: Ron50 May 25 2020, 12:12 PM

Thank you for appreciating my chart. I think that charts are important tools to convey the meaning of statistical numbers. So I try to give them an attractive appearance without overdoing it.

I tried to replicate your test by changing to 3D Bar Chart. On the Chart Options dialog box Titles tab there was a greyed out Series (Y) axis option visible. I take this for what you call the secondary axis option. Next, on the Format Data Series dialog box there is no Axis tab in my version of Access 2007, preventing me to continue from there on. There seems to be no option for whichever secondary chart axes in my version of Access 2007.

Another issue is perhaps that combining two different grouping variables into one row source query may present a problem for Access: Access seems to interpret the first dataseries as the independent variable (to be used as X-axis values) and all subsequent dataseries as the dependent variables (to be used as Y-axis values). Having the first two dataseries as grouping variables seems to lead Access to plot the second grouping variable on the Y-axis, which is not what is intended. It is intended to be plotted on a (secondary) X-axis. If this problem is insoluble, it will evidently be impossible to create charts with a secondary X-axis in Access. It would look then as if what I'm trying to do is simply impossible in Access.

As to the form/subform question: you are very much right. I just hadn't thought about it. I've changed the form/subform structure into a single form with the selector controls placed in the form header and the rest in the Detail section. It is simpler and therefore preferable. And I noticed it's faster, too. And it cleared up another problem that had mystified me from the start: for some unknown reason a few isolated records plainly refused to show up in the chart in the form/subform arrangement, but now they have decided to join their fellow partners in the chart. Testing the chart's row source query in a common query proved that there was no problem with the affected records: they showed up without any problem. There really is no reason why they wouldn't show up in the chart. And yet they weren't there. (These records aren't in the example database.) So thank you kindly for that suggestion.

I hope you can still help me with the different groupings / one row source problem that I mentioned above. Or would it perhaps be better to open a separate forum thread on this topic?

Posted by: June7 May 25 2020, 01:04 PM

Not 3-D, just regular bar chart.

You are correct in that only first field in RowSource can be considered for X-axis.

Posted by: Ron50 May 25 2020, 02:43 PM

I take that as a proposition that it is impossible to have a secondary X-axis in Access.

Posted by: June7 May 25 2020, 03:08 PM

Not with a different field. My test with your chart resulted in lower x-axis showing the dd-mmm and top x-axis showing dd/mm/yyyy. Same data, same scale.

Actually, I used column, not bar.

Posted by: Ron50 May 28 2020, 09:16 AM

Yes, I guess I could add more fields using the same time scale field as X axis field, but that wouldn't solve my problem. What I am trying to do is to have two different fields that can both be used by Access as X axis for the chart. Much like Excel allows to have data series with two different X axis scales. But Access doesn't seem to allow that to happen. It interprets only the leftmost field in the chart's row-source query as the sole independent variable—and thus as X values—and everything else as dependent variables—and thus as Y values. To have Access allow what Excel allows would mean that Access should have an option to tell it that there are two instead of one independent variable for the data series in the chart's row source. And there doesn't seem to be such an option.

As it is now I can either plot the chart on a category scale X axis (week numbers), or I can plot it on a time scale. Plotting it on a category scale is not intuitive to spectators—they have to look up the week numbers in a calendar. Plotting it on a time scale X axis (with min or max weekday date as the grouping field) makes the columns very thin, thereby giving the onlooker both a misleading and an unappealing impression of the chart. What is needed is the possibility to use both scale types concurrently.

Unfortunately however, the problem seems to be turning rather academic in my situation, for in my version of Access there is no Axis tab in the Format Data Series dialog box. So even if it would become clear at the end of this much appreciated discussion that there is a possibility to achieve in Access want I aim to do, my version of Access doesn't allow me to do that, for the very option to be able to activate a second X axis is missing.

Posted by: June7 May 28 2020, 01:13 PM

Might be able emulate the effect by overlaying charts. Second chart with a dataset linked to second x-Axes. Don't display labels, data points, grid lines, tick marks, etc on second chart.

Posted by: FrankRuperto May 28 2020, 01:17 PM

QUOTE (Ron50)
I take that as a proposition that it is impossible to have a secondary X-axis in Access.

One more good reason to transfer the data to Excel and do the charting there. You can automate the whole thing from Access.

Posted by: Ron50 May 28 2020, 04:21 PM

To FrankRuperto: Interesting Idea. I read about this before, but never explored it, really. Could you give me some input about how to tackle such an undertaking? Is there a quick way to learn to do it? And the user should be able to remain in Access. The whole idea is to be able to move quickly from record to record. The chart is not a static thing, it should be able to adapt quickly to the new data when moving through the records.

Posted by: June7 May 28 2020, 04:43 PM

Code could manipulate Excel objects then either open Excel for user to view results or possibly display embedded in Access form (either the workbook itself or an image of graph). I once explored this and abandoned. Completely eliminated Excel from a database product.

And no, it won't be quick to learn - based on my experience from developing code to automate Excel (one example as well as code to manipulate Access charts.

In your case, workbook and chart would be built manually and saved and then your code would change data (perhaps just refreshing link to Access query). How this would be managed with multiple db users is not clear to me.

Posted by: Ron50 May 28 2020, 05:10 PM

To June7: I tried that, but I can't make the overlay chart transparent. With all of its elements but the X axis set to "none" the chart's background stays white, even when I have set its Back Style property to transparent.

Posted by: Ron50 May 28 2020, 05:31 PM

To June7: I suspected already that it would not be easy to develop some kind of access-excel integration. I don't think I'm going to explore this any further. I also guess that such an undertaking would create an additional time lag when moving through the records. That would be very undesirable. I guess I'll just stay with the area chart as it is. The main reason why I preferred the column chart to the area chart is that it is mathematically better. A column chart gives each time period an equally wide column, while an area chart plots the first and last of the X values on the Y axis walls, thereby reducing the widths of their time periods to only half of the widths of the other time periods. At the moment there are not many X-axis values yet in the chart, but as time goes by more X axis values will be added, so after some time this problem will become less pressing. In fact, I find the shape of the area chart more pleasing to the eye, and I also think that it better capable to convey the movement in the data. So while it may be mathematically less "pure", it is better on all the other points for this kind of data.

Posted by: June7 May 28 2020, 06:14 PM

I did manage to get a column chart to be transparent. 3-D doesn't allow this as it would defeat the 3-D effect.

Could calculate date for week numbers and display in textbox next to chart.

I have XYScatter chart with a fixed x-axis scale and use textboxes with calculation to display text for x-axis labels as well as line controls to serve as vertical grid lines (lot of trial and error to get positioned). The chart's own vertical grid lines are turned off. Example of expression: =IIf([metric]=True,"4.75","#4"). Textboxes set to run the text vertically.

Posted by: Ron50 May 28 2020, 07:06 PM

I have used textboxes as axis labels in Excel, but never in Access. My experience with them in Excel was not especially positive. As more values are added to the X axis the axis labels will stay at the same place, but they will no longer correspond to the axis values. A row source is dynamic. Therefore the positions of the textboxes will demand frequent adaptations every time the number of X axis values is raised by an additional time period in the chart's row source (which in this case is every week). I didn't like them then and I don't like them now. There is a reason for a chart to have integral axes: they are dynamically adjusted whenever the data demand it.

Posted by: June7 May 28 2020, 07:18 PM

What? How can you not use textboxes in Access?

Posted by: Ron50 May 28 2020, 08:11 PM

I think this discussion has come to its logical conclusion. I've learned that it is impossible to have two distinct X axes in an Access chart, due to the fact that Access can only treat one (the leftmost) field as the chart's X axis. All the other fields in the chart's row source will be treated as Y values. I think it would really add to Access's charting power if it would incorporate the feasibility to create charts with two X axes with distinct axis scale types. It's really a pity that this is not possible.

This discussion has helped me focus my attention, for which I'm truly grateful. I've also learned from your experience, which has saved me from exploring fruitless paths, thus saving me valuable time. In the end I've come to realize that my choice for a 3D area chart wasn't really so bad at all. Considerations other than purely mathematical ones have convinced me that a 3D area chart is after all the best choice for this type of data presentation.

It remains for me to thank you for your kind assistance.

Posted by: June7 May 28 2020, 08:27 PM

Oh, you meant textboxes as part of a chart presentation. Yes, I only mentioned as an example of finding a way because there was a will. My data happened to fit that solution. Unfortunately, sounds like won't for you.

Happy to be of any assistance and good luck with your project.