473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Calculating Weighted Average in Access

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
10 15924
Scott Price
1,384 Expert 1GB
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
1,384 Expert 1GB
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
1,384 Expert 1GB
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
Thank You very much!
Sep 5 '07 #5
Scott Price
1,384 Expert 1GB
Thank You very much!
You're welcome, and good luck on the rest of your project!

Regards,
Scott
Sep 5 '07 #6
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
1,384 Expert 1GB
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
MMcCarthy
14,534 Expert Mod 8TB
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
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
1,384 Expert 1GB
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

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

Similar topics

1
by: BerkshireGuy | last post by:
I have the following data Month/Year Rep Avg Courtesy Rating Number Returned -------------------------------------------------------- May 2005 BJM 3.6 2 June 2005 ...
5
by: Mr. Ken | last post by:
I am calculating the phase of an IQ signal, which are polluted by AWGN gaussian noise. Thus, near pi/2, direct division of atan(Q/I) may yield outputs either +pi/2 or -pi/2. How do I handle this...
1
by: HEMH6 | last post by:
Weihted Average Write a C program to calculate and print the weighted average of a list of N floating point number, using the formula Xave = F1X1 + F2X2+...+ FnXn where the F's are...
5
by: ye2127 | last post by:
Hi, I have two fields in my report. One of them is school name(the school name appears multiple times). The other field is class test score. How would I go about calculating the test average...
1
by: ye2127 | last post by:
Hi, I have two fields in my report. One of them is school name(the school name appears multiple times). The other field is class test score. How would I go about calculating the test average for...
3
by: Salad | last post by:
http://www.mathwords.com/w/weighted_average.htm At the above link gives an example of a weighted average. It uses the following example: Grades are often computed using a weighted average....
5
by: jamesnkk | last post by:
I want to calculate the average price, I have a cost price table- tblcostPrice as below,Same part no can be purchase from different supplier at different cost. In my tblcostPrice I have 2...
0
by: edmund_xue | last post by:
Hello There, I was just working on a project of creating a database for a relative that have alots of clients. His company managed the shares portfolio of clients and he requires the buy price...
1
by: cmb3587 | last post by:
My code runs fine for the most part...the only time it fails is when I type in a negative to end the array. I don't want the negative number to be included in the array and I thought that is what...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.