Average in Query

[RMires]

I have a query that collects temperature reading off of several pipelines.
For example:

Pipeln1 86
Pipeln1 88
Pipelin2 67
Pipelin2 63

I need an average for each pipline:

Pipeln1 87
Pipeln2 65

Can I do this without creating a separate query for each pipeline?

 

[John Spencer]

You should be able to.

SELECT [PipelineName], Avg([Reading]) as TheAverage
FROM [YourTable]
GROUP BY [PipelineName]

If you can only use query design view (query grid)
-- Open a new query
-- Select your table
-- Add the Pipeline name field and the reading field
-- Select View: Totals from the menu
-- Change GROUP BY to AVG under the reading field.
-- Run the query

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jeff Boyce]

How depends on what.

Are those separate fields in your underlying table(s)?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[RMires]

I did that and it worked when it was just one pipeline, but when I added the
others it gave me each temperature reading instead of an average.

You should be able to.

SELECT [PipelineName], Avg([Reading]) as TheAverage
FROM [YourTable]
GROUP BY [PipelineName]

If you can only use query design view (query grid)
-- Open a new query
-- Select your table
-- Add the Pipeline name field and the reading field
-- Select View: Totals from the menu
-- Change GROUP BY to AVG under the reading field.
-- Run the query

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a query that collects temperature reading off of several pipelines.
For example:

Pipeln1 86
Pipeln1 88
Pipelin2 67
Pipelin2 63

I need an average for each pipline:

Pipeln1 87
Pipeln2 65

Can I do this without creating a separate query for each pipeline?

 

[RMires]

"Pipeline" is one field and "Temperature" is another field. I am using
"Temperature" in a function in a separate field in the same query. Could that
cause a problem?

 

[RMires]

That appears to be the problem. The function is using each temperature
instead of the "Averageof" Temperature. How do I get the function to use the
Average? I am using the field name as the variable in the function. What do I
call it to use the average of the field as the variable?
"AverageofTemperature"?

 

[John Spencer]

That should work IF your table structure is like the following

Table: PipelineReadings
Field: PipelineName
Field: PipelineReading

If you have a structure something more like this:
Field: PipelineName1
Field: PipelineReading1
Field: PipelineName2
Field: PipelineReading2
Field: PipelineName3
Field: PipelineReading3

Then you have a faulty table structure and will need to do something
different. So first, post your table structure.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I did that and it worked when it was just one pipeline, but when I added the
others it gave me each temperature reading instead of an average.

You should be able to.

SELECT [PipelineName], Avg([Reading]) as TheAverage
FROM [YourTable]
GROUP BY [PipelineName]

If you can only use query design view (query grid)
-- Open a new query
-- Select your table
-- Add the Pipeline name field and the reading field
-- Select View: Totals from the menu
-- Change GROUP BY to AVG under the reading field.
-- Run the query

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a query that collects temperature reading off of several pipelines.
For example:

Pipeln1 86
Pipeln1 88
Pipelin2 67
Pipelin2 63

I need an average for each pipline:

Pipeln1 87
Pipeln2 65

Can I do this without creating a separate query for each pipeline?

 

[Dale Fye]

Or you could use a sub-query; something like:

SELECT Temp.[Pipeline],
Temp.[AvgOfTemp],
fnWhatever(Temp.[AvgOfTemp]) as FnValue
FROM (SELECT [Pipeline], Avg([Temperature]) as AvgOfTemp
FROM [yourTable]
GROUP BY [Pipeline]) as Temp

Bewhere, when this gets compiled and saved, Access will save it like:

SELECT Temp.[Pipeline],
Temp.[AvgOfTemp],
fnWhatever(Temp.[AvgOfTemp]) as FnValue
FROM [SELECT [Pipeline], Avg([Temperature]) as AvgOfTemp
FROM [yourTable]
GROUP BY [Pipeline]]. as Temp

If you then go back and make changes to it in the query grid, Access will
generate an error message. If that happens, go to the SQL view, and change
it so that the subquery (everything after the first FROM and before the ". as
Temp" is wrapped in parenthesis () not brackets [ ].

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.