Pareto Graphic

[JtrilloJ]

Hello:I intend to create a Pareto chart with modern Access graphics.Using the Combined option, I have placed the series on the appropriate (Y) axis.On the (X) axis I have the products and they are ordered in ascending order without letting me sort by Sales (descending) and Cumulative percentage (Ascending).I would like to leave an example database but it won't let me upload it.Can you help me?Thanks in advance >> Jacinto Trillo

 

[CJ_London]

Try compacting, then zip and upload the zipped file. Make sure you remove or obscure sensitive data

 

[JtrilloJ]

Here is File >> Jacinto

 

[azhar2006]

Hello:I intend to create a Pareto chart with modern Access graphics.Using the Combined option, I have placed the series on the appropriate (Y) axis.On the (X) axis I have the products and they are ordered in ascending order without letting me sort by Sales (descending) and Cumulative percentage (Ascending).I would like to leave an example database but it won't let me upload it.Can you help me?Thanks in advance >> Jacinto Trillo

https://support.microsoft.com/en-us...to-chart-a1512496-6dba-4743-9ab1-df5012972856

 

[JtrilloJ]

Thanks for the link:It's interesting and I had looked at it, but I'm trying to do something similar in "Access 365" and I can't do it.
As I explained in my initial post, it doesn't let me sort (or I can't do it) by the Sales values descending and Cumulative percentage ascending.
It sorts me by the Axis (X) and there's no way to change it.
You can see the problem in my attached file "ParetoModern".
I'm translating this text with Google so if there's something wrong, I apologize.What I can't change is:
SELECT [IdProducto], Sum([VtaPorProd]) AS [SumOfVtaPorProd], Sum([Pct100]) AS [SumOfPct100] FROM [TblParetoModern] GROUP BY [IdProducto] ORDER BY [IdProducto]

Best regards >> Jacinto

 

[azhar2006]

Thanks for the link:It's interesting and I had looked at it, but I'm trying to do something similar in "Access 365" and I can't do it.
As I explained in my initial post, it doesn't let me sort (or I can't do it) by the Sales values descending and Cumulative percentage ascending.
It sorts me by the Axis (X) and there's no way to change it.
You can see the problem in my attached file "ParetoModern".
I'm translating this text with Google so if there's something wrong, I apologize.What I can't change is:
SELECT [IdProducto], Sum([VtaPorProd]) AS [SumOfVtaPorProd], Sum([Pct100]) AS [SumOfPct100] FROM [TblParetoModern] GROUP BY [IdProducto] ORDER BY [IdProducto]

Best regards >> Jacinto

Try adding a date field to each record and create a query based on the months of the year.
Months: Format([Transaction date];"mmm")

 

[isladogs]

@JtrilloJ
I tried to do this using a Combo chart by first sorting the data but I was also unable to get the desired result
I've discussed this with a member of the Access team who has confirmed that its not currently possible to do in Access
However, they have agreed to look into providing this functionality in the future as part of the ongoing work on modern charts.
Hopefully I will be able to give you more information in the not too distant future.

In the meantime, it is very easy to do this in Excel. Here is an example:

 

[JtrilloJ]

@JtrilloJ
I tried to do this using a Combo chart by first sorting the data but I was also unable to get the desired result
I've discussed this with a member of the Access team who has confirmed that its not currently possible to do in Access
However, they have agreed to look into providing this functionality in the future as part of the ongoing work on modern charts.
Hopefully I will be able to give you more information in the not too distant future.

In the meantime, it is very easy to do this in Excel. Here is an example:

View attachment 116590

Thanks Azhar and Colin:
Colin: The truth is that I had spent hours trying to get it without success.
I would be happy if it was modified and to hear from you when it is done.
I had already built it in Excel.
Kind regards >> Jacinto

 

[isladogs]

It would have been helpful if you had said that in post #1 & indeed provided a screenshot from Excel.
I had never heard of Pareto charts and had to look them up before attempting (unsuccessfully) to do this in Access.

Although the modern chart improvements have now been released, there is still further work being done on these with additional functionality including VBA support. For the latest information, please check my article:

Modern Chart Improvements

This article summarises the very welcome improvements to modern charts that are being rolled out in Access 365 version 2408. Changes include many new chart types and additional functionality with existing charts

www.isladogs.co.uk

I will update it again when there is anything significant to report.

 

[JtrilloJ]

It would have been helpful if you had said that in post #1 & indeed provided a screenshot from Excel.
I had never heard of Pareto charts and had to look them up before attempting (unsuccessfully) to do this in Access.

Although the modern chart improvements have now been released, there is still further work being done on these with additional functionality including VBA support. For the latest information, please check my article:


I will update it again when there is anything significant to report.

Colin:
I'm sorry I didn't explain myself so that the problem I had was clear from the start.
I've been following your posts, and that link has also been very useful to me.
Regards >> Jacinto

 

[isladogs]

Just for info, the Access team have already committed to adding sort functionality to all modern charts e.g. see my article comments re: funnel charts. Adding the ability to create Pareto charts would appear to fit in with that commitment

 

[arnelgp]

you can use Excel automation to build your chart.
open Form1 on the demo.

 

[JtrilloJ]

you can use Excel automation to build your chart.
open Form1 on the demo.

Thanks arnelgp:
It is a very interesting exercise and also demonstrates your mastery of the Access/Excel tandem.
Obviously it is a longer path, but it is a solution in the meantime that Microsoft solves the problem, as Colin announces.
My best regards >> Jacinto

 

[arnelgp]

i made another demo where you can assign a title to the Chart.
before you run the db, first delete agpWB.xlsm on your Documents folder.
view the Open event of Form1 to see the code where i put the Title for the chart.

 

[JtrilloJ]

Thanks again.
It works perfectly.
I had to import the table "zzTable" to avoid the code error.
Regards >> Jacinto

 

[arnelgp]

zzTable is but a remnant of my first approach to use Temporary table to
be used by your Modern Chart, but unfortunately, accesss still doesn't obey
the sequence of which the records are saved (descending on volume and ascending on pct).
you can safely delete that since that table is never used.