Chart query (1 Viewer)

[mdcory]

I am trying to make a query for a chart that includes the following fields from a table tblMetals:

AnalysisDate
AnalysisName
SampleName
ReptSmpmg
ReptBkgmg

I need to do some calculations for the chart as follows:

Result= ([ReptSmpmg]-[ReptBkgmg])*100/1
Average= Avg(Result)
Std= StDev(Result)
plus2= Average+(2*Std)
plus3= Average+(3*Std)
minus2= Average-(2*Std)
minus3= Average-(3*Std)

And I need to plot the Result, Average, plus2, plus3, minus2 and minus3 in the line chart.

When I put anything like the above in the query I get an error about not including them in an aggregate. I have tried all kinds of variations of queries but no go.

Does anyone have any suggestions on how to get this to chart? It has been driving me crazy for over a week now.

From what I have read it looks like maybe it can be done with VB but I have very limited experiance with VB. I have also tried doing it with excel and opening it with access but I had problems with it to with updating correctly and others.

Thanks for any help.
Matt

 

[Jon K]

I need to do some calculations for the chart as follows:

Result= ([ReptSmpmg]-[ReptBkgmg])*100/1
Average= Avg(Result)
Std= StDev(Result)
plus2= Average+(2*Std)
plus3= Average+(3*Std)
minus2= Average-(2*Std)
minus3= Average-(3*Std)

And I need to plot the Result, Average, plus2, plus3, minus2 and minus3 in the line chart.

I don't think you can build such a query as Result will return multiple rows whereas Average, Std etc will return only one row.

In other words, you are trying to build a query which is partly a Select Query and partly a Totals Query.
.