470,564 Members | 2,424 Online

# 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 3847 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
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

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],
[Week Ending]
FROM [Daily Sales Totals] AS d

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.