Access TREND calculation

[m2techinc]

I have a table with 12 records and the following fields: Month, InventoryUsage

I'm trying to calculate the linear TREND of the Inventory Usage data and I'd like to use a control on a form to show this data next to the Inventory Usage form.

Any ideas?

[puppydogbuddy]

I have a table with 12 records and the following fields: Month, InventoryUsage

I'm trying to calculate the linear TREND of the Inventory Usage data and I'd like to use a control on a form to show this data next to the Inventory Usage form.

Any ideas?

Have you thought about using the MS Graph control to place a line graph on your form?

[m2techinc]

Have you thought about using the MS Graph control to place a line graph on your form?

Yes, that part was easy to figure out but it still doesn't give me a numerical representation of what I am looking for.

[puppydogbuddy]

Yes, that part was easy to figure out but it still doesn't give me a numerical representation of what I am looking for.

I not sure what you mean by it still doesn't give you a numerical presentation of what you are looking for? Please clarify. If you are referring to regression and other statisical functions available in Excel, those functions can be called from Access via automation techniques. See these links:

See these links for a "How To":


http://support.microsoft.com/kb/198571
http://www.fabalou.com/Access/Queri...l_functions.asp


See also:
http://www.cpearson.com/excel/ATP.htm
an add-in for scientific and engineering calculations

[m2techinc]

The links you provided were a great start to what I'm trying to do...let me explain further.

In Excel I could have a table as follows:
Month Usage Trend
1 55 56.29
2 57 56.77
3 56 57.26
4 62 57.74
5 59 58.23
6 56 58.71

The 3rd column (Trend) is calculated using the Trend function...quite easy in Excel! In Access, I used the link you provided (http://www.fabalou.com/Access/Querie..._functions.asp) and created a Function but I get a runtime error: Unable to get the Trend Property of the Worksheet Function Class. The reason I need to see these numbers is because we simply do not want to have to guess from a graph what the trend number is for any particular month.

Look forward to any other helpful ideas you may have.

[color=black][/color]

[color=black] [/color]

[color=black]I not sure what you mean by it still doesn't give you a numerical presentation of what you are looking for? Please clarify. If you are referring to regression and other statisical functions available in Excel, those functions can be called from Access via automation techniques. See these links:[/color]

[color=black] [/color]

See these links for a "How To":


[color=#800080]http://support.microsoft.com/kb/198571[/color]
[color=#800080]http://www.fabalou.com/Access/Queri...l_functions.asp[/color]



See also:
[color=#800080]http://www.cpearson.com/excel/ATP.htm[/color]
an add-in for scientific and engineering calculations[color=black][/color]

[NeoPa]

Yes, that part was easy to figure out but it still doesn't give me a numerical representation of what I am looking for.

I'm sorry the answer doesn't seem to match your expectations.
For that though, you need to write the question more clearly and explicitly.
I'm sure you weren't trying to sound ungracious but please bear that in mind in future.

MODERATOR.

*edit*
Having seen your latest post - this seems redundant.
Keep it in mind though perhaps.

[m2techinc]

I figured out the problem so I thought I'd post the result in case it can be of help to someone in the future:

 
Function Trend()
Dim objExcel As Excel.Application
Dim Arg1(9) As Double
Dim Arg2(9) As Double
Dim x As Integer
Dim result As Variant
 
For x = 0 To 9
Arg1(x) = 10 + x
Arg2(x) = x
Next
Set objExcel = CreateObject("Excel.Application")
result = objExcel.WorksheetFunction.Trend(Arg1, Arg2)
For x = 1 To 10
MsgBox result(x)
Next
objExcel.Quit
Set objExcel = Nothing
End Function
 

[MMcCarthy]

Thank you for posting the solution.

You never know who might find it useful in the future.

Mary

[NeoPa]

And, just in case you think I'm only here to be grumpy and criticise, let me add my thanks too.
It really is useful if solutions can be posted where found, even when they are not provided by our pool of Experts.

Cheers, -Adrian.