By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

Query Calculations

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe you can use a "running total" type query to get the results
you want, but first I'd put the Date and Time into one column:
"EventDate." This will make the time calculation easier. The query
could look like this:

SELECT IDNumber, EventDate,
(SELECT DateDiff("n", E.EventDate, EventDate)
FROM Events
WHERE IDNumber = E.IDNumber
AND EventDate > E.EventDate
ORDER BY EventDate Desc) As MinutesDiff
FROM Events As E
ORDER BY IDNumber, EventDate

Haven't tested this query.

This is based on info in Microsoft Knowledgebase Q138911, "ACC: How to
Create Running Totals in a Query."

MS SQL Server T-SQL has the same function, DateDiff(), with the same
parameters - see SQL Books On Line for more info.

I've found that Access reports don't "like" the subquery in the SELECT
clause. Therefore, you'd have to change it to a DLookup() function to
get it to work in a report. Once you've moved to MS SQL'r you can use
the subquery again.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP/Zo/oechKqOuFEgEQIl1ACguoE+EhlmA3ZmQhx4ARxoGOxKlsgAoPT O
Pb4nX+kOfIM9HwqlmprTgS16
=d0il
-----END PGP SIGNATURE-----

Willem wrote:

<SNIP>
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


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.