Calculate - Record to Record

[Mike C]

I append about 300,000 records a day of operational data into an Access
database from a large repository. The operational data consists of workers in
the field inputing audit information into a PDA. When they complete an audit
and send it in, there is a time stamp and audit number associated with the
information. One of the things I need to find out with this data is how much
time there is from audit to audit. What I am currently doing is sorting the
table (300,000 records) in a certain order (emp_num, audit_num, complete_tm)
and then throwing 50,000 records at a time into Excel and calculaing the time
from one audit to another. I then paste 50,000 records at a time into another
table.

As an alternative I would think I could have a function that opens the
recordset of the table and does that calculation in a field I added to the
table. Any ideas, suggestions, or sample code would be greatly appreciated.

tbl_AuditData
Date, District, Emp_Num, Audit_Type, Score, Audit_Num, Audit_Time

 

[BeWyched]

Hi Mike

Do you mean 'Time', i.e. 08:27 a.m., or 'Date'?

Whichever, you can find the difference by simply subtracting one from
another (as per Excel).

i.e. varDateDiff = fld_Date1 - fld_Date2
will return the number of days between the two dates.

So extract your data into a recordset using a SQL string ...

Set rst = CurrentDb.OpenRecordset("SELECT Audit_Time FROM tbl_AuditData
WHERE .... selection criteria goes here... ORDER BY Audit_Time")

Then spin through ther recordset poping the date difference between each
record into a new field...

Good luck.

BW

 

[Mike C]

Thanks for pointing me in the right dirction. I was actually able to update
the field in the table but I have to find a way to loop it. In the code
below I've got 2 recordsets because one of them needs to be ahead of the
other to calculate the time in between stops. I'm not sure how to loop it so
that it stays 1 ahead. Any ideas or suggestions would be greatly appreciated.

Function Clicker()
Dim r1, r2 As Recordset

Set r1 = CurrentDb.OpenRecordset("SELECT * FROM tbl_DriverDay ORDER BY
tbl_DriverDay.[Day Date], tbl_DriverDay.[District Num], tbl_DriverDay.[Center
Num], tbl_DriverDay.[Pkg Svc Provider UPS Id Num], tbl_DriverDay.[Stop Actual
Sequence Num], tbl_DriverDay.[Stop Pkg Record Sequence Num];")

Set r2 = CurrentDb.OpenRecordset("SELECT * FROM tbl_DriverDay ORDER BY
tbl_DriverDay.[Day Date], tbl_DriverDay.[District Num], tbl_DriverDay.[Center
Num], tbl_DriverDay.[Pkg Svc Provider UPS Id Num], tbl_DriverDay.[Stop Actual
Sequence Num], tbl_DriverDay.[Stop Pkg Record Sequence Num];")

r1.MoveFirst
r2.MoveFirst
r2.MoveNext
r2.Edit
r2!Clicks = r2![Stop Complete Time] - r1![Stop Complete Time]
r2.Update
End Function

 

[BeWyched]

Hi Mike

It looks like you are finding the difference between each succesive record.
If so then it would be better to use just 1 recordset, and store the previous
value in a variable, say varLastTime, along the lines of

Dim varLastTime, rst

Set rst = CurrentDb("SELECT .... your first SQL String")
With rst
varLastTime - ![Stop Complete Time] ' establishes first value
.movenext
Do Until .eof ' start the loop
.edit
!Clicks = ![Stop Last Time] - varLastTime
.update
varLastTime = ![Stop Last Time] ' sets varLastTime for
next loop
.movenext ' to next record
Loop
End With
MsgBox "Done"

A question - you are sorting the records against a whole load of fields - is
this necessary as it will slow things down a lot - only sort if you need to.

Hope this works!

Cheers.

BW

Thanks for pointing me in the right dirction. I was actually able to update
the field in the table but I have to find a way to loop it. In the code
below I've got 2 recordsets because one of them needs to be ahead of the
other to calculate the time in between stops. I'm not sure how to loop it so
that it stays 1 ahead. Any ideas or suggestions would be greatly appreciated.

Function Clicker()
Dim r1, r2 As Recordset

Set r1 = CurrentDb.OpenRecordset("SELECT * FROM tbl_DriverDay ORDER BY
tbl_DriverDay.[Day Date], tbl_DriverDay.[District Num], tbl_DriverDay.[Center
Num], tbl_DriverDay.[Pkg Svc Provider UPS Id Num], tbl_DriverDay.[Stop Actual
Sequence Num], tbl_DriverDay.[Stop Pkg Record Sequence Num];")

Set r2 = CurrentDb.OpenRecordset("SELECT * FROM tbl_DriverDay ORDER BY
tbl_DriverDay.[Day Date], tbl_DriverDay.[District Num], tbl_DriverDay.[Center
Num], tbl_DriverDay.[Pkg Svc Provider UPS Id Num], tbl_DriverDay.[Stop Actual
Sequence Num], tbl_DriverDay.[Stop Pkg Record Sequence Num];")

r1.MoveFirst
r2.MoveFirst
r2.MoveNext
r2.Edit
r2!Clicks = r2![Stop Complete Time] - r1![Stop Complete Time]
r2.Update
End Function

Hi Mike

Do you mean 'Time', i.e. 08:27 a.m., or 'Date'?

Whichever, you can find the difference by simply subtracting one from
another (as per Excel).

i.e. varDateDiff = fld_Date1 - fld_Date2
will return the number of days between the two dates.

So extract your data into a recordset using a SQL string ...

Set rst = CurrentDb.OpenRecordset("SELECT Audit_Time FROM tbl_AuditData
WHERE .... selection criteria goes here... ORDER BY Audit_Time")

Then spin through ther recordset poping the date difference between each
record into a new field...

Good luck.

BW

 

[BeWyched]

Hi

Just spotted an error -

Line should read:
Set rst = CurrentDb.OpenRecordset("SELECT.....")

Hi Mike

It looks like you are finding the difference between each succesive record.
If so then it would be better to use just 1 recordset, and store the previous
value in a variable, say varLastTime, along the lines of

Dim varLastTime, rst

Set rst = CurrentDb("SELECT .... your first SQL String")
With rst
varLastTime - ![Stop Complete Time] ' establishes first value
.movenext
Do Until .eof ' start the loop
.edit
!Clicks = ![Stop Last Time] - varLastTime
.update
varLastTime = ![Stop Last Time] ' sets varLastTime for
next loop
.movenext ' to next record
Loop
End With
MsgBox "Done"

A question - you are sorting the records against a whole load of fields - is
this necessary as it will slow things down a lot - only sort if you need to.

Hope this works!

Cheers.

BW

Thanks for pointing me in the right dirction. I was actually able to update
the field in the table but I have to find a way to loop it. In the code
below I've got 2 recordsets because one of them needs to be ahead of the
other to calculate the time in between stops. I'm not sure how to loop it so
that it stays 1 ahead. Any ideas or suggestions would be greatly appreciated.

Function Clicker()
Dim r1, r2 As Recordset

Set r1 = CurrentDb.OpenRecordset("SELECT * FROM tbl_DriverDay ORDER BY
tbl_DriverDay.[Day Date], tbl_DriverDay.[District Num], tbl_DriverDay.[Center
Num], tbl_DriverDay.[Pkg Svc Provider UPS Id Num], tbl_DriverDay.[Stop Actual
Sequence Num], tbl_DriverDay.[Stop Pkg Record Sequence Num];")

Set r2 = CurrentDb.OpenRecordset("SELECT * FROM tbl_DriverDay ORDER BY
tbl_DriverDay.[Day Date], tbl_DriverDay.[District Num], tbl_DriverDay.[Center
Num], tbl_DriverDay.[Pkg Svc Provider UPS Id Num], tbl_DriverDay.[Stop Actual
Sequence Num], tbl_DriverDay.[Stop Pkg Record Sequence Num];")

r1.MoveFirst
r2.MoveFirst
r2.MoveNext
r2.Edit
r2!Clicks = r2![Stop Complete Time] - r1![Stop Complete Time]
r2.Update
End Function

Hi Mike

Do you mean 'Time', i.e. 08:27 a.m., or 'Date'?

Whichever, you can find the difference by simply subtracting one from
another (as per Excel).

i.e. varDateDiff = fld_Date1 - fld_Date2
will return the number of days between the two dates.

So extract your data into a recordset using a SQL string ...

Set rst = CurrentDb.OpenRecordset("SELECT Audit_Time FROM tbl_AuditData
WHERE .... selection criteria goes here... ORDER BY Audit_Time")

Then spin through ther recordset poping the date difference between each
record into a new field...

Good luck.

BW




:

I append about 300,000 records a day of operational data into an Access
database from a large repository. The operational data consists of workers in
the field inputing audit information into a PDA. When they complete an audit
and send it in, there is a time stamp and audit number associated with the
information. One of the things I need to find out with this data is how much
time there is from audit to audit. What I am currently doing is sorting the
table (300,000 records) in a certain order (emp_num, audit_num, complete_tm)
and then throwing 50,000 records at a time into Excel and calculaing the time
from one audit to another. I then paste 50,000 records at a time into another
table.

As an alternative I would think I could have a function that opens the
recordset of the table and does that calculation in a field I added to the
table. Any ideas, suggestions, or sample code would be greatly appreciated.

tbl_AuditData
Date, District, Emp_Num, Audit_Type, Score, Audit_Num, Audit_Time

 

[John W. Vinson]

I append about 300,000 records a day of operational data into an Access
database from a large repository. The operational data consists of workers in
the field inputing audit information into a PDA. When they complete an audit
and send it in, there is a time stamp and audit number associated with the
information. One of the things I need to find out with this data is how much
time there is from audit to audit. What I am currently doing is sorting the
table (300,000 records) in a certain order (emp_num, audit_num, complete_tm)
and then throwing 50,000 records at a time into Excel and calculaing the time
from one audit to another. I then paste 50,000 records at a time into another
table.

As an alternative I would think I could have a function that opens the
recordset of the table and does that calculation in a field I added to the
table. Any ideas, suggestions, or sample code would be greatly appreciated.

tbl_AuditData
Date, District, Emp_Num, Audit_Type, Score, Audit_Num, Audit_Time

A "Self Join" query can be used for this purpose - but it may be really
inefficient given the size of the table. You should have indexes on Emp_Num,
Audit_Type and the Date field (which, by the way, should be renamed: that's a
reserved word).

What are the actual contents of the fields? Is Date the date of the audit, and
Audit_Time the clock time of the audit? What constitutes the "time from one
audit to another" - the number of minutes between Audit_Time for two records
with... what? The same Emp_Num and Audit_Type, or the same Audit_Num, or what?
If there are two records on different dates (e.g. one Audit_Time is 11:15pm
and the next is 8:45am), do you want to count the total elapsed time, or are
employees allowed sleep time?

John W. Vinson [MVP]