snapper1
asked on
Moving average microsoft access
I want to calculate a variety of moving averages in microsoft access where I have a table with the columns
Date..Transaction Date in the Microsoft example below
Product descriptuon
Price sold...Currency in the microsoft example below
Microsoft gives an example of a module at the web address below but it only works some of the time and also has problems when i start a new month..all of which suggests I have a problem with my dates which are set at Date/Time Fomat Short Date
http://support.microsoft.com/?id=210138 which I have pasted in part below
Option Compare Database
'************************* ********** ********** ********** ******
'Declarations section of the module.
'************************* ********** ********** ********** ******
Option Explicit
'========================= ========== ========== ========== ========
' The following function MovAvg computes moving averages based on
' a table with a multiple-field primary key.
'========================= ========== ========== ========== ========
Function MovAvg(currencyType, startDate, period As Integer)
Dim rst As DAO.Recordset
Dim sql As String
Dim ma As Currency
Dim n As Integer
sql = "Select * from table1 "
sql = sql & "where currencyType = '" & currencyType & "'"
sql = sql & " and transactiondate <= #" & startDate & "#"
sql = sql & " order by transactiondate"
Set rst = CurrentDb.OpenRecordset(sq l)
rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
MovAvg = 0
Exit Function
Else
ma = ma + rst.Fields("rate")
End If
rst.MovePrevious
Next n
rst.Close
MovAvg = ma / period
End Function
I am not skilled in SQL but can understand part of what is written and create by cut and paste the suggested solution and use it
but as I said it only works part of the time appearing in short period moving averages ( say 5 days) to strike problem as the date
is dropping toward the 12 th of a month the prior 5 days moving average starts to come in error suggesting that as you move from double digit to single digit dates something doesnt work right
I also have problem if my period exceeds about 23 days as I always get a zero on the 1st of the month and sometimes the 2nd
Any help in understanding why I have these problems greatly appreciated
Date..Transaction Date in the Microsoft example below
Product descriptuon
Price sold...Currency in the microsoft example below
Microsoft gives an example of a module at the web address below but it only works some of the time and also has problems when i start a new month..all of which suggests I have a problem with my dates which are set at Date/Time Fomat Short Date
http://support.microsoft.com/?id=210138 which I have pasted in part below
Option Compare Database
'*************************
'Declarations section of the module.
'*************************
Option Explicit
'=========================
' The following function MovAvg computes moving averages based on
' a table with a multiple-field primary key.
'=========================
Function MovAvg(currencyType, startDate, period As Integer)
Dim rst As DAO.Recordset
Dim sql As String
Dim ma As Currency
Dim n As Integer
sql = "Select * from table1 "
sql = sql & "where currencyType = '" & currencyType & "'"
sql = sql & " and transactiondate <= #" & startDate & "#"
sql = sql & " order by transactiondate"
Set rst = CurrentDb.OpenRecordset(sq
rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
MovAvg = 0
Exit Function
Else
ma = ma + rst.Fields("rate")
End If
rst.MovePrevious
Next n
rst.Close
MovAvg = ma / period
End Function
I am not skilled in SQL but can understand part of what is written and create by cut and paste the suggested solution and use it
but as I said it only works part of the time appearing in short period moving averages ( say 5 days) to strike problem as the date
is dropping toward the 12 th of a month the prior 5 days moving average starts to come in error suggesting that as you move from double digit to single digit dates something doesnt work right
I also have problem if my period exceeds about 23 days as I always get a zero on the 1st of the month and sometimes the 2nd
Any help in understanding why I have these problems greatly appreciated
ASKER
Attached is a file I was trying to work off..I am from Australia so my dates 12/11/2010 mean 12th November 2010 etc
Essentially for each date say 12 November ,11/11/2010 etc I am trying to calculate a moving average for the codes in product type using the prices in Rate ie Product type TAH has a price for 12/11/2010 of 7.29 and on 11/11/2010 of 7.36 so a 2 day average would be 7.32 and for 11/11/2010 it would be 7.345 as price on 10/11/2010 is 7.33
I am interested in being able to have several moving averages such as 15, 30 ,60 day being days listed as distinct from calendar days as there is no data for weekend days and would probably use a make table query to output it to another table.
I would like to be able to vary the time of the moving averages
I hope this is a little clearer
Database2.accdb
Essentially for each date say 12 November ,11/11/2010 etc I am trying to calculate a moving average for the codes in product type using the prices in Rate ie Product type TAH has a price for 12/11/2010 of 7.29 and on 11/11/2010 of 7.36 so a 2 day average would be 7.32 and for 11/11/2010 it would be 7.345 as price on 10/11/2010 is 7.33
I am interested in being able to have several moving averages such as 15, 30 ,60 day being days listed as distinct from calendar days as there is no data for weekend days and would probably use a make table query to output it to another table.
I would like to be able to vary the time of the moving averages
I hope this is a little clearer
Database2.accdb
For Access SQL you will need to convert the dates to US format. eg mmddyyyy
see http://www.mvps.org/access/queries/qry0009.htm
Cheers
JC
see http://www.mvps.org/access/queries/qry0009.htm
Cheers
JC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much www.adit had the answer on how to convert my au dates to Us dates and then things worked fine
Date
Dates in SQL statements must be in US (United States) short format, and surrounded by the # symbol. For example:
SQL = “Select * from Employee where StartDate >= #” & _
Format(StrtDate, “MM/DD/YY”) & “#”
except i used mm/dd/yyyy
Date
Dates in SQL statements must be in US (United States) short format, and surrounded by the # symbol. For example:
SQL = “Select * from Employee where StartDate >= #” & _
Format(StrtDate, “MM/DD/YY”) & “#”
except i used mm/dd/yyyy
ASKER
defined problem as date issue and gave me a solution with some good articles to rad
Please restate what you are trying to do, and work up a numerical example. Posting a sample MDB file would be best of all.
In any event, you probably do NOT need VBA to get a rolling average; a normal SQL statement will probably do the job, and more quickly at that. Here are two quick examples, using the attached file:
5 day rolling average, by product, for which all days get a rolling average value:
Open in new window
5 day rolling average, by product, for rolling average is null if there are fewer than 5 days:
Open in new window
Cheers,
Patrick
Q-26614461.mdb