Pivot Chart - Change Sum of data to Average of data programatically

[EddiRae]

I am creating a pivot chart that right now creates the chDimValues using the SUM of the fields. I am needing to use the AVERAGE of the fields.

Has anyone done this using VB behind the scenes?

I cannot locate how to do this.

Thanks for your help
Eddi Rae

 

[Guus2005]

when it is a query, you can change it dynamically.

 

[EddiRae]

the data that I am pulling is multiple series, so I can't just change it dynamically.

the query is in detail, where the pivot pulls the detail to a summation and in this case an average of all the detail.

That is what I need to change programmatically.

For example ... this is for an ice cream plant
the filter field is the lines at the plant
the categories is the timeframe that they are looking at (weeks, months, years)
the series are the flavors of ice cream that they are making

in this instance, the data are speedloss values that I don't want to sum, I want to average for all the different flavors.

The user has the ability to make selections, and the average of speedloss needs to be one of them

 

[Guus2005]

Can you post the pivot query?

 

[EddiRae]

SELECT Format(tblAllDowntime!StartDate,"yyyy") AS StartYear, Format(Format(tblAllDowntime!StartDate,"mm"),"00") AS StartMonth, Format(Format(tblAllDowntime!StartDate,"ww"),"00") AS StartWeek, Format(tblAllDowntime!StartDate,"mm/dd/yyyy") AS StartDate, tblAllDowntime.[GPS Loss], tblAllDowntime.[Loss Type], tblAllDowntime.Loss, tblAllDowntime.Reason, tblAllDowntime.Line, tblAllDowntime.Occurances, tblAllDowntime.Minutes
FROM tblAllDowntime
ORDER BY Format(tblAllDowntime!StartDate,"yyyy"), Format(Format(tblAllDowntime!StartDate,"mm"),"00"), Format(Format(tblAllDowntime!StartDate,"ww"),"00"), Format(tblAllDowntime!StartDate,"mm/dd/yyyy"), tblAllDowntime.[GPS Loss], tblAllDowntime.[Loss Type], tblAllDowntime.Loss, tblAllDowntime.Reason, tblAllDowntime.Line;

 

[Guus2005]

This is not your pivot query. This is a simple select query with an order by. Not even a group by...

 

[EddiRae]

The query does NOT have to have a sum or group. I let the pivot chart do the grouping .... that is why I want to know how to change the sum to an average.

If you don't know how to do this programmatically, then please do not respond to this anymore.

 

[Guus2005]

So you don't have a query that needs to be changed but a chart. Be more specific in your questions next time.