Subform problem with calculations (1 Viewer)

[jason2885]

Create Chart to show the standard deviation of filtered results graphically

I have a subform that has filtered(by the main form) information from a query. The information that is displayed in subform some of the columns I would like to take the average, median, standard deviation. So how do I go about making another form in order to accomplish this. Also is there anyway to do some graphing of information in a subform.

EDIT: I have this part above figured out but I dont know who to create a graph that looks like your normal bell curve responce for the deviation. Anyone have any ideas.

 

[Tim L]

If you want your graphing information displayed in a subform and you want it to be related to the parent form use a query to determine the values and ensure that the primary key field(s) of the query match the primary key(s) of the parent. This way the values are available both for use in displaying and for the chart. You may need to construct several queries to get each bit of information and then combine the results.

It to place a graph on subform create a form as normal then click on Insert\Chart, followed by using the wizard to set up your chart/graph but ensure that the source for the chart is set to the query defined as above. Finally add this form to the 'master' form as a subform and link accordingly.

HTH

Tim

 

[jason2885]

Ok but here is the thing.

I have the subform displaying the data that I is filtered by the main form. I then have the footer of the subform set up to take the average and stdev of the filtered form. What I want to be able to do is take graph the results of with the cost on the bottom axis and the # of the things.

So how would I go about showing that on a graph?

 

[jason2885]

Also the graph need to show the classic bell shaped curve type thing and show the standard deviation with all of the points plotted I guess the y axis should be number of things but Im not sure about that.

 

[Tim L]

I have the footer of the subform set up to take the average and stdev of the filtered form. What I want to be able to do is ... graph the results of ... the cost on the bottom axis and the # of the things.

I've tried to understand you but just can't fathom out what you mean. Was I correct to remove 'take' and 'with' (respectively)? What has 'hash' got to do with the graph?

Also the graph need to show the classic bell shaped curve ... and show the standard deviation with all of the points plotted I guess the y axis should be number of things but Im not sure about that.

Plotting anything on a chart generally needs data, so if you want to plot a bell curve you will need to create some suitable data so that Access can plot it for you, I am not aware of any facility in Access (or Excel) that will do the work for you. The same goes for the standard deviation.

Sorry that I can't be any more help.

Tim

 

[jason2885]

Let me try to make clear what I am trying to do.

I have a query that was pulled from a table that has information about a certain operation. The information there included the individual name of the thing, contractor, area, zones, miles, dates, costs(7 different types)etc.

I then made a form in order to filter the data in the query to show results for specific contractors, areas, zones etc. i displayed the dates names miles and costs in a subform.

I then used the AVG and STDEV functions to get the average and standard deviation in the subform footer. This all worked fine.

Now I need to be able to make a graph of the things with respect to the various costs and see which of the ones are within a standard deviation of the mean. This will allow us to look more closely at the ones that are not to see what went wrong. I need someway though to make look correct for instance lets say the average is 5000. 5000 would be where the highest point on the graph and then the graph would fall off at the a rate and then this would determine the % of things that are inside the first standard deviation. I dont know if this made any more sense or not but thats what I am trying to get to. I need to be able to look at the graph and see the things that are not fitting into one standard deviation.

 

[Tim L]

This has got to be a quick (and short) reply as I have to go to work.

The Average and Standard Deviation are values that calculated on the whole set of numbers? If this is so then the numbers that the graph is using are currently the filtered numbers, as desired by your original query?

If I have got that straight then you need to create a query that "adds" the average and standard deviation figures to your filtered query. I put "adds" in quotes as the 'adding' should actually be 'includes', i.e: you need to 'add/'include' columns in your query for the average and standard deviation figures (the same would go for your bell curve values).

HTH

Tim