Avg function

[Guest]

I would like to calculate a running average for a column, [FuelBTU] . This
column is numeric and values are entered weekly. I would like to calculate
from starting week(March 10, 2006) to present. Please help! thanks

 

[Guest]

Use a subquery in the outer query's SELECT clause so that it returns the
average for all rows up to and including the outer query's current row, e.g.

SELECT [Starting Week], [FuelBTU],
(SELECT AVG([FuelBTU])
FROM [YourTable] AS T2
WHERE T2.[Starting Week] >= #03/10/2006#
AND T2.[Starting Week] <= T1.[Starting Week])
AS [Average BTU]
FROM [YourTable] AS T1
WHERE [Starting Week] >= #03/10/2006#
ORDER BY [Starting Week];

Ken Sheridan
Stafford, England

 

[Guest]

Ken, here is the subquery as entered. However I keep getting a syntax
error.
Can you pls look it over and tell me where I messed up. thanks again


Expr1: (SELECT [Week], [FuelBTU],
(SELECT AVG([FuelBTU])
FROM [2 Main input] AS T2
WHERE T2.[Week] >= #03/10/2006#
AND T2.[Week] <= T1.[Week])
AS [Average BTU]
FROM [2 Main input] AS T1
WHERE [Week] >= #03/10/2006#
ORDER BY [Week])
--
Eddie Z

Use a subquery in the outer query's SELECT clause so that it returns the
average for all rows up to and including the outer query's current row, e.g.

SELECT [Starting Week], [FuelBTU],
(SELECT AVG([FuelBTU])
FROM [YourTable] AS T2
WHERE T2.[Starting Week] >= #03/10/2006#
AND T2.[Starting Week] <= T1.[Starting Week])
AS [Average BTU]
FROM [YourTable] AS T1
WHERE [Starting Week] >= #03/10/2006#
ORDER BY [Starting Week];

Ken Sheridan
Stafford, England

I would like to calculate a running average for a column, [FuelBTU] . This
column is numeric and values are entered weekly. I would like to calculate
from starting week(March 10, 2006) to present. Please help! thanks

 

[Guest]

The SQL statement is the entire query, not the subquery. The subquery is the
parenthesised SELECT statement within it. Open the query in design view,
then switch to SQL view and paste in the following over everything that's
there:

SELECT [Week], [FuelBTU],
(SELECT AVG([FuelBTU])
FROM [2 Main input] AS T2
WHERE T2.[Week] >= #03/10/2006#
AND T2.[Week] <= T1.[Week])
AS [Average BTU]
FROM [2 Main input] AS T1
WHERE [Week] >= #03/10/2006#
ORDER BY [Week];

You can add any other columns you want returned either by typing them into
outer query's SELECT clause in SQL view or by switching back to desgn view
and adding then there.

I'm assuming the Week column is of date/time data type.

Ken Sheridan
Stafford, England

Ken, here is the subquery as entered. However I keep getting a syntax
error.
Can you pls look it over and tell me where I messed up. thanks again


Expr1: (SELECT [Week], [FuelBTU],
(SELECT AVG([FuelBTU])
FROM [2 Main input] AS T2
WHERE T2.[Week] >= #03/10/2006#
AND T2.[Week] <= T1.[Week])
AS [Average BTU]
FROM [2 Main input] AS T1
WHERE [Week] >= #03/10/2006#
ORDER BY [Week])
--
Eddie Z

Use a subquery in the outer query's SELECT clause so that it returns the
average for all rows up to and including the outer query's current row, e.g.

SELECT [Starting Week], [FuelBTU],
(SELECT AVG([FuelBTU])
FROM [YourTable] AS T2
WHERE T2.[Starting Week] >= #03/10/2006#
AND T2.[Starting Week] <= T1.[Starting Week])
AS [Average BTU]
FROM [YourTable] AS T1
WHERE [Starting Week] >= #03/10/2006#
ORDER BY [Starting Week];

Ken Sheridan
Stafford, England

I would like to calculate a running average for a column, [FuelBTU] . This
column is numeric and values are entered weekly. I would like to calculate
from starting week(March 10, 2006) to present. Please help! thanks

 

[Guest]

Ken, it worked perfectly. Thank you very much for your help.
--
Eddie Z

The SQL statement is the entire query, not the subquery. The subquery is the
parenthesised SELECT statement within it. Open the query in design view,
then switch to SQL view and paste in the following over everything that's
there:

SELECT [Week], [FuelBTU],
(SELECT AVG([FuelBTU])
FROM [2 Main input] AS T2
WHERE T2.[Week] >= #03/10/2006#
AND T2.[Week] <= T1.[Week])
AS [Average BTU]
FROM [2 Main input] AS T1
WHERE [Week] >= #03/10/2006#
ORDER BY [Week];

You can add any other columns you want returned either by typing them into
outer query's SELECT clause in SQL view or by switching back to desgn view
and adding then there.

I'm assuming the Week column is of date/time data type.

Ken Sheridan
Stafford, England

Ken, here is the subquery as entered. However I keep getting a syntax
error.
Can you pls look it over and tell me where I messed up. thanks again


Expr1: (SELECT [Week], [FuelBTU],
(SELECT AVG([FuelBTU])
FROM [2 Main input] AS T2
WHERE T2.[Week] >= #03/10/2006#
AND T2.[Week] <= T1.[Week])
AS [Average BTU]
FROM [2 Main input] AS T1
WHERE [Week] >= #03/10/2006#
ORDER BY [Week])
--
Eddie Z

Use a subquery in the outer query's SELECT clause so that it returns the
average for all rows up to and including the outer query's current row, e.g.

SELECT [Starting Week], [FuelBTU],
(SELECT AVG([FuelBTU])
FROM [YourTable] AS T2
WHERE T2.[Starting Week] >= #03/10/2006#
AND T2.[Starting Week] <= T1.[Starting Week])
AS [Average BTU]
FROM [YourTable] AS T1
WHERE [Starting Week] >= #03/10/2006#
ORDER BY [Starting Week];

Ken Sheridan
Stafford, England

:

I would like to calculate a running average for a column, [FuelBTU] . This
column is numeric and values are entered weekly. I would like to calculate
from starting week(March 10, 2006) to present. Please help! thanks