Histogram

[Jason]

I'd like to make a histogram from a group of data. I'd
like the bin sizes to be set based on the data so that
I'll have no more than 10. However, if there isn't much
data, I'd probably only want 3 or 4 bins. Anyone have an
idea how to generate a query or write code that will give
me the dynamic data set I need for the chart?

Thanks in advance,

Jason

 

[Andrew Smith]

A query for a histogram is reasonably straight forward. If you have a table
(tblBin) with fields for the Min and Max values for each bin, and you want
to do a histogram of a field from another table (tblName) from a field
called Pts, then a query based on tblBin with a subquery to do the counting
will work. The SQL would be as follows:

SELECT tblBin.Min, tblBin.Max,
(SELECT Count(*) From tblName
Where tblName.Pts > tblBin.Min
And tblName.Pts <=tblBin.Max) AS [Count]
FROM tblBin;

(I know that I don't really need the min and max fields, but it makes the
query simpler to write).

Clearly tblName could be the name of a query rather than a table, or you
could include a Where clause in the sub query to restrict the results
returned if you need.

You could first create tblBin manually and set up the histogram manually.
You could then write some code to carry out the following operations:

- look up the minimum and maximum values from your data, and the number of
data points.
- calculate the required number of bins based on the number of data points
- calculate the lowest value and the size of each bin
- delete the existing values in tblBin
- write new values to tblBin based on the calculations you've just done
- modify the SQL of the histogram query (or create a new one) so that the
correct data is used

I'm afraid I just don't have time to work all this out in detail for you.