MSGraph.Chart.8 data point limit

[grant]

I'm trying to use a scatter chart to plot level reading for a pump
station level sensor.

The sensor takes a reading every 4 seconds, and there are 23,000
reading per chart

There appears to be a limit of 4000*4000 data points in MS Chart so I
have had a play with the Office 10 activX chart control, but cant get
that to plot the data at all. It always try to summaries the data for
some reason fathomable only to Bill Gates! Mind you, the MS Graph
wizard does the same, but at least I can work around this wizard, but
with the active X one I cant seem to specify X and Y data sets without
them being summarised.

So how can I reduse my dataset down to less than 4000 records to use
the MS Graph object.

Here is a sample of the data

DateTime Level1
07/09/07 13:22:06 2.96
07/09/07 13:22:10 2.98
07/09/07 13:22:14 2.98
07/09/07 13:22:18 2.96
07/09/07 13:22:22 2.96
07/09/07 13:22:26 2.96
07/09/07 13:22:30 2.98
07/09/07 13:22:34 2.96
07/09/07 13:22:38 2.98
07/09/07 13:22:42 2.96
07/09/07 13:22:46 2.96
07/09/07 13:22:50 2.96
07/09/07 13:22:54 2.98
07/09/07 13:22:58 2.98
07/09/07 13:23:02 2.98
07/09/07 13:23:06 2.98
07/09/07 13:23:10 2.98
07/09/07 13:23:14 3
07/09/07 13:23:18 3
07/09/07 13:23:22 2.98
07/09/07 13:23:26 2.98
07/09/07 13:23:30 2.98
07/09/07 13:23:34 2.98
07/09/07 13:23:38 3
07/09/07 13:23:42 2.98
07/09/07 13:23:46 3
07/09/07 13:23:50 2.98
07/09/07 13:23:54 3
07/09/07 13:23:58 2.98
07/09/07 13:24:02 3
07/09/07 13:24:06 3
07/09/07 13:24:10 2.98
07/09/07 13:24:14 3
07/09/07 13:24:18 3
07/09/07 13:24:22 2.98
07/09/07 13:24:26 3
07/09/07 13:24:30 3
07/09/07 13:24:34 2.98
07/09/07 13:24:38 3
07/09/07 13:24:42 3
07/09/07 13:24:46 2.98
07/09/07 13:24:50 2.98
07/09/07 13:24:54 2.98
07/09/07 13:24:58 3
07/09/07 13:25:02 3
07/09/07 13:25:06 2.98
07/09/07 13:25:10 3
07/09/07 13:25:14 2.98
07/09/07 13:25:18 3
07/09/07 13:25:22 3
07/09/07 13:25:26 3.02
07/09/07 13:25:30 3
07/09/07 13:25:34 3
07/09/07 13:25:38 3.02
07/09/07 13:25:42 3.02
07/09/07 13:25:46 3
07/09/07 13:25:50 3.02
07/09/07 13:25:54 3.02
07/09/07 13:25:58 3.02
07/09/07 13:26:02 3.02
07/09/07 13:26:06 3
07/09/07 13:26:10 3.05
07/09/07 13:26:14 3.05
07/09/07 13:26:18 3.02
07/09/07 13:26:22 3
07/09/07 13:26:26 3.05
07/09/07 13:26:30 3.05
07/09/07 13:26:34 3.02
07/09/07 13:26:38 3.02
07/09/07 13:26:42 3
07/09/07 13:26:46 3.02
07/09/07 13:26:50 3.05
07/09/07 13:26:54 3.05
07/09/07 13:26:58 3.02
07/09/07 13:27:02 3.05
07/09/07 13:27:06 3.02
07/09/07 13:27:10 3.02
07/09/07 13:27:14 3.05
07/09/07 13:27:18 3.05
07/09/07 13:27:22 3.05
07/09/07 13:27:26 3.05
07/09/07 13:27:30 3.02
07/09/07 13:27:34 3.02
07/09/07 13:27:38 3.05
07/09/07 13:27:42 3.05
07/09/07 13:27:46 3.05
07/09/07 13:27:50 3.05
07/09/07 13:27:54 3.05
07/09/07 13:27:58 3.02
07/09/07 13:28:02 3.05
07/09/07 13:28:06 3.02
07/09/07 13:28:10 3.05
07/09/07 13:28:14 3.05
07/09/07 13:28:18 3.07
07/09/07 13:28:22 3.07
07/09/07 13:28:26 3.09
07/09/07 13:28:30 3.12
07/09/07 13:28:34 3.14
07/09/07 13:28:38 3.14
07/09/07 13:28:42 3.16
07/09/07 13:28:46 3.19
07/09/07 13:28:50 3.21
07/09/07 13:28:54 3.21
07/09/07 13:28:58 3.28
07/09/07 13:29:02 3.3
07/09/07 13:29:06 3.3
07/09/07 13:29:10 3.33
07/09/07 13:29:14 3.35
07/09/07 13:29:18 3.33
07/09/07 13:29:22 3.39
07/09/07 13:29:26 3.42
07/09/07 13:29:30 3.42
07/09/07 13:29:34 3.46
07/09/07 13:29:38 3.46
07/09/07 13:29:42 3.49
07/09/07 13:29:46 3.49
07/09/07 13:29:50 3.51 high point
07/09/07 13:29:54 3.51 high point
07/09/07 13:29:58 3.51 high point
07/09/07 13:30:02 3.49
07/09/07 13:30:06 3.51
07/09/07 13:30:10 3.49
07/09/07 13:30:14 3.46
07/09/07 13:30:18 3.46
07/09/07 13:30:22 3.46
07/09/07 13:30:26 3.44
07/09/07 13:30:30 3.44
07/09/07 13:30:34 3.46
07/09/07 13:30:38 3.44
07/09/07 13:30:42 3.44
07/09/07 13:30:46 3.44
07/09/07 13:30:50 3.44
07/09/07 13:30:54 3.42
07/09/07 13:30:58 3.39
07/09/07 13:31:02 3.42
07/09/07 13:31:06 3.42
07/09/07 13:31:10 3.37
07/09/07 13:31:14 3.37
07/09/07 13:31:18 3.37
07/09/07 13:31:22 3.35
07/09/07 13:31:26 3.33
07/09/07 13:31:30 3.35
07/09/07 13:31:34 3.35
07/09/07 13:31:38 3.33
07/09/07 13:31:42 3.3
07/09/07 13:31:46 3.25
07/09/07 13:31:50 3.25
07/09/07 13:31:54 3.23
07/09/07 13:31:58 3.23
07/09/07 13:32:02 3.23
07/09/07 13:32:06 3.21
07/09/07 13:32:10 3.19
07/09/07 13:32:14 3.16
07/09/07 13:32:18 3.14
07/09/07 13:32:22 3.14
07/09/07 13:32:26 3.12
07/09/07 13:32:30 3.09
07/09/07 13:32:34 3.09
07/09/07 13:32:38 3.05
07/09/07 13:32:42 3.02
07/09/07 13:32:46 3

Its a sewerage pump stations, so we have inflow that fills the pump
chamber until the water level hits the start float and the pump kicks
in. This will draw the water level down untill the stop float turns
the pump off and the level starts rising again. This cycle continues
through out the monitoring period.

I'm only interested in the highs and lows, but the problem is that due
to the wave action and sensitivity of the sensor, the difference in
level between consecutive readings cant be used to identify the top or
bottom of each cycle as it may just be a "false" high.

So, how do I filter out the intermediate records so I have just the
highs and lows with the date time against each?

Cheers

Grant

[grant]

Also, I've dumped the full 23,000 records out to excel and can graph
it in excel using MZ Graph, so why is there a 4000*4000 limit in
Access and not Excel???.

[Marshall Barton]

gr***@technologyworks.co.nz wrote:

>I'm trying to use a scatter chart to plot level reading for a pump
station level sensor.

The sensor takes a reading every 4 seconds, and there are 23,000
reading per chart

[snip]

>So how can I reduse my dataset down to less than 4000 records to use
the MS Graph object.

[snip]

>Its a sewerage pump stations, so we have inflow that fills the pump
chamber until the water level hits the start float and the pump kicks
in. This will draw the water level down untill the stop float turns
the pump off and the level starts rising again. This cycle continues
through out the monitoring period.

I'm only interested in the highs and lows, but the problem is that due
to the wave action and sensitivity of the sensor, the difference in
level between consecutive readings cant be used to identify the top or
bottom of each cycle as it may just be a "false" high.

So, how do I filter out the intermediate records so I have just the
highs and lows with the date time against each?

I can't help with the graph control issues, but would it be
usefule to first filter the data down to the min and max of
each 2 or 3 minute interval?

SELECT DateValue(dt) + TimeSerial(Hour(dt),
2 * (Minute(dt) \ 2), 0) As TimeBracket
Min(Level) As MinLevel,
Max(Level) As MaxLevel
FROM table
GROUP BY DateValue(dt) + TimeSerial(Hour(dt),
2 * (Minute(dt) \ 2), 0)

OTOH, it seems like the peaks should be within a reasonable
range of each other, so it might be useful to filter to
within a range of the min/max of the entire dataset:

SELECT dt, Level
FROM table
INNER JOIN [SELECT Min(Level) As MinLevel,
Max(Level) As MaxLevel
FROM table]. As X
ON Level < MinLevel + .04 OR Level MaxLevel - .04

--
Marsh

[CDMAPoster]

On Jul 28, 7:51 am, [e-mail] wrote:

I'm trying to use a scatter chart to plot level reading for a pump
station level sensor.

The sensor takes a reading every 4 seconds, and there are 23,000
reading per chart

There appears to be a limit of 4000*4000 data points in MS Chart so I
have had a play with the Office 10 activX chart control, but cant get
that to plot the data at all. It always try to summaries the data for
some reason fathomable only to Bill Gates! Mind you, the MS Graph
wizard does the same, but at least I can work around this wizard, but
with the active X one I cant seem to specify X and Y data sets without
them being summarised.

So how can I reduse my dataset down to less than 4000 records to use
the MS Graph object.

Here is a sample of the data

DateTime Level1
07/09/07 13:22:06 2.96
...
07/09/07 13:29:46 3.49
07/09/07 13:29:50 3.51 high point
07/09/07 13:29:54 3.51 high point
07/09/07 13:29:58 3.51 high point
07/09/07 13:30:02 3.49
...
07/09/07 13:32:46 3

Its a sewerage pump stations, so we have inflow that fills the pump
chamber until the water level hits the start float and the pump kicks
in. This will draw the water level down untill the stop float turns
the pump off and the level starts rising again. This cycle continues
through out the monitoring period.

I'm only interested in the highs and lows, but the problem is that due
to the wave action and sensitivity of the sensor, the difference in
level between consecutive readings cant be used to identify the top or
bottom of each cycle as it may just be a "false" high.

So, how do I filter out the intermediate records so I have just the
highs and lows with the date time against each?

Cheers

Grant

I wrote a little program to do a scatter plot output to pdf format:

http://personalwebs.oakland.edu/~for...catterPlot.pdf
http://personalwebs.oakland.edu/~for...catterPlot.zip

Notes:

1) Provided "as is"
2) Use at your own risk
3) Ignore the warning when opening Acrobat Reader. This has never
caused me any problems. Acrobat Reader is simply "fixing up" the
file.
4) The data point shape should be chosen using a combobox instead of
being hard coded.
5) The software is not polished and should be considered experimental.
6) The software assumes Acrobat Reader is installed in the default
installation directory.
7) The extreme values are not placed on the graph but it is not
difficult to put that information on the graph using code.
8) The fields in any data tables must be ID, X and Y.
9) The software is written in A97 but should convert to later versions
with little or no effort.
10) The software does not expect any Null values in X or Y. (You can
also try 'WHERE X IS NOT NULL AND Y IS NOT NULL' as part of the SQL
string.)

On my (slow) computer it took about five minutes to produce a scatter
plot for 26,000 points in "efficiency" mode. I learned an interesting
thing about Access while trying this out on 30,000 data points:

For very large strings it is better to group several strings together
before appending them. E.g.,

Once strX is large,
strX = strX & "A "
strX = strX & "B "
strX = strX & "C "

is about three times slower than
strX = strX & "A B C "

It isn't intuitive to me that longer strings take longer for Access to
find out where they end. Normally, this situation could occur after
the compressed pixel information for a large image has been added to
the pdf stream. Because of the relatively intense pdf calculations
needed to draw filled circles rather than filled squares, circles take
about eight times longer than squares to create (I.e., it took about
40 minutes for 26,000 data points on my machine using circles.). I
realize that pdf output may not suit your needs. Even though all the
data points are plotted, it is possible for some data points to be
completely covered up by later data points. If you try it, let me
know if you discover any problems.

James A. Fortune
CD********@FortuneJames.com

[CDMAPoster]

On Jul 29, 10:07 pm, [e-mail] wrote:

http://personalwebs.oakland.edu/~for...catterPlot.pdf

I'm having trouble displaying this pdf from my browser. It displays
fine in Acrobat Reader 7.0. Maybe skip the sample pdf. I'll need to
look into why the browser is having difficulty.

James A. Fortune
CD********@FortuneJames.com

[grant]

On Jul 30, 2:27 pm, [e-mail] wrote:

On Jul 29, 10:07 pm, [e-mail] wrote:

http://personalwebs.oakland.edu/~for...catterPlot.pdf

I'm having trouble displaying this pdf from my browser. It displays
fine in Acrobat Reader 7.0. Maybe skip the sample pdf. I'll need to
look into why the browser is having difficulty.

James A. Fortune
[e-mail]

Marshall

Thanks for the suggestions, i've now grouped the dat into 30 second
intervals so MS Graph can now handle it.

I've done quite a bit of graphing in Access, and this project has
shown up all the bugs in MS Graph like..

-not holding the scale settings, when set by code
-loosing the chart when changing the record source
-MS graph is much more stable in Access 97 than AccessXP!
-sometimes having to toggle between "by Row" and "by colum" to force
the chart to display the data

Thanks again

Grant