Interpolate data from tabel

[Geagleeye]

Hi.

I need to interpolate som data i have in my tabel.
my tabel looks like this.
Y X
0
0 0,026
0,037
0,003 0,038
0,005 0,064
0,067
0,074
0,008 0,077
0,01 0,09
0,093

but i need a smart way to interpolate the emty places in Y.
i did make som code in vba, but it takes about 3 min for 1100
recordset, and its to slow, there for im asking for some help with som
codes .
hope somone can help :(
thanks

[Lyle Fairfield]

This is old. Maybe it will help. Maybe not.

Dim m As Currency, b As Currency
Sub sInterpolation( )
Dim strSQL As String
Call sRegressionLine
strSQL = "UPDATE Data SET Data.Y = " & _
"(" & m & ")" & " * [X] + (" & b & ")" & _
"WHERE (((Data.Y) Is Null) AND ((Data.X) Is Not Null));"
CurrentDb.Execu te strSQL
End Sub
Sub sRegressionLine ()
Dim dbs As Database, rcs As Recordset
Set dbs = CurrentDb()
Set rcs = dbs.OpenRecords et("SELECT Sum(Data.X) AS SumX, " & _
"Sum([X]*[X]) AS SumXX, Sum(Data.Y) AS SumY, Sum([X]*[Y]) AS SumXY,
" & _
"Count(Data .X) AS N FROM Data " & _
"WHERE (((Data.X) Is Not Null) AND ((Data.Y) Is Not Null));")
m = (rcs!N * rcs!SumXY - rcs!SumX * rcs!SumY) / (rcs!N * rcs!SumXX
- rcs!
SumX ^ 2)
b = (rcs!SumY * rcs!SumXX - rcs!SumX * rcs!SumXY) / (rcs!N *
rcs!SumXX -
rcs!SumX ^ 2)
End Sub

[Geagleeye]

the compiler says that there is a syntaxt error on
strSQL = "UPDATE Data SET Data.Y = (" & m & ")" & " * [X] + (" & b
& ") " & _
" WHERE (((Data.Y) Is Null) AND ((Data.X) Is Not Null));"

need ,

[Geagleeye]

the code sems very nice, but i cant get´it work on vba (access)

[Lyle Fairfield]

For it to compile one would need to remove any usenet client created
line breaks.

For instance this
"Sum([X]*[X]) AS SumXX, Sum(Data.Y) AS SumY, Sum([X]*[Y]) AS SumXY," &
_
should be all one line but my/your usenet client may have cut it into
"Sum([X]*[X]) AS SumXX, Sum(Data.Y) AS SumY, Sum([X]*[Y]) AS
SumXY,
" & _

To get it "to work" one would have to have a Table called Data with
fields called X etc, or one would have to substitute table names and
field names of his own table and fields for these.

[Geagleeye]

hi again,....
i Did renamed my tabel as Data and both my attribut as X and Y.
and its also possible for me to see the number inside the variabels.

[Lyle Fairfield]

I'm sorry; I'm not there and I can't tell what might be wrong.

[Geagleeye]

did try the code, before you post it here ?

[Geagleeye]

did try the code, before you post it here ?

[Geagleeye]

did try the code, before you post it here ?