Solved

Moving-average for nested tables in Access 2013

Posted on 2016-09-25
Medium Priority
55 Views
Hello,

I have a table which resembles the following:

-------------------------------------------
-------------------------------------------
1       | 1/1/2014 |    \$25.00
2       | 1/1/2014 |    \$29.40
3       | 1/1/2014 |    \$50.80
4       | 2/1/2014 |    \$16.70
5       | 3/1/2014 |    \$48.50
6       | 3/1/2014 |    \$34.70
.
.

I intend to calculate the 5-day moving average for the above data. My SQL code in Access 2013 resembles the following:

SELECT Avg(B2.CostSum)
FROM
(SELECT U3.BuyDate as BDate, SUM(B3.Cost) as CostSum
FROM Bought AS B3
WHERE
GROUP BY B2.BDate
) AS [5-day_MovAvg]
FROM Bought AS B1

However, when I tried to run the code, a prompt popped-up in Access, stating that at most only one record can be returned.

Please advise on how I may be able to do this. My desired SQL output would be as follows:

--------------------------------------------------------
--------------------------------------------------------
1/1/2014 |   \$105.20 |  \$105.20
2/1/2014 |    \$16.70 |  \$60.95
3/1/2014 |    \$83.20 |  \$68.37
.
.
.

Thanks.
0
Question by:sz14
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2

LVL 51

Assisted Solution

Gustav Brock earned 1000 total points
ID: 41815433
Perhaps this will do:
SELECT
SUM(B1.Costs) AS Total_Costs,
(SELECT Avg(CostSum)
FROM
(SELECT SUM(B3.Costs) AS CostSum
FROM Bought AS B3
[5-day_MovAvg]
FROM
Bought AS B1
GROUP BY
/gustav
0

LVL 22

Assisted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 1000 total points
ID: 41815435
try something like this:
(
SELECT Avg(b2.Costs)
FROM Bought AS B2
WHERE
) AS [MovAvg5Day]
FROM Bought AS B1
0

Author Comment

ID: 41815821
Hi Gustav & Crystal,

Firstly, thank you very much for your prompt replies to my query. :)

Gustav, I have attempted to run your solution in Access 2013, but a prompt appears when I attempt to run this query, requesting for a parameter value to be entered for B1.BuyDate. This is strange as Access usually prompts the user for this input if the variable was not defined prior to execution of the SQL code, so Access prompts the user for a value to be entered at run-time.

Crystal, I have also tried to run your code in Access 2013. It seems that the MovAvg values are obtained by averaging the values for the day. So, for instance, the total costs for 1/1/2014 is \$105.20, but the moving average was calculated to be \$35.20 (which is incorrect, as it should be averaging the total sums on a daily basis, and not for individual entries in the table). Please see the attached screenshots for what happens when the code is run in Access 2013.

Thanks.

(P.S. BTW, there are a couple of typos in my question posted above:
1. The header for the third field of the table should read Costs, and not Cost.

I sincerely apologize for the typos. )
Crystal.JPG
Gustav.JPG
0

LVL 51

Expert Comment

ID: 41815828

/gustav
0

Accepted Solution

sz14 earned 0 total points
ID: 41815836
Hi Gustav & Crystal,

I have modified the code so that it now gives the correct moving average on a daily basis.

The code is as shown below (for future reference by anyone who intends to do this using SQL in Access):

SELECT Avg(B2.Total_Cost)
FROM
FROM Bought as B3
WHERE
) AS MovAvg5Day
FROM Bought AS B1

Thanks for your time & response to the query.

:)
0

LVL 22

Expert Comment

ID: 41816438
0

Author Closing Comment

ID: 41824649
The final solution which I provided solves the problem, but kudos to Gustav & Crystal who have provided a nudge in the right direction (through their code).
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month13 days, 12 hours left to enroll