Table recordsets input/output mismatch

[floorwax]

Hello,
Hopefully this is something simple for the programming professional since I am only a casual user.

I have a table of pressure data with a date/time stamp as the primary key. There are over 300K recordsets (data for each minute). My objective is to read in one day of data (up to 1440 minutes), a recordset representing one minute at a time, and calculate daily averages, etc. Once the average is calculated, the result is output into a new table where each recordset now represents only one day.

My code works fine for Dec2009 to May2010 data. Once I hit Jun2010 to Aug2010, the date output is now out of order and contains random time stamps like 6/6/2010 10:30am. In addition, output recordset contains pressure averages for sometimes only 3 minutes when 1440 minutes are in the input table. See sample output below.

Records Date Ave. Press (in H2O)
1440 5/27/2010 8.759048E-02
1440 5/28/2010 8.996695E-02
1440 5/29/2010 0.1674371
1440 5/30/2010 0.1215714
622 8/15/2010 11:57:00 PM 0
3 8/16/2010 0
725 8/15/2010 11:49:00 AM 0.1506768
728 8/16/2010 12:05:00 PM 0.044
104 5/30/2010 10:22:00 AM 0
818 5/31/2010 0.9376
118 6/6/2010 10:30:00 AM 0.1622444

Here is a condensed version of my code...

Public Function AvePressure()
On Error GoTo AvePressure_Err
Dim dbs As DAO.Database
Dim rstISD As DAO.Recordset
Dim rstTbl As DAO.Recordset
Dim CurDay As Integer
Dim RecCount As Integer
Dim AvePres As Single

Set dbs = CurrentDb
Set rstISD = dbs.OpenRecordset("Pressure_Data")
Set rstTbl = dbs.OpenRecordset("Pressure_Averages")

RecCount = 1
With rstISD
If .RecordCount > 0 Then
.MoveFirst
Do While Not .EOF
CurDay = DatePart("d", (![Date/Time]))
RecCount = 0
AvePres = 0
Do
'Calculate averages, etc.
.MoveNext
RecCount = RecCount + 1
If .EOF Then Exit Do
Loop Until (DatePart("d", ![Date/Time]) <> CurDay)
rstTbl.AddNew
rstTbl![Date].Value = ![Date/Time].Value - 1
rstTbl![Record].Value = RecCount
rstTbl![DAP (in H2O)].Value = AvePres
rstTbl.Update
rstTbl.Bookmark = rstTbl.LastModified
Loop
End If
End With
rstISD.Close
rstTbl.Close
dbs.Close
AvePressure_Exit:
Set rstISD = Nothing
Set rstTbl = Nothing
Set dbs = Nothing
Exit Function
AvePressure_Err:
MsgBox Error$
Resume AvePressure_Exit
End Function

Any help would be appreciated on what is wrong with my existing code OR insight on a different approach.

Thanks,
"floorwax"

 

[traingamer]

Why not use a simple Group by query?

Select count([Date/Time]), avg([PressureField]) 
from Pressure_Data
group by format([date/time], "yyyymmdd") as TestDate

Or, alternately, use an order by clause in your existing selection?


Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[floorwax]

Hello Traingamer,

I would like to do a simple Group By query; however, I am doing much more than a simple average of pressure inside my do loop. In detail, I am checking for only positive pressures to be a part of the average. I am also calculating the maximum average hourly pressure. On top of that, I am calculating 30 day rolling averages. I should have mentioned that in the beginning. Thanks for your thoughts.

 

[traingamer]

Then use the alternative I suggested. Instead of just opening a recordset from the table (which returns the records in a random order), use a query sorted by the date.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[floorwax]

Traingamer,

Okay, you are saying within the code I can open a record set that uses the ORDER BY clause to create a sorted query on the table? From the Access help, it appears the ORDER BY is a SQL command...which I am not at all familiar with. But if I follow your idea, would my added code look something like this:

Set rst = dbs.OpenRecordset("SELECT Date/Time, Pressure, " _
& "FROM Pressure_Data " _
& "ORDER BY Date/Time DESC;")
In essence, this query does not change the order of my table as I see it in datasheet view since I imported it all in sequencial order. Effectively, this query gets around the random order inwhich data is internally stored for tables.

Is my understanding correct? If so, I am curious as to why the table data is stored in a random order...to optimize memory usage?

Thanks,
"floorwax"