473,324 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Query Calculations

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
2 2520
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
-----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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: David | last post by:
Hi everyone. I have read every page that Google returns on this topic, but can't find anything that resolves my problem. Basically, I have an Access Database that does a number of different...
0
by: Erwin | last post by:
I am writing VBA code in a report code module. I need to make calculations that display on the report based on information on a query. What is the syntax for that? The logic is below. if...
4
by: Richard Hollenbeck | last post by:
The following query takes about one second to execute with less than 1,000 records, but the report that's based on it takes from 15-30 seconds to format and display. That's frustrating for both me...
15
by: Rolan | last post by:
There must be a way to enhance the performance of a query, or find a plausible workaround, but I seem to be hitting a wall. I have tried a few tweaks, however, there has been no improvement. ...
14
by: Crimsonwingz | last post by:
Need to calculate a sum based on a number of factors over a period of years. I can use formula ^x for some of it, but need totals to carry over in the sum and have only been able to do this thus...
3
by: Robin Thomas | last post by:
I am fairly new to ASP.NET so I think I am missing something fundamental. Anyway, quite often I am pulling data from a database, but then I need to use that data to produce more data. A simple...
22
by: robertmeyer1 | last post by:
Hi. I have been working on creating the structure of a DB and now I am trying to create some queries which will perform calculations. I have 1 table, tblAnswers which is based off an append query....
12
by: bhipwell via AccessMonster.com | last post by:
Hello, I have hit the "Cannot open any more databases" and "System resource exceeded" errors. Knew this was coming, but as I got closer to finishing the database, I hoped it wouldn't be an...
1
by: Grubsy4u | last post by:
Grubsy4u Newbie 7 Posts October 5th, 2007 11:31 AM #1 Report calculations --------------------------------------------------------------------------------
4
by: talktozee2 | last post by:
Hi, everyone. My problem is a bit too complex to explain in writing, let alone in the title of my post, but I'll give it a shot. I have a report that has a basic set of columns and rows, but...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.