Willem wrote:
Hi there,
I'm sort of new with doing much record manipulation with queries. Up
till now I've been programming VBA and doing record looping to get my
results. This works fine but tends to get very, very slow as the
number of records grows - somewhere in the number of 5,000,000. I
imagine queries are much faster but am not quite sure if queries will
do the trick.
My problem:
I have a table that records unique time based events. I would like to
compute the time difference between the most recent unique time based
event and the previous matching unique time based event.
Example
Table - Events
[Date] [Time] [IDNumber] [NextTime] [DifferenceMinutes]
01/01/04 03:30 1110 05:00 90
01/01/04 04:00 1120 09:00 300
01/01/04 05:00 1110
01/01/04 07:00 1130
01/01/04 09:00 1120
If I could somehow do this with a query then I could likewise move the
database the a MSSQL backend which I presume would handle things more
efficiently.
Any suggestions would be greatly appreciated.
Thanks
Willem
In a case like yours, I'd make it easy on myself. I don't know if this is
against normalization rules or not, but I wouldn't care. I would create a
new column to hold either the previous time value or else the key to the
record where the previous time value was held. If I had a column holding
the previous time, I'd somply subtract the values. If you used the
method holding the key, the query builder would display the table 2
times; the link would be from the linkID field to the record key in the
second table. Set the relationship to show ALL records in the table and
any matching in the second.
Running around trying to figure out what was the last record, or if a last
record exists, is too much of a pain.
Thus, when you add a new record, you could open up a recordcordset and
store the value. Put this in the BeForeUpdate event of the form.
If Me.NewRecord then
Dim rst As REcordset
Dim strSQL As String
Dim lngID As Long
lngID = 1120 'this could be a passed value.
strSQL = "Select TimeField from YourTable Where ID = " & lngID & _
" Order By DateField Desc, TimeField Desc"
set rst = currentdb.openrecordset(strSQL,dbopensnapshot)
If not rst.EOF Then
'move to first record as the sort is by date/time descending
rst.movefirst
Me.PreviousTime = rst!TimeField
Endif
rst.close
set rst = Nothing
else
me.previoustime = null
Endif
Here I assigned the time of the last record to the field PreviousTime
contained on a form. I'm not sure how you add a record, so adjust this as
necessary. But if you have a field on your form called PreviousTime (that
is bound to the field PreviousTime in the query or table) then get the
value when first saved. The field would be hidden (visible set to false).
If you overlap days you should store the value as a full date time
value. Let's say you have 5 million records, and the date field adds 2
bytes, that's only a 10 meg increase in table size...with indexes we could
kick it to 10 bytes...that's 50 meg. That's nothing in this day of large
file sizes.
You could even fill the column with an Update query. Create a function
similar to the above. In the querybuilder, make it an update query.
Enter in the UpdateTo row something like
=UpdatePreviousTimeField([id],[datefield],[timefield])
then in the function named UpdatePreviousTimeField, change the SQL select
to select all record where the id is the same but the date and time is
less than the datetime of the record to be updated.
If you DON'T want an extra field, create the function like
UpdatePreviousTimeField. It will be slightly slower since it needs to
recalc the values when it repaints the screen.