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];
