Maths and VBA (Variance)

[The Member]

Young people of the universe,

At the moment I am working on a piece of code to calculate Variance.

The calculation procedure involve each member of the recordset minus the mean of the recordset (Xi - MnX, where Xi is the member of the Recordset and the MnX is the mean).

Using Recordset.edit and recordset.Upate I manage to make the calculation through but this means modification of the original data in the table that is just not cool.

So my question is... is there any way that I can make a copy of the Recordset and work off it in VBA without makeing a new table??

Many ta in advance.

JL

 

[Rich]

Why not use a form/query or report to carry out the calculations?

 

[Emohawk]

As far as I'm aware you can't actually copy a recordset without it referencing a table/s. A recordset is a collection of references that point to data objects matching your query.

Did that make sense? It didn't to me. I think I have the skill of BS down to a fine art.

Just copy the contents of the recordset one at a time to an array using a loop then edit those details as you like.

 

[The Member]

As far as I'm aware you can't actually copy a recordset without it referencing a table/s. A recordset is a collection of references that point to data objects matching your query.

Did that make sense? It didn't to me. I think I have the skill of BS down to a fine art.

Just copy the contents of the recordset one at a time to an array using a loop then edit those details as you like.

Dude, I think it is a good idea... the reaseon I would like to keep the out come as a recordset is becase I can feed it into the another function that need a recordset... seems that I cannot refer to Sum afterall..

ta anyway.

JL

cool hair do BTW

 

[Emohawk]

Well you could always make your changes (in regard to the variance) to the Recordset pass it to your function and then roll back the changes.

MyRecordset.CancelUpdate

Just a thought...

P.s.

The Mullet will live forever.

 

[rich.barry]

Having a squiz at this could be a good thing....

'Read the data for the statistics arrays
Record.Open SQLString, db, adOpenKeyset, adLockOptimistic
If Record.RecordCount > 0 Then
c = 1
Record.MoveFirst
Do While Not Record.EOF
With Record
data(c, l * 2 + 1) = !MoE
If Not IsNull(!MoR) Then
data(c, l * 2 + 2) = !MoR
End If
c = c + 1
.MoveNext
End With
Loop
End If
Record.Close


EVMoE.Caption = Round(StatV(data, 1), 2)



Option Compare Database
Option Explicit

Public data(10000, 5) As Single
Public SortArray(0 To 1, 10000) As Variant

Function StatSD(DataArray As Variant, Colnum As Integer) As Single

Dim c As Integer
Dim total As Double
Dim avg As Single
Dim sumdev2 As Double

c = 0

Do While DataArray(c + 1, Colnum) > 0
c = c + 1
total = total + DataArray(c, Colnum)
Loop

If c > 0 Then avg = total / c

c = 0

Do While DataArray(c + 1, Colnum) > 0
c = c + 1
sumdev2 = sumdev2 + (DataArray(c, Colnum) - avg) ^ 2
Loop

StatSD = (sumdev2 / (c - 1)) ^ 0.5


End Function

Function StatAverage(DataArray As Variant, Colnum As Integer) As Single

Dim c As Integer
Dim total As Double

c = 0

Do While DataArray(c + 1, Colnum) > 0
c = c + 1
total = total + DataArray(c, Colnum)
Loop

StatAverage = total / c

End Function

Function StatCount(DataArray As Variant, Colnum As Integer) As Integer

Dim c As Integer

c = 0

Do While DataArray(c + 1, Colnum) > 0
c = c + 1
Loop

StatCount = c

End Function

Function StatV(DataArray As Variant, Colnum As Integer) As Single

StatV = StatSD(DataArray, Colnum) / StatAverage(DataArray, Colnum)

End Function

 

[The Member]

Having a squiz at this could be a good thing....

Hey dude,

You can easily be a very good buddy.

I have survived the Variance and have done some other stuff too, like mean, SD, var, correlation, covariance.

I also have a 2 distributions, normal and T (hence Gamma function too). and their inverse.

So if you want we can share some codes.

Today I will do the Skewness (not Skegness, where I went to school), and Kurtosis.

JL

 

[The_Doc_Man]

Hate to toss a bucket of cold water your way, but...

Are you aware that there is a VAR function in Totals queries?

Why write code to reinvent wheels if there is a wheel there for the taking? (I also believe there is a StDev function.)

In query design view (the grid), look for the Sigma button. Press it. Now look at the choices in the drop-down list of functions. Besides Group By and Count and Max and Min, there are several others - of which Var and StDev are two.

If you write the query correctly, you won't change a thing in the recordset.

 

[Pat Hartman]

An alternative is purchasing a statistical package. One is available from www.fmsinc.com

 

[The Member]

Hate to toss a bucket of cold water your way, but...

Are you aware that there is a VAR function in Totals queries?

Why write code to reinvent wheels if there is a wheel there for the taking? (I also believe there is a StDev function.)

In query design view (the grid), look for the Sigma button. Press it. Now look at the choices in the drop-down list of functions. Besides Group By and Count and Max and Min, there are several others - of which Var and StDev are two.

If you write the query correctly, you won't change a thing in the recordset.

I am aware of that, and also aware that a Stat package is just around the corner, the reason I am doing all this is that I wish to have full control over the calculation process and also may be I can bring this to VB ( which I never use but heard rumor that it is more or less the same as VB).

My company is thinking about making some kind of forcasting tools, which might end up as a stand alone programme (may be better in VB) or may be attach to some databases (access VBA) which they have not decided yet, but I just want to be ready for it.

 

[raskew]

Suspect you're spinning your wheels trying to reinvent something that already exists.

Nonetheless, search for "neatcode.mdb", download it, and you'll find multiple examples of a variety of advanced mathematical functions, using just code (not pre-designed functions).