468,161 Members | 1,993 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,161 developers. It's quick & easy.

Running average for weekly Dollars

I am looking for a way to make a query / report display the running
average for total dollars.

I have already set up a query to provide totals dollars per day from
which a report graphly shows the dollars per week. How do I then take
the dollars and get a running average for the year?

- Randy

Mar 9 '07 #1
3 3724
mo*******@gmail.com wrote:
I am looking for a way to make a query / report display the running
average for total dollars.

I have already set up a query to provide totals dollars per day from
which a report graphly shows the dollars per week. How do I then take
the dollars and get a running average for the year?

- Randy
If you are making a report, perhaps add a field that does a running sum.

If you are running a query, write a function.
Mar 10 '07 #2
In article <1173480840.990085.185300
@q40g2000cwq.googlegroups.com>, mo*******@gmail.com says...
I am looking for a way to make a query / report display the running
average for total dollars.

I have already set up a query to provide totals dollars per day from
which a report graphly shows the dollars per week. How do I then take
the dollars and get a running average for the year?

- Randy

You may have gotten the help you needed, but your question
seemed challenging to me and so here are some queries for
Northwind. Name the queries as I have them named. If nothing
else, it was a learning experience for me. Watch out for line
wrapping.

Northwind Query: Daily Sales Totals
--------------------------------------------
SELECT Orders.OrderDate,
SUM([Order Subtotals].Subtotal) AS [Daily Sales]
FROM Orders
INNER JOIN [Order Subtotals]
ON Orders.OrderID = [Order Subtotals].OrderID
WHERE Orders.Orderdate BETWEEN [Enter start date:]
AND [Enter end date:]
GROUP BY Orders.OrderDate;

Northwind Query: Weekly Sales Totals
--------------------------------------------
SELECT SUM(d.[Daily Sales]) AS [Weekly Sales],
COUNT(d.OrderDate) AS Weekdays,
CCUR(SUM(d.[Daily Sales]) / COUNT(d.OrderDate)) AS
[Daily Average],
DATEADD("ww",DATEDIFF("ww",0,[d.OrderDate]),0) AS
[Week Ending]
FROM [Daily Sales Totals] AS d
GROUP BY DATEADD("ww",DATEDIFF("ww",0,[d.OrderDate]),0);

Northwind Query: Weekly Running Sales
-------------------------------------------
SELECT [Weekly Sales Totals].[Week Ending],
[Weekly Sales Totals].[Weekly Sales],
[Weekly Sales Totals].[Daily Average],
CCUR((SELECT SUM(a.[Weekly Sales])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly Sales
Totals].[Week Ending])
/ (SELECT SUM(a.[Weekdays])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly
Sales Totals].[Week Ending]))
AS [YTD Daily Average],
CCUR((SELECT SUM(a.[Weekly Sales])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly Sales
Totals].[Week Ending])
/ (SELECT SUM(a.[Weekdays])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly Sales
Totals].[Week Ending]))
* [Weekly Sales Totals].[Weekdays]
AS [YTD Weekly Average],
(SELECT SUM(a.[Weekly Sales])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly Sales
Totals].[Week Ending])
AS [YTD Sales],
(SELECT SUM(a.[Weekdays])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly Sales
Totals].[Week Ending])
AS [Running Days]
FROM [Weekly Sales Totals]
GROUP BY [Weekly Sales Totals].[Weekly Sales],
[Weekly Sales Totals].[Daily Average],
[Weekly Sales Totals].[Week Ending],
[Weekly Sales Totals].Weekdays
ORDER BY [Weekly Sales Totals].[Week Ending];
Mar 12 '07 #3
On Mar 11, 8:07 pm, Mike Gramelspacher <grame...@psci.netwrote:
In article <1173480840.990085.185300

Northwind Query: Weekly Running Sales
-------------------------------------------
SELECT [Weekly Sales Totals].[Week Ending],
[Weekly Sales Totals].[Weekly Sales],
[Weekly Sales Totals].[Daily Average],
CCUR((SELECT SUM(a.[Weekly Sales])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly Sales
Totals].[Week Ending])
/ (SELECT SUM(a.[Weekdays])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly
Sales Totals].[Week Ending]))
AS [YTD Daily Average],
CCUR((SELECT SUM(a.[Weekly Sales])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly Sales
Totals].[Week Ending])
/ (SELECT SUM(a.[Weekdays])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly Sales
Totals].[Week Ending]))
* [Weekly Sales Totals].[Weekdays]
AS [YTD Weekly Average],
(SELECT SUM(a.[Weekly Sales])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly Sales
Totals].[Week Ending])
AS [YTD Sales],
(SELECT SUM(a.[Weekdays])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly Sales
Totals].[Week Ending])
AS [Running Days]
FROM [Weekly Sales Totals]
GROUP BY [Weekly Sales Totals].[Weekly Sales],
[Weekly Sales Totals].[Daily Average],
[Weekly Sales Totals].[Week Ending],
[Weekly Sales Totals].Weekdays
ORDER BY [Weekly Sales Totals].[Week Ending];
In this query computation of YTD Weekly Average is incorrect. It
should be:

CCUR((SELECT SUM(a.[Weekly Sales])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly Sales Totals].[Week
Ending])
/ (SELECT COUNT(a.[Week Ending])
FROM [Weekly Sales Totals] AS a
WHERE [a].[Week Ending] <= [Weekly Sales Totals].[Week
Ending]))
AS [YTD Weekly Average]

Mar 12 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Emile van Sebille | last post: by
2 posts views Thread by Shum | last post: by
3 posts views Thread by paeh | last post: by
21 posts views Thread by Bill Cunningham | last post: by
1 post views Thread by gcdp | last post: by
reply views Thread by kamranasdasdas | last post: by
reply views Thread by gcreed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.