[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 945
  • Last Modified:

Chart record source limit

per PAQ :
   20506459
   21469087
   23071091
   20436364
I see that Access 2003 has a limiti of showing 3999 points on a scatter chart. I have 30000 records and If I paste that into Excel 2003 and chart it it plots fine, all data is shown.

I have 2 related questions :
1 Why? Do not both Access 2003 and Excel 2003 both use MSChart? The property sheets on both objects are the same?
2. Does Access 2007 still have this problem?

For those of you who are sure to ask "why on earth are you wanint to plot that much data", it is scientific data that I need to plot and chart and get the regresssion equation for (y=mx+b) and this amount of data in peanuts in our industry.

Note really keen on  getting a third party charting addon but feel free to offer that advise

0
LukeB
Asked:
LukeB
  • 8
  • 7
  • 7
  • +2
5 Solutions
 
RyanProject Engineer, ElectricalCommented:
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.

0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
RyanProject Engineer, ElectricalCommented:
I'm pretty sure Excel07 upped that limit with the increase of the maxRow and maxColumn.
But yes, its signed maxInt limit (32k)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LukeBAuthor Commented:
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.
0
 
RyanProject Engineer, ElectricalCommented:
Oh theres 3rd party data. I've seen mention of some really sweet ones. That do animation and stuff. But that was like $500. Don't recall the name.  Theres some smaller ones, but I've not used any. Just read about them in here.

I don't know what speed the "user defined chart" would be, but you could do simple axis and stuff on it pretty easily (if you can plot 30k lines, i would expect you can do axes, and a bunch of labels (or 1 well formatted label)).
Add you linear best fit using the function.

If you were good, I would expect you could incorporate all this into a form that takes maybe an SQL string argument, making the form modular and could be reused. (By this point the money the company spent paying you to code it could have purchased the $500 product)
Unless you just enjoy the challenge. I'd consider it if i didn't have other projects.


Exporting to Excel, charting that way, possibly even returning the chart object to Access (Copy/paste) would be less work (still limited to 32k pts).

Check out http://office.microsoft.com/en-us/excel/HA101641791033.aspx  Point # 3. Interesting it doesn't mention Access.
0
 
Jeffrey CoachmanMIS LiasonCommented:
LukeB, MB,

Here are the chart specs for Excel 2007:
http://office.microsoft.com/en-us/excel/HP051992911033.aspx

Data points 2d chart - 32,000
Data points for all series - 256,000

Luke,
<are you sure you want to show it all", ... absolutley without questoin, need to see it all>
If you have 30,000 points and one is missing, how would you know?
With so many points jamed together, would not interpolated data be just as good as actual data. (say, 20,000 points)
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?
I'm not trying to argue, I am just trying to understand.
Believe me "large scientific data sets and plotting XY scatter and looking for relationships and trends"
...is not my strong point!
:-)

On a 8.5x11 sheet of paper you can actually discern 30000 points?
Surely there are no tick marks on the axis.
:O

Perhaps it would be useful if you posted a sample of an Excel chart with all of these points?
Post to:
www.ee-stuff.com.

Or are you plotting this chart out on a large scale plotter?

I too have heard/read about the other graphing products.
Most recently, "Keynote" from Apple (The same presentation software Al Gore used in his movie)
...But again , I never needed more than what Excel had.

JeffCoachman

0
 
LukeBAuthor Commented:
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?

0
 
RyanProject Engineer, ElectricalCommented:
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
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)
:-(

0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
RyanProject Engineer, ElectricalCommented:
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.
0
 
LukeBAuthor Commented:
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
0
 
LukeBAuthor Commented:
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?
0
 
LukeBAuthor Commented:
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.
0
 
RyanProject Engineer, ElectricalCommented:
Theres some mentioned in EE within Access forums. But can't direct you much more than that.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Mbw,

<Whats a reasonable solution to this?>

I guess the solution for right now is to port the data to Excel and chart there.

Beyond 30,000...?

Wait and see if XL2007 or Acc2000 can handle it, or go with a thrid party Utility.

If Luke won't pass 30,000 for a while, then he has some time.

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
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?
0
 
Jeffrey CoachmanMIS LiasonCommented:
Oh,

One more and I'm off to bed.

I just remembered:

"Mathematica" is supposedly the Top of the line mathematical Analysis and presentation tool.
It has been around for years

Prices start at about $50.00 USD

The site claims:
"scaled to multimillion-vertex graphs."
(Whatever that means!)
:-O

http://www.wolfram.com/

Sounds like the way to go to me!
:-)

Good night!

JeffCoachman

0
 
RyanProject Engineer, ElectricalCommented:
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.

0
 
jjafferrCommented:
Hi Luke

Will there be duplicate points in the chart?
OR
Can you group points within a certain region/range, then represent them with one point?

If yes, then you can 1st run your equation on all the data, then 2nd get rid of the duplicate points, then do the chart, this "might" reduce the number of points.

just a thought.

jaffer
0
 
Jeffrey CoachmanMIS LiasonCommented:
MrBullwinkle,

"MATLAB".... Hmmm.

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

JeffCoachman
0
 
LukeBAuthor Commented:
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.
0
 
LukeBAuthor Commented:
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
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 8
  • 7
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now