473,287 Members | 1,650 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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 4115
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Emile van Sebille | last post by:
QOTW: "Oddly enough it is in Python that I have had the most fun programming. It is in Python that I find myself not only the most expressive but the most elegant in my programming. In Python my...
0
by: Cameron Laird | last post by:
QOTW: "They say that when you have a hammer, everything looks like a nail. When using Python, most everything /is/ a nail." -- Josiah Carlson "This is the main reason I stick to Tkinter. I've...
1
by: John J Lee | last post by:
QOTW: "The site that I worked on spent TWO MILLION U.S. DOLLARS on its web server hardware. OK, it used Java servlets that are even slower than Python, but you have to understand that there's a...
16
by: Cameron Laird | last post by:
QOTW: "I found the discussion of unicode, in any python book I have, insufficient." -- Thomas Heller "If you develop on a Mac, ... Objective-C could come in handy. . . . PyObjC makes mixing...
8
by: nickdu | last post by:
I'm trying to isolate "applications" into their own application domain within a single process. I've quoted applications because it's a logical representation of an application. Basically it...
2
by: Shum | last post by:
Hi ... I'm given the task of keeping a running average of number of patients over 20 days, and plotting it in a graph.. What is basically running average? is it simple average or what.. ? and how...
3
by: paeh | last post by:
hello..can anyone help me. I am beginner in programming. I need to make a system that can calculate moving average. my system process will be executed according to certain schedule such as daily,...
0
by: SuzK | last post by:
I am trying to calculate in VBA in Access 2002 a moving average and update a table with the calculations. Fields in my WeeklyData table are Week Ending (date) ItemNbr (double) Sales Dollars...
21
by: Bill Cunningham | last post by:
I have create these 2 files. Called main.c and atr.c. They seem to work pretty well. I just wanted to submit them to see what if any errors others that know more might find. Thanks. atr.c ...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.