histograms from queries

[slrphd]

I have a data set that contains plant operational data and I would like to make a histogram of it. Initially, I made a query based on that field, gave maximum and minimum values for the interval of interest and let the query return the count of the number of values in that interval. This approach requires that I modify the interval limits for each bar in the histogram. Not a big deal but it seems like I ought to be able to do all intervals in a single query. My level of knowledge of how queries behave says that would only work if the count is the same in each interval. Any suggestions?

Thanks.

 

[Len Boorman]

Think I know what you are looking for and the answer is a number of queries.

Basically
you can select the data you want
you now have the range
you can divide the range into 10
you can divide each value by the range step and take the integer result to tell you which interval it belongs to.
you can count the number of each intervals
you can plot the count of intervals against the intervals

Bit short and sharp but did it this way to produve standard distribution curves.

Generally you will need a table on the way to store the values as you derive them and remember to delete all records before starting to create another set.

Good luck

Len

 

[slrphd]

Len,
Thanks for responding. So the procedure is to use a series of queries to pick out the information I need. I feared as much. I was hoping for a function or group of functions that might do some of this for me in a few queries. Looks like I better get started writing queries. Thanks.
Steven

 

[slrphd]

If you have only a few ranges, you can do it with an IIf() but if you have more than 3 or 4, you should make a function. The function will be much easier to read and modify.

Select IIf(YourField > 0 AND YourField < 10,"Group1", IIf(YourField >=10 and YourField < 20, "Group2", IIf(YourField >= 20 and YourField < 30, "Group3", "Group4"))) As YourGroup, ....
From ...;

If you use a function, the query looks much simpler:
Select YourFunc(YourField) As YourGroup, ....
From ....;

Pat,
I do not have it all worked out yet but your suggestion is clearly the way to go. I cannot thank you ehough for all the help you have given me over the last few months. You have provided wonderful insights to what can be done with this tool.

If I undertand your idea, once I have the function written, I should be able to run the query and each record is returned with a field indicating which bin it belongs to. Then I should be able to use the COUNT function to get the population of each category. You're genius, Thanks.
Steven

 

[The_Doc_Man]

You can do this in a sries of queries if you don't mind being a little verbose and if you don't mind "tweaking" it now and then.

Let's say that value [X] is what you wanted. You need to know the min and max values of X. So build a simple-minded query that does a MIN of X in one column, a MAX of X in another column, and maybe a COUNT of X in the third column. Easy to build, gives you what you need to know for the next step.

Now do some mental gyrations. It ALWAYS occurs that if you let a computer pick the boundaries for a graph, it picks the ugliest boundaries it possibly can. (Probably an offshoot of one of Murphy's more obscure laws.)

Decide the number of steps and step size you can accept given the Min and Max. Pick them so they are reasonably round numbers 'cause otherwise you'll get more decimal places than an irrational number convention.

NOW, you need to decide the number of bars in your histogram. This will be a MAXIMUM number 'cause Access claims the right to omit them now and then. Define (knowing the min and max values and the number of columns) the data range and the implied range of data that would fall into a single column. The range is just XMAX - XMIN, the step size is the range / number of intended columns. You need to at least ATTEMPT to make the step size round so that you can plot it meaningfully later.

OK, you've done your homework. Now build a query that, for one field, computes the formula:

COLNO: LONG( NUMCOLS * ( X - XMIN ) / ( RANGE ) )

In ANOTHER column, use

MINX: ( SINGLE( LONG( NUMCOLS * ( X - XMIN ) / ( RANGE ) ) ) * ( STEPSIZE ) ) + XMIN

If I got this right, you will have X in one field, a proposed column/bar number in another field with an expression name of COLNO, and the mininum value of X associated with the histogram's column in the third field of the query with an expression name of MINX.

NOW take this query and build a query that does the following:

COUNT the X field, GROUP BY the COLNO field, average the MINX field.

(Yes, this is a query of a query.)

Now build your histogram from this aggregate query, where the MINX field is your bar label as well as your X-axis and the COUNT(X) field is your Y-axis.

That looks harder than it really is, but once you have built the queries for the first time, you can keep them around and just edit the new range, step size, minimum X, etc. when you need to change them. High initial setup fee, low recurring engineering costs.