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

While loop to get start/end values of condition

P: 6
Hello All,

I was hoping that someone might have a suggestion on the best way to complete the follwoing problem.

I have a bunch of roads. Each road has a Start and end Distance (chainage in metres) and may have been sealed in multiple sections over the last 40 years. Each seal section also has a start and an end value and they may overlap each other.

I have managed to get this information into a stocck type graph which displays the seal in a distance verses year type format. From this I can visually see the age of the road and how the sections should be broken up.

I want to be able to summarize this data to automatically get the the latest seal with a start and end value for the entrire length of road.

I see this kind of done in a while loop where X = total length of road,
XS = value of start of segments
XE = Vale of end of segment
Y = year of seal

therefore

XS = 0
XE= 0
while XE<= X

get Y value
increment XE value until Y value changes
write XS XE Y to table
set XS to equal last XE
and repeat until end of road reached.

The final result that I want is a table of ROAD_ID, Start, End, Year of seal.

Hopefully that all makes sense.

I am probably just searching for the wrong words but if anyone has any examples that I can refer to that would be fantastic.

Cheers,

Laura
Aug 10 '07 #1
Share this Question
Share on Google+
16 Replies


Rabbit
Expert Mod 10K+
P: 12,357
It's a little hard to follow, can you post up an example of the data before hand, and what it should look like after?
Aug 10 '07 #2

ADezii
Expert 5K+
P: 8,623
Hello All,

I was hoping that someone might have a suggestion on the best way to complete the follwoing problem.

I have a bunch of roads. Each road has a Start and end Distance (chainage in metres) and may have been sealed in multiple sections over the last 40 years. Each seal section also has a start and an end value and they may overlap each other.

I have managed to get this information into a stocck type graph which displays the seal in a distance verses year type format. From this I can visually see the age of the road and how the sections should be broken up.

I want to be able to summarize this data to automatically get the the latest seal with a start and end value for the entrire length of road.

I see this kind of done in a while loop where X = total length of road,
XS = value of start of segments
XE = Vale of end of segment
Y = year of seal

therefore

XS = 0
XE= 0
while XE<= X

get Y value
increment XE value until Y value changes
write XS XE Y to table
set XS to equal last XE
and repeat until end of road reached.

The final result that I want is a table of ROAD_ID, Start, End, Year of seal.

Hopefully that all makes sense.

I am probably just searching for the wrong words but if anyone has any examples that I can refer to that would be fantastic.

Cheers,

Laura
Just subscribing and waiting for data from OP.
Aug 10 '07 #3

P: 6
Just subscribing and waiting for data from OP.
Sorry about that, I should have posted an example.

Just say that I have the following data for a road

Road_id start_Seal End_seal treatment year
100:01 0 100 primerseal 1981
100:01 0 100 finalseal 1992
100:01 50 200 reseal 1997
100:01 125 150 reseal 2001


I want to rebreak this information into logical segments which will give a linear timeline (in this case lengthline) to give distance vs age.

ie


Road_id start_Seal End_seal treatment year
100:01 0 50 final seal 1992
100:01 50 125 reseal 1997
100:01 125 150 reseal 2001
100:01 150 200 reseal 1997


segments are now 0-50, 50-125, 125-150, 150-200 with only the latest treatment and year associated with each distance along the road.

These results will be combined with other data into a formula that will be used to predict when a road will need to be rebuilt.

Hopefully that makes a little more sense....
Aug 12 '07 #4

ADezii
Expert 5K+
P: 8,623
Sorry about that, I should have posted an example.

Just say that I have the following data for a road

Road_id start_Seal End_seal treatment year
100:01 0 100 primerseal 1981
100:01 0 100 finalseal 1992
100:01 50 200 reseal 1997
100:01 125 150 reseal 2001


I want to rebreak this information into logical segments which will give a linear timeline (in this case lengthline) to give distance vs age.

ie


Road_id start_Seal End_seal treatment year
100:01 0 50 final seal 1992
100:01 50 125 reseal 1997
100:01 125 150 reseal 2001
100:01 150 200 reseal 1997


segments are now 0-50, 50-125, 125-150, 150-200 with only the latest treatment and year associated with each distance along the road.

These results will be combined with other data into a formula that will be used to predict when a road will need to be rebuilt.

Hopefully that makes a little more sense....
I'm gonna sleep on this one tonight and hopefully I'll have a solution for you soon. I'll be in touch.
Aug 13 '07 #5

Rabbit
Expert Mod 10K+
P: 12,357
Complicated...

Gonna throw around an idea until I can get into the office and test it out. Forgive the rambling.

1) Take start, type, and year from current record.

2) Use start of next highest record as the end. Subquery.

3) If no higher record, use current end.

4) Keep record only if it's the most recent year for that start.

No... that's not gonna work. It won't give you the last record. Looks like we'll have to use VBA.
Aug 13 '07 #6

Rabbit
Expert Mod 10K+
P: 12,357
Tested and verified.

DataTable and FinalTable
Road_id; Text
start_Seal; Number
End_seal; Number
treatment; Text
year; Number

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub RoadCondition()
  5.     Dim rst1 As Recordset
  6.     Dim rst2 As Recordset
  7.     Dim rst3 As Recordset
  8.     Dim rstart, rend, current As Integer
  9.     Dim cstart, cend As Integer
  10.  
  11.     ' Opens a record set of all unique Road_id.
  12.     Set rst1 = CurrentDb.OpenRecordset("SELECT DISTINCT Road_id FROM DataTable;")
  13.  
  14.     Do Until rst1.EOF
  15.  
  16.         ' Loops through all unique Road_id and sets start and end values of the Road_id
  17.         rstart = DMin("start_Seal", "DataTable", "Road_id = '" & rst1!Road_id & "'")
  18.         rend = DMax("End_seal", "DataTable", "Road_id = '" & rst1!Road_id & "'")
  19.  
  20.         ' Sets current position equal to start of road
  21.         current = rstart
  22.  
  23.         ' Loops until current position reaches end of road
  24.         Do Until current > rend
  25.  
  26.             ' Sets the values of the start of the section
  27.             cstart = current
  28.  
  29.             ' Gets the most current values for the start of the road section
  30.             Set rst2 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM DataTable WHERE Road_id = '" & rst1!Road_id & "'" & _
  31.                 " AND " & cstart & " BETWEEN start_Seal AND End_seal ORDER BY year DESC;")
  32.  
  33.             ' Retrieves the record with a start_Seal after the current position with a year later than current record's year
  34.             Set rst3 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM DataTable WHERE Road_id = '" & rst1!Road_id & "'" & _
  35.                 " AND start_Seal > " & cstart & " AND start_Seal < " & rst2!End_seal & " AND year > " & rst2!Year & " ORDER BY start_Seal, year DESC;")
  36.  
  37.             ' If no such record exists, use the current record's End_seal, if it does, use the next record's start_Seal
  38.             If rst3.RecordCount = 0 Then
  39.                 cend = rst2!End_seal
  40.             Else
  41.                 cend = rst3!start_Seal - 1
  42.             End If
  43.  
  44.             ' Inserts a record into final table for that section
  45.             DoCmd.SetWarnings False
  46.             DoCmd.RunSQL "INSERT INTO FinalTable (Road_id, start_Seal, End_seal, treatment, year) VALUES (" & _
  47.                 "'" & rst1!Road_id & "'," & cstart & "," & cend & ",'" & rst2!treatment & "'," & rst2!Year & ");"
  48.             DoCmd.SetWarnings True
  49.  
  50.             ' Increment to next road section
  51.             current = cend + 1
  52.         Loop
  53.  
  54.         ' Moves to next Road_id
  55.         rst1.MoveNext
  56.  
  57.     Loop
  58. End Sub
  59.  
  60. Private Sub Command0_Click()
  61.     ' Delete the records in the final table before calling the function
  62.     DoCmd.SetWarnings False
  63.     DoCmd.RunSQL "DELETE * FROM FinalTable"
  64.     DoCmd.SetWarnings True
  65.  
  66.     Call RoadCondition
  67. End Sub
  68.  
Aug 13 '07 #7

ADezii
Expert 5K+
P: 8,623
Tested and verified.

DataTable and FinalTable
Road_id; Text
start_Seal; Number
End_seal; Number
treatment; Text
year; Number

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub RoadCondition()
  5.     Dim rst1 As Recordset
  6.     Dim rst2 As Recordset
  7.     Dim rst3 As Recordset
  8.     Dim rstart, rend, current As Integer
  9.     Dim cstart, cend As Integer
  10.  
  11.     ' Opens a record set of all unique Road_id.
  12.     Set rst1 = CurrentDb.OpenRecordset("SELECT DISTINCT Road_id FROM DataTable;")
  13.  
  14.     Do Until rst1.EOF
  15.  
  16.         ' Loops through all unique Road_id and sets start and end values of the Road_id
  17.         rstart = DMin("start_Seal", "DataTable", "Road_id = '" & rst1!Road_id & "'")
  18.         rend = DMax("End_seal", "DataTable", "Road_id = '" & rst1!Road_id & "'")
  19.  
  20.         ' Sets current position equal to start of road
  21.         current = rstart
  22.  
  23.         ' Loops until current position reaches end of road
  24.         Do Until current > rend
  25.  
  26.             ' Sets the values of the start of the section
  27.             cstart = current
  28.  
  29.             ' Gets the most current values for the start of the road section
  30.             Set rst2 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM DataTable WHERE Road_id = '" & rst1!Road_id & "'" & _
  31.                 " AND " & cstart & " BETWEEN start_Seal AND End_seal ORDER BY year DESC;")
  32.  
  33.             ' Retrieves the record with a start_Seal after the current position with a year later than current record's year
  34.             Set rst3 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM DataTable WHERE Road_id = '" & rst1!Road_id & "'" & _
  35.                 " AND start_Seal > " & cstart & " AND start_Seal < " & rst2!End_seal & " AND year > " & rst2!Year & " ORDER BY start_Seal, year DESC;")
  36.  
  37.             ' If no such record exists, use the current record's End_seal, if it does, use the next record's start_Seal
  38.             If rst3.RecordCount = 0 Then
  39.                 cend = rst2!End_seal
  40.             Else
  41.                 cend = rst3!start_Seal - 1
  42.             End If
  43.  
  44.             ' Inserts a record into final table for that section
  45.             DoCmd.SetWarnings False
  46.             DoCmd.RunSQL "INSERT INTO FinalTable (Road_id, start_Seal, End_seal, treatment, year) VALUES (" & _
  47.                 "'" & rst1!Road_id & "'," & cstart & "," & cend & ",'" & rst2!treatment & "'," & rst2!Year & ");"
  48.             DoCmd.SetWarnings True
  49.  
  50.             ' Increment to next road section
  51.             current = cend + 1
  52.         Loop
  53.  
  54.         ' Moves to next Road_id
  55.         rst1.MoveNext
  56.  
  57.     Loop
  58. End Sub
  59.  
  60. Private Sub Command0_Click()
  61.     ' Delete the records in the final table before calling the function
  62.     DoCmd.SetWarnings False
  63.     DoCmd.RunSQL "DELETE * FROM FinalTable"
  64.     DoCmd.SetWarnings True
  65.  
  66.     Call RoadCondition
  67. End Sub
  68.  
Nice solution Rabbit, and thanks for saving me a lot of time especially since I was experiencing a mental block anyway, and probably would not have arrived at a solution. I am curious about one think - is there any special reason why rstart, cstart, and rend are declared as Variants?
Aug 13 '07 #8

ADezii
Expert 5K+
P: 8,623
Complicated...

Gonna throw around an idea until I can get into the office and test it out. Forgive the rambling.

1) Take start, type, and year from current record.

2) Use start of next highest record as the end. Subquery.

3) If no higher record, use current end.

4) Keep record only if it's the most recent year for that start.

No... that's not gonna work. It won't give you the last record. Looks like we'll have to use VBA.
Nice solution Rabbit, and thanks for saving me a lot of time especially since I was experiencing a mental block anyway, and probably would not have arrived at a solution. I am curious about one think - is there any special reason why rstart, cstart, and rend are declared as Variants?
Aug 13 '07 #9

Rabbit
Expert Mod 10K+
P: 12,357
Nice solution Rabbit, and thanks for saving me a lot of time especially since I was experiencing a mental block anyway, and probably would not have arrived at a solution. I am curious about one think - is there any special reason why rstart, cstart, and rend are declared as Variants?
uhh.. the reason is that you should see an ophthalmologist? Unless I made a mistake and you edited my post, I declared them as Integers. You were probably glancing through it and saw me declare the integers over two lines and thought the other was a different data type. I only did it over two lines on a whim.
Aug 13 '07 #10

Rabbit
Expert Mod 10K+
P: 12,357
The results don't match your results exactly but they're accurate. Let me show you what I mean.

With the source data:
Expand|Select|Wrap|Line Numbers
  1. Road_id    start_Seal    End_seal    treatment    year
  2. 100:01    0    100    primerseal    1981
  3. 100:01    0    100    finalseal    1992
  4. 100:01    50    200    reseal    1997
  5. 100:01    125    150    reseal    2001
  6. B    0    100    finalseal    1980
  7. B    10    15    reseal    1981
  8. B    20    25    reseal    1982
  9. B    10    13    reseal    1983
  10. B    21    22    reseal    1984
  11. B    23    100    reseal    1985
  12.  
The result is:
Expand|Select|Wrap|Line Numbers
  1. Road_id    start_Seal    End_seal    treatment    year
  2. 100:01    0    49    finalseal    1992
  3. 100:01    50    124    reseal    1997
  4. 100:01    125    150    reseal    2001
  5. 100:01    151    200    reseal    1997
  6. B    0    9    finalseal    1980
  7. B    10    13    reseal    1983
  8. B    14    15    reseal    1981
  9. B    16    19    finalseal    1980
  10. B    20    20    reseal    1982
  11. B    21    22    reseal    1984
  12. B    23    100    reseal    1985
  13.  
Aug 13 '07 #11

JKing
Expert 100+
P: 1,206
uhh.. the reason is that you should see an ophthalmologist? Unless I made a mistake and you edited my post, I declared them as Integers. You were probably glancing through it and saw me declare the integers over two lines and thought the other was a different data type. I only did it over two lines on a whim.
Hey Rabbit. Adezii is correct in this. You unknowingly declared these variables as variants. You maybe thinking we're both crazy but it's really VBA that is crazy. In other languages declaring multiple variables of a single data type on one line is done in this manner. In VBA you must define the data type for each variable or else it is assumed as a variant.
Aug 13 '07 #12

Rabbit
Expert Mod 10K+
P: 12,357
Hey Rabbit. Adezii is correct in this. You unknowingly declared these variables as variants. You maybe thinking we're both crazy but it's really VBA that is crazy. In other languages declaring multiple variables of a single data type on one line is done in this manner. In VBA you must define the data type for each variable or else it is assumed as a variant.
Ok... I did not know that lol. This whole time I've been declaring variants... no wonder I didn't get the context menu sometimes...

Well that's just ridiculous. You're saying I have to type:
Expand|Select|Wrap|Line Numbers
  1. Dim rstart As Integer, rend As Integer, current As Integer
  2. Dim cstart As Integer, cend As Integer
  3.  
Now that's just a pain.
Aug 13 '07 #13

JKing
Expert 100+
P: 1,206
Ok... I did not know that lol. This whole time I've been declaring variants... no wonder I didn't get the context menu sometimes...

Well that's just ridiculous. You're saying I have to type:
Expand|Select|Wrap|Line Numbers
  1. Dim rstart As Integer, rend As Integer, current As Integer
  2. Dim cstart As Integer, cend As Integer
  3.  
Now that's just a pain.
Yes, that's exactly what I'm saying. Really the only thing you're saving yourself from is typing "Dim" multiple times.

If you wanted to verify this give this example a shot. You'll see that the third variable is the only one that returns the Integer data type.
Expand|Select|Wrap|Line Numbers
  1. Dim int1, int2, int3 As Integer
  2.  
  3. Debug.Print TypeName(int1)
  4. Debug.Print TypeName(int2)
  5. Debug.Print TypeName(int3)
  6.  
Aug 13 '07 #14

Rabbit
Expert Mod 10K+
P: 12,357
Yes, that's exactly what I'm saying. Really the only thing you're saving yourself from is typing "Dim" multiple times.

If you wanted to verify this give this example a shot. You'll see that the third variable is the only one that returns the Integer data type.
Expand|Select|Wrap|Line Numbers
  1. Dim int1, int2, int3 As Integer
  2.  
  3. Debug.Print TypeName(int1)
  4. Debug.Print TypeName(int2)
  5. Debug.Print TypeName(int3)
  6.  
At least now I know why I didn't get the context menu for rst1 when I used:
Dim rst1, rst2 As Recordset
Aug 13 '07 #15

P: 6
Rabbit, thanks heaps for that, you are an absolute legend. I wasn't expecting a whole routine written just some suggestions on how to go about it.

And thank you to everyone else for thinking about it and responding to Rabbits solution, the whole discussion helped me to understand alot more what was going on.

I had to make a couple of changes.

First I had to check my data as I had a couple of entries that had a Start chain greater than the end chain. I needed to fix this anyway.

Second, I forgot about the scenario where the road is not continuously sealed (ie sealed at the start, dirt in the middle and sealed at the end) so I had to add in a function to increment to the next start chain if there was no entry for the "end + 1" part.

something like as follows at the end at the increment part ( a bit messy I know but I am just a hack not an expert);


Expand|Select|Wrap|Line Numbers
  1.  
  2. 'Increment to next road section
  3.             current = cend + 1
  4.             cstart = current
  5.  
  6. 'I thought do while loop would take care of this but it didn't
  7.             If current < rend Then
  8.  
  9. 'check to see if a segment exists for the new cstart value
  10.  
  11.             Set rst4 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM seal_records WHERE ne_name = '" & rst1!ne_name & "'" & _
  12.                 " AND " & cstart & " BETWEEN from_chain AND to_chain ORDER BY year DESC;")
  13.  
  14. 'if it doesn't exist find the start of the next sealed section and make this the new start
  15.  
  16.             If rst4.RecordCount = 0 Then
  17.  
  18.                  Set rst5 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM seal_records WHERE ne_name = '" & rst1!ne_name & "'" & _
  19.                 " AND from_chain > " & cstart & " ORDER BY from_chain, year DESC;")
  20.  
  21.                 current = rst5!from_chain
  22.  
  23.             Else                      'carry on
  24.             current = cstart
  25.              End If
  26.  
The last issue I had was that I kept getting an overflow runtime error. After a quick search I found that I needed to change the integers to long in the Variable statements.

Anyway, It all seems to work now. Once again thank you heaps your assistance is really really apreciated.

Cheers,

Laura
Aug 14 '07 #16

Rabbit
Expert Mod 10K+
P: 12,357
Rabbit, thanks heaps for that, you are an absolute legend. I wasn't expecting a whole routine written just some suggestions on how to go about it.

And thank you to everyone else for thinking about it and responding to Rabbits solution, the whole discussion helped me to understand alot more what was going on.

I had to make a couple of changes.

First I had to check my data as I had a couple of entries that had a Start chain greater than the end chain. I needed to fix this anyway.

Second, I forgot about the scenario where the road is not continuously sealed (ie sealed at the start, dirt in the middle and sealed at the end) so I had to add in a function to increment to the next start chain if there was no entry for the "end + 1" part.

something like as follows at the end at the increment part ( a bit messy I know but I am just a hack not an expert);


Expand|Select|Wrap|Line Numbers
  1.  
  2. 'Increment to next road section
  3.             current = cend + 1
  4.             cstart = current
  5.  
  6. 'I thought do while loop would take care of this but it didn't
  7.             If current < rend Then
  8.  
  9. 'check to see if a segment exists for the new cstart value
  10.  
  11.             Set rst4 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM seal_records WHERE ne_name = '" & rst1!ne_name & "'" & _
  12.                 " AND " & cstart & " BETWEEN from_chain AND to_chain ORDER BY year DESC;")
  13.  
  14. 'if it doesn't exist find the start of the next sealed section and make this the new start
  15.  
  16.             If rst4.RecordCount = 0 Then
  17.  
  18.                  Set rst5 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM seal_records WHERE ne_name = '" & rst1!ne_name & "'" & _
  19.                 " AND from_chain > " & cstart & " ORDER BY from_chain, year DESC;")
  20.  
  21.                 current = rst5!from_chain
  22.  
  23.             Else                      'carry on
  24.             current = cstart
  25.              End If
  26.  
The last issue I had was that I kept getting an overflow runtime error. After a quick search I found that I needed to change the integers to long in the Variable statements.

Anyway, It all seems to work now. Once again thank you heaps your assistance is really really apreciated.

Cheers,

Laura
Not a problem. Good luck.
Aug 14 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.