CDMAPoster@FortuneJames.com wrote:[color=blue]
> Mamilovic wrote:[color=green]
> > I have converted the data from a DBF file format, at the moment there
> > is no index.
> > Any recommendations.
> > John[/color]
>
> With the number of records you have I don't think putting an index on
> those two fields will be enough, but the indexes will help tremendously
> for either a VBA solution or a SQL solution.
>
> James A. Fortune
>
CDMAPoster@FortuneJames.com[/color]
Make sure the indexes are in DataLogged. After copying the structure
of DataLogged to NewTable and deleting any fields beyond TimeStamp and
Reading:
Public Sub FillNewTable(dblBoundary As Double)
Dim MyDB As Database
Dim DataLoggedRS As Recordset
Dim NewTableRS As Recordset
Dim strSQL As String
Dim lngCount As Long
Dim lngI As Long
Dim boolCount As Boolean
Dim dblPrevValue As Double
Set MyDB = CurrentDb
strSQL = "DELETE NewTable FROM NewTable;"
MyDB.Execute strSQL, dbFailOnError
strSQL = "SELECT TimeStamp, Reading FROM NewTable;"
'Confirm that NewTable has no records
boolCount = True
Do While boolCount
Set NewTableRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If NewTableRS.RecordCount = 0 Then boolCount = False
NewTableRS.Close
Set NewTableRS = Nothing
Loop
Set NewTableRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
strSQL = "SELECT TimeStamp, Reading FROM DataLogged ORDER BY
TimeStamp;"
Set DataLoggedRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If DataLoggedRS.RecordCount > 0 Then
DataLoggedRS.MoveLast
lngCount = DataLoggedRS.RecordCount
DataLoggedRS.MoveFirst
For lngI = 1 To lngCount
If lngI <> 1 Then
If dblPrevValue < dblBoundary And DataLoggedRS("Reading") >
dblBoundary Then
NewTableRS.AddNew
NewTableRS("TimeStamp") = DataLoggedRS("TimeStamp")
NewTableRS("Reading") = DataLoggedRS("Reading")
NewTableRS.Update
End If
End If
dblPrevValue = DataLoggedRS("Reading")
If lngI <> lngCount Then DataLoggedRS.MoveNext
Next lngI
End If
DataLoggedRS.Close
Set DataLoggedRS = Nothing
NewTableRS.Close
Set NewTableRS = Nothing
Set MyDB = Nothing
End Sub
This code is completely untested but it does compile. I.e., to be safe
I'm calling it air code but I suspect that it will work as is. If it
works it should be much faster than the SQL I posted earlier. The
method for confirming the delete query is one I've never tried before.
One of the reasons for the huge difference in speed that I suspect will
occur is due to the code storing the previous value rather than having
SQL search the entire table for it. To run it use: Call
FillNewTable(1.45) Let me know how this turns out.
James A. Fortune
CDMAPoster@FortuneJames.com 
