By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,130 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Calculating Weighted Average in Access

P: 3
Hello Everyone,

I am currently trying to create a 3 month running wieghted average to forecast the comming months.

My Table consits of 3 colums (Table ID), (Date),(Monthly Total). (Table ID) contains the numbers 1 to 31, (Date) runs from 1/1/2005 to 7/1/2007.

What I need to do is make a query that will add up the last three months and calculate the weighted average. That average would be the forecasted value for 8/1/2007. This is where it gets tricky, I need it to grab that newly forecasted number for 8/1/2007 and combine it with the numbers from 6/1/2007 and 7/1/2007 to forecast 9/1/2007. This process would need to repeat till the end of 2008.

I am also open to hearing any other forecasting methods I could use using the data that I have contained in my Table.
Aug 31 '07 #1
Share this Question
Share on Google+
10 Replies


Scott Price
Expert 100+
P: 1,384
Calculating a simple average is the easiest part. The following is a sample from one of my databases:
Expand|Select|Wrap|Line Numbers
  1. =DAvg("PersonCountAct","tblHistory","MealID = " & [MealID] & " AND Year BETWEEN " & [Year]-1 & " AND " & [Year]-10)
This is a simple average that I have in my database to help me make projections for the future. I use this function for a ten year simple average, a five year simple average and then display last year's figure to project this years' number. This would need very little changing to fit your data.

However, for your weighted average, you are likely looking for something more along the lines of MS Excel's Forecast or Trend feature, which does some more sophisticated statistical analysis than a simple average. You can try your hand at calling Excel functions from within VBA/Access like this link discusses: http://support.microsoft.com/default...b;en-us;198571. I plan on trying this myself soon, but haven't yet had time to do so :-(

You can use a Pivot Chart to do some statistical analysis, but in my stumbling attempts to be able to capture a number returned by the chart I couldn't seem to get it to work. I could get the chart to display like I wanted, but couldn't figure out how the get the db to read the projected number and store it for further use. It's very possible that someone else here knows how to do this, but I just never had any luck!

Alternatively, you can export your data to an Excel spreadsheet, perform the Forecast or Trend function there and then import it back in.

Hope this helps out a bit...

Regards,
Scott
Sep 1 '07 #2

Scott Price
Expert 100+
P: 1,384
Well, unless you're still running a version of A2000, you're probably out of luck on that link I gave you...After I examined it more I remembered that the .Application extension is one of the things that MS decided has too many security risks, and so is disabled in A2003. This link here discusses this: http://www.databasedev.co.uk/jet-sandbox-mode.html

Regards,
Scott
Sep 1 '07 #3

Scott Price
Expert 100+
P: 1,384
Ok! Your post provoked me to have another go at this... Here is the code for what I got to work. Unfortunately I'm going to be out of town until Weds, but someone else here can jump in and help you out if you run into problems with this before I get back. (Anyone is free to suggest improvements in this code as well! It may not be the most efficient, but it's working for me :-)

You'll need a query to pull the data you wish to use in forecasting. I'm using one with this SQL:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW tblHistory.ConvID, tblHistory.Year, tblHistory.PersonCountAct, tblHistory.MealID
  2. FROM tblHistory
  3. WHERE (((tblHistory.ConvID)=1) AND ((tblHistory.Year)>=(DatePart("yyyy","July 30")-5)) AND ((tblHistory.PersonCountAct)<>0) AND ((tblHistory.MealID)=1))
  4. ORDER BY tblHistory.Year DESC;
  5.  
While this query is open, you will call the function from another query, a control on a form, or report, etc. This is the code for the actual function:
Expand|Select|Wrap|Line Numbers
  1. Public Function xlForeCast() As Double
  2.  
  3. Dim MyDate As Integer 'Will be the point for which you are forecasting, in this case 2007
  4. Dim MyRange() As Variant 'Will be the independent element of the forecast function
  5. Dim MyRange1() As Variant 'Will be the dependent element of the forecast function
  6. Dim MyArray() As Variant 'Temp array to hold the query result set values before being split into the two preceding arrays
  7. Dim db As DAO.Database
  8. Dim rs1 As DAO.Recordset
  9. Dim ls As Integer 'Temp variable to count the rows in the list
  10.  
  11.  
  12. Set db = CurrentDb()
  13. Set rs1 = db.OpenRecordset("qryGetHistory") 'Query must be open to access it
  14.  
  15.     With rs1
  16.         .MoveFirst
  17.         .MoveLast
  18.         ls = .RecordCount
  19.         .MoveFirst
  20.         MyArray() = .GetRows(ls) 'Populate the temporary array with the query results
  21.     End With
  22. 'Split the required data into two arrays, drawing from columns two and three in the query/array
  23. MyRange() = Array(CInt(MyArray(1, 0)), CInt(MyArray(1, 1)), CInt(MyArray(1, 2)), CInt(MyArray(1, 3)), CInt(MyArray(1, 4)))
  24. MyRange1() = Array(CInt(MyArray(2, 0)), CInt(MyArray(2, 1)), CInt(MyArray(2, 2)), CInt(MyArray(2, 3)), CInt(MyArray(2, 4)))
  25. MyDate = CInt(DatePart("yyyy", "July 30")) 'Set the Desired point to forecast for
  26. rs1.Close
  27. Set rs1 = Nothing 'Reset the recordset, releasing memory
  28. Set db = Nothing
  29.  
  30.     xlForeCast = Excel.WorksheetFunction.Forecast(MyDate, MyRange1, MyRange) 'Calls the Excel forecast function
  31.  
  32. Erase MyArray 'Reset the Arrays to zero, releasing memory
  33. Erase MyRange
  34. Erase MyRange1
  35. End Function
I have found before that the most accurate data comes from using a subset of 5 units with the Forecast function (that's using my data... may not reflect the accuracy of your results)

Caveat: this method is a little slow in executing, since it's basically telling Excel to "wake up!", passing the data over and then waiting for a result.

Any questions you have, or any comments any experts might have are welcomed :-)

Regards,
Scott
Sep 2 '07 #4

P: 3
Thank You very much!
Sep 5 '07 #5

Scott Price
Expert 100+
P: 1,384
Thank You very much!
You're welcome, and good luck on the rest of your project!

Regards,
Scott
Sep 5 '07 #6

P: 2
Hi Scott,

I've tried to implement something using your code, but I fail.
Let me explain. I've got a history table with product, month, quantity
only when there's actually history there's a record.

The forecast function only works when zero's are also included.

Therefore I've setup a loop that browses through the table and populates an array with zero's or the quantity.

To calculate the forecast I created the following function

Function XlsFcast(k, x, y) As Double
Dim Xarr, Yarr, result

Xarr = Split(x, ",")
Yarr = Split(y, ",")

result = Excel.WorksheetFunction.forecast(k, Yarr, Xarr)

Xarr = Nothing
Yarr = Nothing

XlsFcast= result

End Function

But whenever I try to run it I get:
Run-time error '1004':
Unable to get the Forecast property of the WorksheetFunction class

I've tried various combinations, but I can't get arround this. Have you've got any ideas?

Thanks
Oct 29 '07 #7

Scott Price
Expert 100+
P: 1,384
Hi ynastra,

Im on a rather extended trip, and wont be back in regular touch until mid-December. Please post your question in a new thread and someone else will help you out.

Sorry.

Regards,
Scott
Nov 4 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Firstly I would change your declarations as follows:

Expand|Select|Wrap|Line Numbers
  1. Function XlsFcast(k As Double, x, y) As Double
  2. Dim Xarr, Yarr
  3. Dim result  As Double
  4.  
  5.     Xarr = Split(x, ",")
  6.     Yarr = Split(y, ",")
  7.  
  8.     result = Excel.WorksheetFunction.forecast(k, Yarr, Xarr)
  9.  
  10.     Xarr = Nothing
  11.     Yarr = Nothing
  12.  
  13.     XlsFcast= result
  14.  
  15. End Function
  16.  
Secondly, using split can be tricky and have unexpected results. Add the following piece of code after the split statement and see what the results are in the "immediate" window.

Expand|Select|Wrap|Line Numbers
  1. Dim i As Integer
  2.  
  3.     For i = 0 to UBound(Xarr)
  4.         Debug.Print Xarr(i) & chr(13)
  5.     Next i
  6.  
This should print out all the values in Xarr (one to each line) which should tell you if there is a problem. Look out for white space where your numbers have been interpreted as strings or nulls.

If that looks alright do the same for Yarr.
Nov 5 '07 #9

P: 2
All,

Thanks for the advice.

The reason for the array was to capture the table column content in a single cell so I could use the forecast function for a to be determined number of history buckets and show the history upon which it was based.

To work around it I have now created a dynamically determined table based upon a counter (i) and for the same counter I populate an X(i) and Y(i) double array.

Expand|Select|Wrap|Line Numbers
  1. 'recreate table
  2. On Error Resume Next
  3. conn.Execute "Drop table slomotracker;"
  4. On Error GoTo 0
  5.  
  6. SQL1 = "Create table slomotracker ( [MINPKG] string constraint primarykey primary key, [SL] Double, [FL] Double, [Warning] string)"
  7. conn.Execute SQL1
  8.  
  9. SQL2 = ""
  10. For i = -HistMonths To FcastMonths - 1 Step 1
  11.     SQL2 = "alter table slomotracker add column [" & i & "] double;"
  12.     conn.Execute SQL2
  13. Next i
  14.  
Then I can pass the arrays to the following function without it returning an excel error message

Expand|Select|Wrap|Line Numbers
  1. Public Function xlfcast(k As Double, Y, X) As Double
  2.  
  3. xlfcast = Excel.Application.WorksheetFunction.forecast(k, Y, X)
  4.  
  5. End Function
  6.  
P.s. if you'd have to initialize Y() first to contain zero's else the forecast value will not match your excel results.
Expand|Select|Wrap|Line Numbers
  1. for i = to to max step 1
  2.     y(i) = 0
  3. next i
  4.  
Nov 9 '07 #10

Scott Price
Expert 100+
P: 1,384
Glad you got it working, and thanks for posting back with your solution!

I'm just getting back into internet access range after being in the mountains of Peru for the last three weeks (no internet out there :-)

Regards,
Scott
Nov 21 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.