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
+ 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
10 Replies

 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 =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

 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

 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 SELECT DISTINCTROW tblHistory.ConvID, tblHistory.Year, tblHistory.PersonCountAct, tblHistory.MealID FROM tblHistory WHERE (((tblHistory.ConvID)=1) AND ((tblHistory.Year)>=(DatePart("yyyy","July 30")-5)) AND ((tblHistory.PersonCountAct)<>0) AND ((tblHistory.MealID)=1)) ORDER BY tblHistory.Year DESC;   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 Public Function xlForeCast() As Double   Dim MyDate As Integer 'Will be the point for which you are forecasting, in this case 2007 Dim MyRange() As Variant 'Will be the independent element of the forecast function Dim MyRange1() As Variant 'Will be the dependent element of the forecast function Dim MyArray() As Variant 'Temp array to hold the query result set values before being split into the two preceding arrays Dim db As DAO.Database Dim rs1 As DAO.Recordset Dim ls As Integer 'Temp variable to count the rows in the list     Set db = CurrentDb() Set rs1 = db.OpenRecordset("qryGetHistory") 'Query must be open to access it       With rs1         .MoveFirst         .MoveLast         ls = .RecordCount         .MoveFirst         MyArray() = .GetRows(ls) 'Populate the temporary array with the query results     End With 'Split the required data into two arrays, drawing from columns two and three in the query/array MyRange() = Array(CInt(MyArray(1, 0)), CInt(MyArray(1, 1)), CInt(MyArray(1, 2)), CInt(MyArray(1, 3)), CInt(MyArray(1, 4))) MyRange1() = Array(CInt(MyArray(2, 0)), CInt(MyArray(2, 1)), CInt(MyArray(2, 2)), CInt(MyArray(2, 3)), CInt(MyArray(2, 4))) MyDate = CInt(DatePart("yyyy", "July 30")) 'Set the Desired point to forecast for rs1.Close Set rs1 = Nothing 'Reset the recordset, releasing memory Set db = Nothing       xlForeCast = Excel.WorksheetFunction.Forecast(MyDate, MyRange1, MyRange) 'Calls the Excel forecast function   Erase MyArray 'Reset the Arrays to zero, releasing memory Erase MyRange Erase MyRange1 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

 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

 Expert 100+ P: 1,384 Hi ynastra, I´m on a rather extended trip, and won´t 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

 Expert Mod 10K+ P: 14,534 Firstly I would change your declarations as follows: Expand|Select|Wrap|Line Numbers Function XlsFcast(k As Double, x, y) As Double Dim Xarr, Yarr Dim result  As Double       Xarr = Split(x, ",")     Yarr = Split(y, ",")       result = Excel.WorksheetFunction.forecast(k, Yarr, Xarr)       Xarr = Nothing     Yarr = Nothing       XlsFcast= result   End Function   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 Dim i As Integer       For i = 0 to UBound(Xarr)         Debug.Print Xarr(i) & chr(13)     Next i   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 'recreate table On Error Resume Next conn.Execute "Drop table slomotracker;" On Error GoTo 0   SQL1 = "Create table slomotracker ( [MINPKG] string constraint primarykey primary key, [SL] Double, [FL] Double, [Warning] string)" conn.Execute SQL1   SQL2 = "" For i = -HistMonths To FcastMonths - 1 Step 1     SQL2 = "alter table slomotracker add column [" & i & "] double;"     conn.Execute SQL2 Next i   Then I can pass the arrays to the following function without it returning an excel error message Expand|Select|Wrap|Line Numbers Public Function xlfcast(k As Double, Y, X) As Double   xlfcast = Excel.Application.WorksheetFunction.forecast(k, Y, X)   End Function   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 for i = to to max step 1     y(i) = 0 next i   Nov 9 '07 #10

 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.