A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Running average - subquery?



 
 
Thread Tools Display Modes
  #1  
Old March 3rd, 2010, 07:57 PM posted to microsoft.public.access.queries
Maurice
external usenet poster
 
Posts: 1,585
Default Running average - subquery?

Hi Guys,

Been struggling a bit on this so a pointer would be great.
Just a simple example would do..

tableA with two fields "Startdate" and "Amount"

now i need a running average in the query which would give me the following
output:

[Startdate] - [amount] - [average]
1-1-2010 - 100 - 100
2-1-2010 - 50 - 75
3-1-2010 - 60 - 70

So in the average column the last value should be the average of the sum of
the previous records and the current records (being the average of the total).

I know that the fieldnames are incorrect they are just for illustrating the
question.
any pointers appreciated.

--
Maurice Ausum
  #2  
Old March 3rd, 2010, 08:33 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Running average - subquery?

Maurice wrote:
Been struggling a bit on this so a pointer would be great.
Just a simple example would do..

tableA with two fields "Startdate" and "Amount"

now i need a running average in the query which would give me the following
output:

[Startdate] - [amount] - [average]
1-1-2010 - 100 - 100
2-1-2010 - 50 - 75
3-1-2010 - 60 - 70

So in the average column the last value should be the average of the sum of
the previous records and the current records (being the average of the total).



You can use a subquery to calculate the running average
field:
Average: (SELECT Avg(X.amount) FROM table As X
WHERE X.Startdate = table.startdate)

--
Marsh
MVP [MS Access]
  #3  
Old March 3rd, 2010, 08:49 PM posted to microsoft.public.access.queries
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Running average - subquery?

Maurice -

Try this:

SELECT TableA.Startdate, TableA.amount, (SELECT Sum(amount) from TableA AS
T1 WHERE T1.Startdate = TableA.Startdate)/(SELECT Count(amount) from TableA
AS T1 WHERE T1.Startdate = TableA.Startdate) AS RunningAvg
FROM TableA
ORDER BY TableA.Startdate;

--
Daryl S


"Maurice" wrote:

Hi Guys,

Been struggling a bit on this so a pointer would be great.
Just a simple example would do..

tableA with two fields "Startdate" and "Amount"

now i need a running average in the query which would give me the following
output:

[Startdate] - [amount] - [average]
1-1-2010 - 100 - 100
2-1-2010 - 50 - 75
3-1-2010 - 60 - 70

So in the average column the last value should be the average of the sum of
the previous records and the current records (being the average of the total).

I know that the fieldnames are incorrect they are just for illustrating the
question.
any pointers appreciated.

--
Maurice Ausum

  #4  
Old March 3rd, 2010, 08:55 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Running average - subquery?

Try this with your table and field names --
SELECT Aaron.SalesDate, Aaron.Qty, (SELECT Sum([XX].Qty)/ Count([XX].QTY)
FROM Aaron AS [XX] WHERE [XX].SalesDate = Aaron.SalesDate) AS [Average]
FROM Aaron
ORDER BY Aaron.SalesDate;

--
Build a little, test a little.


"Maurice" wrote:

Hi Guys,

Been struggling a bit on this so a pointer would be great.
Just a simple example would do..

tableA with two fields "Startdate" and "Amount"

now i need a running average in the query which would give me the following
output:

[Startdate] - [amount] - [average]
1-1-2010 - 100 - 100
2-1-2010 - 50 - 75
3-1-2010 - 60 - 70

So in the average column the last value should be the average of the sum of
the previous records and the current records (being the average of the total).

I know that the fieldnames are incorrect they are just for illustrating the
question.
any pointers appreciated.

--
Maurice Ausum

  #5  
Old March 3rd, 2010, 10:00 PM posted to microsoft.public.access.queries
Maurice
external usenet poster
 
Posts: 1,585
Default Running average - subquery?

Daryl, Karl and Marsh,

Thanx for the reponse and pointers, got it up and running as i would like it
to be.
much appreciated :-)
--
Maurice Ausum


"Marshall Barton" wrote:

Maurice wrote:
Been struggling a bit on this so a pointer would be great.
Just a simple example would do..

tableA with two fields "Startdate" and "Amount"

now i need a running average in the query which would give me the following
output:

[Startdate] - [amount] - [average]
1-1-2010 - 100 - 100
2-1-2010 - 50 - 75
3-1-2010 - 60 - 70

So in the average column the last value should be the average of the sum of
the previous records and the current records (being the average of the total).



You can use a subquery to calculate the running average
field:
Average: (SELECT Avg(X.amount) FROM table As X
WHERE X.Startdate = table.startdate)

--
Marsh
MVP [MS Access]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:57 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.