Data from Previous Record

[sluggercoach]

Hi. I am attempting to do some calculations with time that requires me to pull data from 2 different records. Below is an example.

Record # Start Time Finish Time
1 9/3/2009 8:55:09 9/3/2009 9:01:09
2 9/3/2009 9:04:10 9/3/2009 9:07:05

Each record represents the cycle time for completing an action. I need to calculate the transportation time between the 2 cycles which is the Start Time of Record #2 minus the Finish Time of Record #1. I have to do this for 83 different records in over 100 stations. Of course in Excel this is easy but I need to automate this so that my managers can choose the set of data and then see the transfer time calculated for them. Any suggestions on how to do this in Access? I imagine I will need some kind of VB code to loop through but I am not sure if that is the best approach.

 

[Scooterbug]

Someone had a similar problem a few weeks ago. This post:
http://www.access-programmers.co.uk/forums/showpost.php?p=879127&postcount=18
Has a sample db that will do what you are looking to do. You will have to tweak it with your table and field names...but it should do the trick.

 

[sluggercoach]

Thanks alot. I see the database but I am having a hard time understanding what you are calculating in the sample. It seems that you are just taking data from one table and putting it in another. Maybe I am not following. What I am trying to do is calculate the difference in time between 2 records in the same table. Am I making sense? Sorry.

 

[Scooterbug]

Thanks alot. I see the database but I am having a hard time understanding what you are calculating in the sample. It seems that you are just taking data from one table and putting it in another.

Correct. What the code does is cycle through the records, grabs the times, makes the calculations and dumps the data into a Temp table. By putting them into a separate table, you can then view the responses.

Maybe I am not following. What I am trying to do is calculate the difference in time between 2 records in the same table. Am I making sense? Sorry.

The code in that database does the exactly that. If the Start and Stop times were placed in the same record, you could simply subtract the Start time from the Stop time. But, since the times you want are in two different records, you need to cycle through them and remember the Time value from the previous record.

I'm sure that a function could be made so that you could calculate the difference on the fly...but you would need to make sure you had a way to grab the preceding record so you could use the correct times.

 

[sluggercoach]

I think I understand. I just need to change a few things to match my tables and I should be good. Thank you so much! I have been struggling with this a while now. I was doing about 5 make table queries (which always somehow fail when you give them to the end user) to try to do the same thing!

 

[The_Doc_Man]

A theoretical explanation of this problem is appropriate.

When trying to define something as a value in record X = something computed from record X-1, the problem is that theoretically no such record exists. Ever.

Records have no inherent order until you define one. That is because the set theory on which most queries are based happens to be defined as though everything happens at once. The fact that the database engine can't actually do it that way is immaterial. What happens is that you run your query or whatever and the set theory underpinning says the thing you ran comes back only after the "monolithic" action is complete.

I.e. if you do a SELECT, that doesn't come back until the recordset has been defined. If you do an UPDATE, that doesn't come back until all updates are complete. The query is a "black box" from SQL. In that context, there is no variable you can use to remember something from a previoius record because there is no previous record.

The normal solution is to create a SELECT query with an ORDER BY clause and feed that to a set of VBA code that can single-step its way through the recordset. With the ORDER BY clause and a step-wise execution, you get to define a "PREVIOUS" record because this is no longer a "pure" SQL context. SQL has no variables. VBA can store data in a variable to remember values from one step to the next. The "correct" way to refer to a previous record is via VBA code.

There are more complex ways of doing that purely with SQL and complex selection criteria, but they are extremely clunky. Also, the catch is always that you cannot really remember a value from one record to the next in SQL. You must dynamically re-evaluate any value you want for each new record. Which is why so many SQL-only solutions to this problem involve a Domain Aggregate function (such as DMAX or DLOOKUP) with really complicated selection criteria. And of course, since each domain aggregate is an implied query, your execution speed just hit the toilet for long recordsets. For short recordsets, this won't be quite so bad, but it still isn't pretty.

 

[sluggercoach]

OK. I have the code working except for one thing. I am trying to take a time from Field1 in row 2 and from a different field in row 1.

I have a table that has a start time and a finish time in. To calculate travel time I have to take the start time from record 2 minus the finish time from record 1. This code has me taking the time from row 2 minus the time in row 1 for the same field. Any suggestions?

 

[Scooterbug]

Can you post the code? To make it easier to read, make sure you put the code tags around it.

 

[sluggercoach]

HEre is what it is. I took the code from your sample database and modified it to try and get what I need. Right now I am getting the End Date from Row 2 minus End Date from Row 1. What I need is Start Date from Row 2 minus End Date from Row 1.

I don't know what the code tags are. Sorry and hope you can read this.

Private Sub Command2_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim dtmTime1 As Date
Dim dtmTime2 As Date

Set db = CurrentDb
Set rs = db.OpenRecordset("Query1") 'Defines a record set based off of Query1. Referenced in code as rs
Set rs2 = db.OpenRecordset("Table2") 'Defines a record set based off of Table2. Referenced in Code as rs2

With rs 'tells the code to add rs to anything that starts with a .
.MoveFirst 'Move to first record in the query
Do
Do Until .EOF
dtmTime1 = ![EndTime] 'Define first time
If dtmTime2 = "12:00:00 AM" Then 'checks to see if there is a previous time so it can calculate, if not
dtmTime2 = dtmTime1 'Assigns the previous time into a new variable
.MoveNext 'then it moves to the next record

dtmTime1 = ![StartTime] 'Grabs the next time on the list
Else 'This goes off if there is both time variables have valid times
'The next line displays the data in a text box called txtHolder. Code will error out if the text
'box isn't there. If you are going to copy the code, you can remove the line below.
Me.txtHolder = Me.txtHolder & "Time for " & ![txn_id] & ": " & DateDiff("s", dtmTime2, dtmTime1) & vbCrLf
rs2.AddNew 'Creates a new record in Table2
rs2.Fields("txn_id") = ![txn_id] 'Adds the ID to the table so you can associate the calculated difference
rs2.Fields("Difference") = DateDiff("s", dtmTime2, dtmTime1) 'adds the difference in time
rs2.Update
dtmTime2 = dtmTime1 'Moves the second time to the first variable so next calculation can be done
.MoveNext 'Moves to the next record
End If
Loop
Loop Until .EOF
End With

End Sub

 

[Scooterbug]

HEre is what it is. I took the code from your sample database and modified it to try and get what I need. Right now I am getting the End Date from Row 2 minus End Date from Row 1. What I need is Start Date (=dtmTime2) from Row 2 minus End Date (=dtmTime1) from Row 1.

I don't know what the code tags are. Sorry and hope you can read this.

Private Sub Command2_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim dtmTime1 As Date
Dim dtmTime2 As Date
 
Set db = CurrentDb
Set rs = db.OpenRecordset("Query1") 'Defines a record set based off of Query1. Referenced in code as rs
Set rs2 = db.OpenRecordset("Table2") 'Defines a record set based off of Table2. Referenced in Code as rs2
 
With rs 'tells the code to add rs to anything that starts with a .
.MoveFirst 'Move to first record in the query
Do
Do Until .EOF
dtmTime1 = ![EndTime] 'Define first time
If dtmTime2 = "12:00:00 AM" Then 'checks to see if there is a previous time so it can calculate, if not
dtmTime2 = dtmTime1 'Assigns the previous time into a new variable
.MoveNext 'then it moves to the next record
 
dtmTime1 = ![StartTime] 'Grabs the next time on the list
Else 'This goes off if there is both time variables have valid times
'The next line displays the data in a text box called txtHolder. Code will error out if the text
'box isn't there. If you are going to copy the code, you can remove the line below.
Me.txtHolder = Me.txtHolder & "Time for " & ![txn_id] & ": " & DateDiff("s", dtmTime2, dtmTime1) & vbCrLf
rs2.AddNew 'Creates a new record in Table2
rs2.Fields("txn_id") = ![txn_id] 'Adds the ID to the table so you can associate the calculated difference
rs2.Fields("Difference") = DateDiff("s", dtmTime2, dtmTime1) 'adds the difference in time
rs2.Update
dtmTime2 = dtmTime1 'Moves the second time to the first variable so next calculation can be done
.MoveNext 'Moves to the next record
End If
Loop
Loop Until .EOF
End With
 
End Sub

Reposting with code tags....will edit after posted with some more clarification...

(btw...the code tag is like any other tag...to start you put code in brackets [] to end, use /code in brackets [])

Edit:
This code assumes that the time is located in the same field. As it grabs the time, it passes it off to the second variable (dtmDate2). That wont work in your case, since your start time and end time are in different fields. Give me a few minutes and I will modify the code. I work much better when I can see the code in action

 

[Scooterbug]

Alrighty...here is the modified code and how it works. The Delcarations and such remain the same. The only change you need to do is to the Do Loop:

Do
    Do Until .EOF
    dtmTime1 = ![[COLOR=darkred]FinishTime[/COLOR]]

This part assigns the finish time of the first record to the the variable called dtmTime1. (Note that the words in this color Need to match field names in your tables)

            If dtmTime2 = "12:00:00 AM" Then       
               .MoveNext
               dtmTime2 = ![[COLOR=darkred]StartTime[/COLOR]]

It now checks to see if there is a value for dtmTime2. If not, it tells it to go to the next record and set the variable dtmTime2 to equal the start time.

                rs2.AddNew
                rs2.Fields("[COLOR=darkred]ForeignKey[/COLOR]") = ![[COLOR=darkred]pkid[/COLOR]]
                rs2.Fields("[COLOR=darkred]Difference[/COLOR]") = DateDiff("s", dtmTime1, dtmTime2)
                rs2.Update

This is the code to add the records to the temp table. It puts the value of the primary key of the record with the start time for reference. It then uses the function DateDiff() to calculate the difference between the finish time and the start time. It records this in seconds. Once finished, it will loop back to the beginning of the Do Loop.

        Else
            .MoveNext
                If .EOF Then Exit Do

This part executes if there is a value for dtmTime2. It moves to the next record. If it's reached the end of the file, it will exit the loop, since there will be no new start time to use in the calculation

            dtmTime2 = ![[COLOR=darkred]StartTime[/COLOR]]
            rs2.AddNew
            rs2.Fields("[COLOR=darkred]ForeignKey[/COLOR]") = ![[COLOR=darkred]pkid[/COLOR]]
            rs2.Fields("[COLOR=darkred]Difference[/COLOR]") = DateDiff("s", dtmTime1, dtmTime2)
            rs2.Update

This part grabs the start time of the next record (which we moved to in the last bit of code). dtmTime1 is the finish time of the previous record. dtmTime2 is filled with the start time of the current record. A new record is created in the Temp table (referenced via the rs2.AddNew). The values are entered in the corresponding fields and the record is updated.

        End If
    Loop
Loop Until .EOF
 
.Close
End With

This part ends the If..then statement as well as tells the code to stop looping when the end of file is reached.

So, take all the code parts above and replace what was in the code with the new code. Make sure that you replace field names to match your database. Also make sure you keep these parts:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim dtmTime1 As Date
Dim dtmTime2 As Date
Dim strDeleteSql As String
strDeleteSql = "DELETE Table2.* FROM Table2;"
CurrentDb.Execute strDeleteSql
 
Set db = CurrentDb
Set rs = db.OpenRecordset("Query1") 
Set rs2 = db.OpenRecordset("Table2") 
 
With rs 
.MoveFirst

Edit:
NOTE: This only works if the times are entered IN ORDER. The reason being is that the query that the data is being pulled from is ordered by the Primary Key. As Doc explained earlier, the data in the table has no inheritant order. That is why the data is pulled from the query.

 

[Scooterbug]

One last alternative....

You can use a junction table to store the ID number of the Start and Stop times. Then create a sub query for the start times, a sub query for the stop times, then build a query using the sub queries and do the calculations there. I've attached a sample database to illustrate.

 

[CheapSlider]

Data from Previous Record (Queries only)

Can be acheived using queries only

The attached relies on the record numbers being consequtive in Scooterbugs "table 3"


Two queries are used:

"qry Prev"
Previous records

"qry Prev Finish - Start"
Differences between Start and Previous finish

This works so long as no records are deleted