-1

My query name: Total Daily Orders Summary

My Input [2 columns]: business_date,# Daily Orders

I need these 3 columns as calculated output: Orders# (7 days) Average, Orders# (30 Days) Average ,Orders# (Acuum Average) [i.e.from day one to date]

I have tried my best to find clear answers for how to do moving/rolling average in Microsoft Access query but unfortunately, I couldn't make it work for me. Therefore, I have decided to put my request here to see if someone will put me in the right direction to start working on my files and tasks.

Said Al-Hajri
  • 1
  • Does this answer your question? [Moving average query MS Access](https://stackoverflow.com/questions/45824087/moving-average-query-ms-access) – June7 Mar 10 '21 at 09:00

1 Answers1

0

I have trouble with correlated subqueries myself. here is a VBA/Access solution that I think is easier.

Start by adding a code module to your Database by clicking create on the menu and selecting module -which is all the way to the right. Then create a public function like:

Public Function AverageOrders(CurrentDate As Date, NumberofPriorDays As Integer) As Integer
AverageOrders = DSum("DailyOrders", "MyTable", "business_date BETWEEN #" & CurrentDate & "# AND #" & DateAdd("d", -NumberofPriorDays, CurrentDate) & "#")
End Function

we create the function this way so access intellisense works. This is a powerful technique which I also use to wrap any calls to access form parameters. To find the function in a query right click on the top of a query column and choose build. In the build wizard under expression elements select functions then your database. Average Orders and any other public functions you make will appear. Or just type it.

then you get:

enter image description here

enter image description here

February has 28 days. We don't use # for DateOrders because In strings # denotes a date. To get the accumulated average just pick an absurdly early date or make another function without [NumberofPriorDays].

mazoula
  • 1,221
  • 2
  • 11
  • 20
  • Thanks a lot mazoula for your time and help. I have tried to do exactly what you explain in your answer. But unfortunately I keep receiving this message "Undefined function 'AverageOrders' in expression. – Said Al-Hajri Mar 11 '21 at 12:00
  • I get that error when I forget to put the public in front of AverageOrders. Check to see if you remembered to put public in front of your function. – mazoula Mar 12 '21 at 05:42
  • this could also happen if you accidentally created a class module rather than a normal module. – mazoula Mar 12 '21 at 05:45