Access2002 Charts: Data Limits, how to break?

Kelemit

Registered User.
Local time
Today, 11:56
Joined
Mar 30, 2006
Messages
57
The BIG problem is this.

I need a basic string graph over time recieved from a cross tab query. The data points all come in fine, but the "strings" / lines connecting the points do not show up.

I believe the reason is because there are just too many records.

I culled the data down using a vba loop to only show 1 stop per minute. (BTW, is there a way to do this using only SQL?) I now show all record points, but still do not show the lines between points. Some of the lines show up, but the more data points on the graph there are, the less the lines show up, till only one or two show.

This is an Access limitation that I'm unsure of how to get around. This really shouldn't be an issue though, because Excel handles the graph just fine EVEN BEFORE the culling of the data down to access acceptable limits. And it shows the lines between each record as well.

Does anyone know of a resolution for this?

Kelemit
 
I once had Access plot a dataset based on one observation every 15 minutes for at least four months and it worked OK. That was something over 12,000 points if I recall correctly. Do you have more than that number?

You can get Access to build a query that summarizes time to the minute if you wish. I think the query wizard can do that. But it has been a while so I make no promises. You might also try to do something like run a query to convert the time to one of the formats that only shows minutes, then run that back through a second query to back-convert it to Date/Time format. Though to be honest, Access and time formats don't always get along.

In order to better answer your question, I need you to think about the following and see if any of these apply:

Access will have trouble graphing if:

Selected/default printer is very low resolution. (OR no default printer is selected.) MS Graph (called by Access) has to draw lines for a specific printer. Trying to draw 4800 dots per inch on a 2400 dpi printer ain't gonna cut it.

Memory is small (< 1/4 Gb) and swap space is no more than the size of memory. Generally, you have to swap a lot on small-memory machines. After enough swapping you start getting data drop-outs. Decidedly not good.

Date or Date/Time format conversion errors occur between the CrossTab and its original table or query. (You said you were plotting against time.)

Hint: If Excel handles it and Access does not, it is not a limits problem per se. Both Access and Excel call MS Graph to get the job done. And Excel has less capacity for number of rows in tables than does Access. So the problem isn't in Access, but rather must be in the way this particular chart was specified. I know that doesn't help, but it is nonetheless true.
 
Excellent reply.

The data plots can easily be over 12,000.

I noticed data poins would drop, especially during the busy times in the day, where I would actually not even have 2 ~ 3 entire sets plotted on the screen.

The possible number of points I can recieve is quite large, and is only on a daily basis.

I have a possible 18 trains stopping at 8 stops moving to each location within approximately 3 minutes.

The maximum number of trains on the system during peak times is usually 13. The number of data plots don't usually exceed 20k though. Which is what was bugging me. Excel plotted the set fine, Access, at peak times, would drop entire series.

I solved this by culling the data down such that it only ever took on stop per minute. This is not a solution, and ultimately did not give me what I needed, which was a plot of trains over time stopping at each location.

I am now, with the data culling of 1 stop per minute per station, able to show all data, but I desire a string graph to show the actual path the train travels over time. The current temporary graph I use only shows that a stop was made by A TRAIN at SOME TIME within that one minute period AT THAT stop. But it does not show which train, and currently, even if it did, would not show continuity of train travel because several trains can stop within on stop within on minute, so train stops can actually be skipped.


I honestly do not believe the printer is the issue. The display on screen itself does not work. And the printer used is a high quality printer. I am uncertain of its DPI, but am 99% that the printer is not the issue. But good point.

I do not believe that it is a date time conversion issue either. When I remove points, the data starts showing up approbriately.

When you brought up memory, I looked at my system memory.

256 mb of ram :(...

i'm pretty sure thats the issue. I have a home laptop here, I will test now to see if I can get better results with 1 gig of ram.

Thank you much. I would not have even thought of that issue.

Kelemit
 
Yep, 256 Mb for a modern Windows-anything box is kind of limiting. On my WinXP Home box, when I look at a detailed memory usage diagram, about 280 Mb. are occupied. But then again I have 1 Gb in the box.

The less swapping / paging you have to do, the better off you are.

The difference between 12K points and 20K points is not that much. But I'm curious now. What precisely do you mean by "string" graph. Is that just an X-Y graph with the dots connected by a line? Or did you have something else in mind. I've not heard that nomenclature before. The closest I've ever heard is "line graph."

From your description, is that a municipal transit rail system you are running?
 
String = Line...

I know most of my stuff from self taught info. Which means alot of what I use to describe can sometimes be wrong, I apologize.

And yes, that is exactly what I'm looking for, just to have the points connected by lines to show travel over time through each station.

And yes, the trains are automated system for a major air port here on the east coast. They travel between parking, the terminals, and a link to the north east corrider Rail system.

The whole system is automated, and returns a bunch of VERY raw data, that IS NOT standardized.. SIGH!!! arg..

Fields are not alway standard. IE: Field 8 ~ 14 can be different things depending on the context of field 3.

If Field 3 is type A report, then Field 8 = train number, field 9 = location, field 10 = error report, etc

If Field 3 is type B report, then Field 8 = Engine Number, Field 9 = Collision type, and Field 10 = ... something else....

its Horrid.. absolutely horrid.

Gotta take this data, import about 1/4 million records daily, analyze it with various queries, some scripting, etc, then generate a report from it displaying just about everything imaginable that could happen to an automated train system.

Then flush out the records for the next days reports. Need to do junks of days in about 10 ~ 15 time junks.

Got the whole system automated now, it will generate an entire months of reports at a click of a button, but I want to try to change the graph I was talking about before.

Currently, it simply shows stops made at each station by time, but doesn't distinguish by train.

I can make a cross tab query to get the trains easy, but I cannot get the lines to consistently display between points.

Here, I can show you and example of the graph NOT showing the lines consistently.

See the attached PDF.

As you can see, we are at the moment only a day in chunks of three 8 hour time periods, and even then, the display is rather...... useless. But... explain that to those who want it.

Kelemit
 

Attachments

Users who are viewing this thread

Back
Top Bottom