By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,334 Members | 2,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,334 IT Pros & Developers. It's quick & easy.

Running average for weekly Dollars

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.