adding travel times without regards for timestamp

rossmg9

Registered User.
Local time
Today, 04:17
Joined
Oct 28, 2008
Messages
34
I have the attached database file with the form CalcTravelTime. I also have a code associated with the command button as you can see. The code lets the travel time step up to the next 5 minute interval after the travel time gets over 300 seconds or 5 minutes.

Basically what I need help doing is to eliminate the way the code steps up to the next 5 minute interval. Whether that be changing the code or eliminating part of the code. That is the part I'm not sure of.

So really I still need the total travel time summation but without the step up to the next interval. Also I would like to keep it in the same format as the attached file if possible. Thank you
 

Attachments

Not sure I 'll have time to look at this further.

Just wanted to say I didn't understand the problem. On the one hand you seem to be deliberately stepping up the time interval by 5 minutes. On the other hand you seem to be saying that this is what you want to avoid. I don't get it. (I'm a bit slow).
 
jal, if you're slow so am I. I don't understand either.
 
Thats exactly what I'm trying to do so your not slow. I got the code from a colleague and it is stepping up to the next interval in the attached DB. But I am looking for a way to modify the code to get rid of that 5 minute step up...Does that help at all?
 
I still don't understand any of this. I don't understand who's traveling and why, what information is needed, who needs it, why they need it, what a segment is, what a segment is used for, what an interval is, what "stepping up" is, why it is stepped up, what time is being calculated, why it is being calculated, what your form is currently doing, what it is you want it to do, etc, etc, etc.
 
jal I figured out the line of code that I need to delete from the code in my DB it was:

miscRS.Filter = "segTimeStamp = #" & currTime & "# AND segment >" & currSegment

But i have a new problem that maybe you can help with and I tried to explain it the best way I could so here it goes

I have average travel times over certain roadways segments in a road network (segments 1-15 in my DB). The travel times are averaged over five minute intervals (the average travel time for segment x from 12:00 am-12:05 am, for 12:05 am-12:10 am, etc.) throughout the day from raw speed data that I have access to. So in my DB I have the average travel time over every five minute interval throughout the day for every segment.

The next part, which is the topic of my project, is the total travel time. So when I want to find total travel time from segment x to segment y then I must add all the travel times between those two segment to get total travel time. But the catch is, for example, if the total travel time is more than five minutes from segment x to segment y I have to know at what segment BECAUSE once the total travel time is over 5 minutes then i must now use the travel times associated with the next timestamp or five minute interval because the travel times associated with the previous five minute interval are useless since I already have a travel time of 5 miuntes. This is the stepping up of travel times I mentioned in the first post and this is called my actual travel time.

The posted travel time is the same as the above explanation but once the total travel time is over five minutes then it doesn't move to the next five minute interval.

Now I have this information in the two forms attached. But what I would like to do is have this information side by side in a query or table so that I can compare it, the actual and posted travel time, that is.

It will probably involve joining two tables but first I need to get the information from the form into a table so I can join them, but that is the part where I am stuck.

I'm hoping that I can just use the code that I have attached to the command button in the form to the VB editor but then I need to define it in the fieldlist section of the query and am not sure how to do that either.

An example of what I am looking for from the table will be, Every combination of segment to segment at every five minute interval.

Example: So total travel time from segment 1 to segment 2,3,4,5,6,7,8,9,10,11,12,13,14,15 at every 5 minute interval throughout the day (12:00 am, 12:05 am, 12:10 am, etc.)

Then total travel time from segment 2 to segment 3,4,5,6,7,8,9,10,11,12,13,14,15 at every 5 minute interval

etc. for posted and actual travel time for comparison


I hope that helped and thank you for the reply back, please let me know if explanation is not clear enough. Thank you
 
You cleared up some things. Part of the problem here is that I have a pretty severe malady that makes my health worse every year (it's a miracle I'm still alive). Right now, I am so exhausted I can barely understand 2 plus 2. I'll take a stab at this, though, until I fall asleep.
 
Let's consider segments 1 to 10 at 1:00 AM. When I total segments 1 to 7, including 7, I surpass the five minute limit.

At that point, for segments 8 to 10, I have to pretend as though the user had selected 1:05 AM (instead of 1:00 AM), according to you.

The question is regarding item 7. Should I be using 1:00 AM for that segment, or 1:05 AM?

For now I am assuming 1:00 AM.
 
Ok, here's how to calculate one totalTime value based on the user's criteria on the form.

Private Sub calcTime_Click()
If Nz(Me.StartTime, 0) = 0 Or Nz(Me.StartSegment, 0) = 0 Or Nz(Me.endSegment, 0) = 0 Then Exit Sub
Dim TheIntervalSelectedByUser As Date, TheIntervalAfterTheUsersSelection As Date
TheIntervalSelectedByUser = CDate(Me.StartTime)
TheIntervalAfterTheUsersSelection = DateAdd("n", 5, TheIntervalSelectedByUser)
Dim rs As New ADODB.Recordset
'The AND clause below is optional. Improves performance. I thought it would help with the logic too but not really.
Dim sql As String
sql = "SELECT Segment, segTimeStamp, [Travel Time] " & _
"FROM TravelSegments " & _
"WHERE Segment >= " & Me.StartSegment & " AND Segment <= " & Me.endSegment & " " & _
"AND (CDate(segTimeStamp) = #" & TheIntervalSelectedByUser & "# OR CDate(SegTimeStamp) = #" & _
TheIntervalAfterTheUsersSelection & "#) " & _
"ORDER BY segment, CDate(segTimeStamp) "
rs.Open sql, CurrentProject.Connection
Dim totalTravelTime As Double
Dim LastSegmentUsed As Long 'to prevent the cutoff segment from being used twice.
Do While Not rs.EOF
If CDate(rs!SegTimeStamp) = TheIntervalSelectedByUser And totalTravelTime < 300 Then
totalTravelTime = totalTravelTime + rs![Travel Time]
LastSegmentUsed = rs!Segment
ElseIf CDate(rs!SegTimeStamp) = TheIntervalAfterTheUsersSelection And totalTravelTime >= 300 Then
If LastSegmentUsed <> rs!Segment Then totalTravelTime = totalTravelTime + rs![Travel Time]
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Me.ttlTravel = Math.Round(totalTravelTime, 2)
End Sub
 
Add this line to the start of the loop to calculate the postedTime (well, also Dim out postedTime as a Double variable before the loop starts).

If CDate(rs!SegTimeStamp) = TheIntervalSelectedByUser Then postedTime = postedTime + rs![Travel Time]
 
I took a stab at your "all possible combinations" request (see attachment).

The loop has to execute thousands of iterations. I stepped through one iteration and then became too tired to test it further. Definitely needs more testing to ensure that I nested the loops correctly.

To execute the loop, click the Calcuate All button on the form.

Not sure how long it takes to generate the output - probably 15 minutess but could be double that for all I know. It outputs the result into a table called CalculateAll.

Note: I changed the Datatype of Segment from Double to Long in your table.
 

Attachments

That is impressive..thank you but just have a few questions.

First to answer your question about your example, you were right to assume to use the travel time for segment 7 at 1:00 am instead of 1:05 am.

You mentioned to add the line of code, I just used your DB as a template for my new DB and all travel times seemed to be correct when compared to the forms I used before. Do I need that line of code for any reason?

Just to let you know it took about 1 hour for the output to be generated and a couple of times my computer froze, maybe because of size or CPU, just FYI.

I have one more question if you are up for it. :)
All the data you saw was from one day, say I wanted to add more days like possibly a month or more. Would it just be as simple as add a line of code to previous code or would it be more in depth than that?

Thank you again. I really appreciate it
 
Also I'm assuming that the final summation of both travel times is rounded to the nearest whole number after all individual travel times between segments were not rounded before summation (though in you DB the final travel times are not rounded to nearest whole number and mine are). Just asking for when I'm comparing travel time with previous answers from forms..Thanks
 
After further observation, I am having a little trouble with the totalTravelTime. I tried to insert code where you mentioned but got error messages. Do I place it in the code for calcTime_Click loop or the last part of code, in that loop?
 
After further observation, I am having a little trouble with the totalTravelTime. I tried to insert code where you mentioned but got error messages. Do I place it in the code for calcTime_Click loop or the last part of code, in that loop?

I thought you said you wanted to calculate postedTime (in addition to TTT) when the user clicked "Calculate" on the form. I thought you said that PostedTime is the same as TTT except there is no stepping up. If so, that line of code should calculate PostedTime (and then you can decide where to display it) if you put it in the original button click event (not the button event that I added myself).
 
All the data you saw was from one day, say I wanted to add more days like possibly a month or more. Would it just be as simple as add a line of code to previous code or would it be more in depth than that?

At this point (haven't given it much thought) I doubt that you would need to change any code. Maybe I'm missing something?
 
Also I'm assuming that the final summation of both travel times is rounded to the nearest whole number after all individual travel times between segments were not rounded before summation (though in you DB the final travel times are not rounded to nearest whole number and mine are). Just asking for when I'm comparing travel time with previous answers from forms..Thanks

If I recall, I didn't do any rounding at all, except for display purposes, in the textbox on the form. I did no rounding when building the all-combo table, as best I can recall.
 
No, I wanted the two to be displayed side by side in a table format in order to compare them. The posted TT in the table is the same as TTT but no stepping up. The information you gave me for the CalculateAll table is mostly right. It has the information side-by-side like I need amd it seems to calculate the posted travel time correctly but there are a few instance where the TTT doesn't appear to be calculating correctly.

I mentioned in a previous post that I have two forms in two different DB's that each calculate the posted TT and the TTT but I wanted to combine them in a table form to compare them. I will attach them. The posted from yesterday at 1:00 will explain what each does. (11-3-08 is TTT, 11-5-08-test is the posted)
 

Attachments

At this point (haven't given it much thought) I doubt that you would need to change any code. Maybe I'm missing something?

I was asking because I may need to add more days data and if I were to do that then maybe I would have to add a date column to distinguish the timestamps
 

Users who are viewing this thread

Back
Top Bottom