Chart record source limit

The charts in Access 03 are not by any means the excel charts. They're lacking in all sorts of ways. There was a guy found a workaround by not using charts at all and created a chart by putting 30k lines on screen representing points. I also think you can use an embedded Excel chart, but I never got it to work. You could also push the data to excel and setup a chart on the fly. This would be slow, but the user would then have all the control of Excel! Would also have the data sheet.

I do not know if the issue was resolved in 07.

As for the linear regression, you don't have to plot it to get that. Theres a function for that.  But I imagine you want to plot it anyway.

LukeB,

Just FYI:

Excel has a 32,000 point limit for charts so you are getting pretty close to that limit as well.
(What then?)

JeffCoachman

I'm pretty sure Excel07 upped that limit with the increase of the maxRow and maxColumn.
But yes, its signed maxInt limit (32k)

Hi Bull and Jeff,

Bull :
Thanks for info on XLS, I thought both XLS and Access use the same Chart Object / MSGraph 'DLL' or whatever. So MS uses two different charting 'dlls' ?

I saw the PAQ on how that IC fellow did the report with Line print, interesting, but no axes, titles, etc.

Yes, I know I can get the regresssion without a plot but I want the user to see the data graphically and look for problem data (yesterday doing this way on the data set we found 10 bad data points ...).  It is a big "scientific" data set (regional geochemical assays vs a variable).

Jeff :
Thanks for the info on XLS, did not know that.
"What Then?" Exactly ... thus my question, does Acc2007 has the same limit? I see no mention of it on MS website. If it does have the same problem then is there any third party software that will plot the data? Like an add on charting or stat package (COM? DLL? VBA?).

Yep, I know a lot a people will say "that is a lot of data to plot, are you sure you want to show it all", ... absolutley without questoin, need to see it all. Once you have worked as lot with large scientific data sets and plotting XY scatter and looking for relationships and trends you need look at the entire data population without the software restricting you to workarounds to display it.

Anyway keen to know any feedback on the issue.

Hi Jeff,

Ok, no problem in trying to understand ...

"What I mean is that if the trend is upward with 30,000 points, that same trend would still be up with 20,000 points, correct?"

No, absolutely not at all ...

Say you have 10,000 sample pts from a statiscal population and you scatter (XY chart) plot them. Say they show a linear correlation (i.e. a straight line) and from that chart, or code, you get the linear regression. Great, nice simple equation to use in data modelling / forecasting.

Now say you get more data, say another 10000 data pts, from your survey or whatever. So do you 'trust' the next 10000 pts and just keep on using the first reults (BTW, if so why bother getting more data)? Or do you want to show them on the chart with the other data (they are the same statistical population, so you must) and make _sure_ they 'behave' and correclate that same as the rest of the statistical population? What if, now with more data from the population, you see that the data is actually not linear but a quadratic relationship (parabolic/ slight curve) and that the intiall amount of data was not enough to have shown you this / discern the detail? You will have a different correlation equation, BIG affect on your forecasting.

So now add even more points, say another 10000, wow ... now you see that not only it not linear but quadratic as was coming to light but now also that there is fanning out or possible dispersion in your data at, say, the upper end of your data where it really matters. Maybe your instruments is faulty/not calibrated/ not senstive at that limit.

Add another 10000 data pts and you will see 'outliers' that are either data error (you will see in your other lots of 10000 data) and will start to see clusters that are occursing off the main trend ... gee, mabye I have got instrument problems / data collection problem / a sub population was selected ....,

The best way to see all this is to plot your data ... no excuse, such as software limits or "ahh,, it'll be ok", not to. Seen too many times, fell into that trap myself more than I care to admit!

Get the idea?

I know you think you want to see all the data, but there are ways to simplify the data.

Example. You don't care about the points that are all mashed into the majority. So you can show for example, points under 10th percentile, above 90th, and the running average (Maybe 10pt avg -that will reduce points by a factor of 10).

The thing to consider is what you're doing with the data that you see. If you're thinking and considering data, then you're doing some kind of mathmatical operation on that data to consider it. It might not be simple in terms of an equation though, but it can be abstracted to one none the less.

I would guess you might be able to do a density function to reduce the data on the chart. Something like a histogram, but possibly 3d.


The link Jeff sent is for 2003, still no word on 07.

MBW,
<The link Jeff sent is for 2003, still no word on 07.>
Yeah, you are right,... But I did google: "Access 2007 chart limit Max"
(I guess I did not read the resulting link)
:-(

Luke,

Ok, I am "kinda" understanding now, (When you say "Get more data")

But I got lost on the rest of it.
:O

Again, being that I was never good at math, let alone regression, I just did not understand.
Thanks for the clarification.

It seems like MBw is better equipted to continue this disscussion with you.
:)

(... I would still like to see a sample chart though)

JeffCoachman

Have a look at:

https://www.experts-exchange.com/questions/23071091/Charts-not-including-all-data-from-table.html?sfQueryTermInfo=1+access+chart+limit

I posted the question of Excel 2007 limits in an expert thread. I can't help ya there. I'm not switching to 07, yet. I just know a bit of math and statisics. I had this problem once. And I did similar to what I mentioned already.

Its pretty simple Jeff, he wants to show more than 32k data points on a chart in Access. Whats a reasonable solution to this?

How about...get out a big sheet of paper, colored pencils and a ruler?  That avoids bugs and compatibility issues.

thanks MB

in a nut shell, as I have poorly tried to explain I do _not_ want to simplify the data for this XY scatter, very important not to - I can do all the weighted averaging, histos and PHDiff stuff in other charts.

Looks like have to go to 3rd party, I will search the web, thought y'all might have come across a good 3rd partth chart addon

Jeff,

I can post an example XY graph where I just copy and pasted the data out of Access and made an XY; it shows the general cluster (like MB mentions) and then the outlier data which is of most interest to me for data checking or trend "worries".  Still want to see that?

GRayL:

thanks, I mentioned in my post that I have seen 23071091 and IC's workaround ... I think I'd rather dive into a 3rd party addon where I can add titles/axes etc.

Theres some mentioned in EE within Access forums. But can't direct you much more than that.

LukeB,

I don't mean to beat this up any more....

How do your peers handle this "Series Celing"?

If what you say is true, then surely you are not the only person on the planet to run up against this limit?

Boag, They passed the task on to him.

MATLAB can do it, haha.

I search google and EE for 15 min looking for those Addons I thought I read about and I don't see anything. How much data are you talking about? For now, I'm thinking your option is Excel Chart. If you max out at 30k, then create a 2nd series and 3rd series within that chart to keep the data going (have to make sure the X-axis is formatted well to allow this).

If that maxes out, then make a new chart, put them side by side and blend them together.  I can't imagine how slow this is going to be to process though.

MrBullwinkle,

"MATLAB".... Hmmm.

I seem to vaguely remember this when I was (zzzzzz....sleeping) in math class.
:-)

JeffCoachman

Boag,

"How do your peers handle this "Series Celing"?"

Very good point, with C++ apps in our office we use Quinn-Curtis and Chart Object (unix platform, very good libary but a b***** to use).

So that got me to thinking and I asked QC ... they have a module for VBA !! So I will likely go with that seing as I kinow the functions and how it works in C++ so I can do that in VBA.

I guess was just a bit dissappointed in current offering with Access and now have to spend time doing the QC (they use memory arrays, not record sets so more overhead) and weave it in at my cost (can not charge to client in this case). But in the long run this will be the solution for we do a lot of neat graphing in C++ with the QC libs.

I also did a search of the web, since the knowledge doesn't seem to be readily available on EE (searched here too, not coming up with much ..). I get MatLabs too and also ChartFX . The ChartFX seems really good, comparable to QC, but am not sure of price and licensing for my end users.

thanks guys, difficult to award points as the offerings are limited in turn to what Access can do and too much of workarounds to make up for the lack in Access .... may as well spend money one the third party tools rather than spending a lot of own or client time bodging a workaround. But going to third party not fun but may be only way for slick and professional and most importatnly fast solution. And no info from MS on Acc2007, have to buy and try.  So dissapointed in MS, not you fellows, not much we can do.

So I spread points based on wjhat I may end up doing/tyring, trust that is agreeable. Other ideas may be applciable to other viewers of the Q , so do read all ...

Luke