While loop to get start/end values of condition

[LODIE]

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

[Rabbit]

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?

[ADezii]

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.

[LODIE]

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....

[ADezii]

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.

[Rabbit]

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.

[Rabbit]

Tested and verified.

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

Option Compare Database
Option Explicit
 
Public Sub RoadCondition()
    Dim rst1 As Recordset
    Dim rst2 As Recordset
    Dim rst3 As Recordset
    Dim rstart, rend, current As Integer
    Dim cstart, cend As Integer
 
    ' Opens a record set of all unique Road_id.
    Set rst1 = CurrentDb.OpenRecordset("SELECT DISTINCT Road_id FROM DataTable;")
 
    Do Until rst1.EOF
 
        ' Loops through all unique Road_id and sets start and end values of the Road_id
        rstart = DMin("start_Seal", "DataTable", "Road_id = '" & rst1!Road_id & "'")
        rend = DMax("End_seal", "DataTable", "Road_id = '" & rst1!Road_id & "'")
 
        ' Sets current position equal to start of road
        current = rstart
 
        ' Loops until current position reaches end of road
        Do Until current > rend
 
            ' Sets the values of the start of the section
            cstart = current
 
            ' Gets the most current values for the start of the road section
            Set rst2 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM DataTable WHERE Road_id = '" & rst1!Road_id & "'" & _
                " AND " & cstart & " BETWEEN start_Seal AND End_seal ORDER BY year DESC;")
 
            ' Retrieves the record with a start_Seal after the current position with a year later than current record's year
            Set rst3 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM DataTable WHERE Road_id = '" & rst1!Road_id & "'" & _
                " AND start_Seal > " & cstart & " AND start_Seal < " & rst2!End_seal & " AND year > " & rst2!Year & " ORDER BY start_Seal, year DESC;")
 
            ' If no such record exists, use the current record's End_seal, if it does, use the next record's start_Seal
            If rst3.RecordCount = 0 Then
                cend = rst2!End_seal
            Else
                cend = rst3!start_Seal - 1
            End If
 
            ' Inserts a record into final table for that section
            DoCmd.SetWarnings False
            DoCmd.RunSQL "INSERT INTO FinalTable (Road_id, start_Seal, End_seal, treatment, year) VALUES (" & _
                "'" & rst1!Road_id & "'," & cstart & "," & cend & ",'" & rst2!treatment & "'," & rst2!Year & ");"
            DoCmd.SetWarnings True
 
            ' Increment to next road section
            current = cend + 1
        Loop
 
        ' Moves to next Road_id
        rst1.MoveNext
 
    Loop
End Sub
 
Private Sub Command0_Click()
    ' Delete the records in the final table before calling the function
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM FinalTable"
    DoCmd.SetWarnings True
 
    Call RoadCondition
End Sub
 

[ADezii]

Tested and verified.

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

Option Compare Database
Option Explicit
 
Public Sub RoadCondition()
    Dim rst1 As Recordset
    Dim rst2 As Recordset
    Dim rst3 As Recordset
    Dim rstart, rend, current As Integer
    Dim cstart, cend As Integer
 
    ' Opens a record set of all unique Road_id.
    Set rst1 = CurrentDb.OpenRecordset("SELECT DISTINCT Road_id FROM DataTable;")
 
    Do Until rst1.EOF
 
        ' Loops through all unique Road_id and sets start and end values of the Road_id
        rstart = DMin("start_Seal", "DataTable", "Road_id = '" & rst1!Road_id & "'")
        rend = DMax("End_seal", "DataTable", "Road_id = '" & rst1!Road_id & "'")
 
        ' Sets current position equal to start of road
        current = rstart
 
        ' Loops until current position reaches end of road
        Do Until current > rend
 
            ' Sets the values of the start of the section
            cstart = current
 
            ' Gets the most current values for the start of the road section
            Set rst2 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM DataTable WHERE Road_id = '" & rst1!Road_id & "'" & _
                " AND " & cstart & " BETWEEN start_Seal AND End_seal ORDER BY year DESC;")
 
            ' Retrieves the record with a start_Seal after the current position with a year later than current record's year
            Set rst3 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM DataTable WHERE Road_id = '" & rst1!Road_id & "'" & _
                " AND start_Seal > " & cstart & " AND start_Seal < " & rst2!End_seal & " AND year > " & rst2!Year & " ORDER BY start_Seal, year DESC;")
 
            ' If no such record exists, use the current record's End_seal, if it does, use the next record's start_Seal
            If rst3.RecordCount = 0 Then
                cend = rst2!End_seal
            Else
                cend = rst3!start_Seal - 1
            End If
 
            ' Inserts a record into final table for that section
            DoCmd.SetWarnings False
            DoCmd.RunSQL "INSERT INTO FinalTable (Road_id, start_Seal, End_seal, treatment, year) VALUES (" & _
                "'" & rst1!Road_id & "'," & cstart & "," & cend & ",'" & rst2!treatment & "'," & rst2!Year & ");"
            DoCmd.SetWarnings True
 
            ' Increment to next road section
            current = cend + 1
        Loop
 
        ' Moves to next Road_id
        rst1.MoveNext
 
    Loop
End Sub
 
Private Sub Command0_Click()
    ' Delete the records in the final table before calling the function
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM FinalTable"
    DoCmd.SetWarnings True
 
    Call RoadCondition
End Sub
 

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?

[ADezii]

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?

[Rabbit]

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.

[Rabbit]

The results don't match your results exactly but they're accurate. Let me show you what I mean.

With the source data:

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
B    0    100    finalseal    1980
B    10    15    reseal    1981
B    20    25    reseal    1982
B    10    13    reseal    1983
B    21    22    reseal    1984
B    23    100    reseal    1985
 

The result is:

Road_id    start_Seal    End_seal    treatment    year
100:01    0    49    finalseal    1992
100:01    50    124    reseal    1997
100:01    125    150    reseal    2001
100:01    151    200    reseal    1997
B    0    9    finalseal    1980
B    10    13    reseal    1983
B    14    15    reseal    1981
B    16    19    finalseal    1980
B    20    20    reseal    1982
B    21    22    reseal    1984
B    23    100    reseal    1985
 

[JKing]

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.

[Rabbit]

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:

Dim rstart As Integer, rend As Integer, current As Integer
Dim cstart As Integer, cend As Integer
 

Now that's just a pain.

[JKing]

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:

Dim rstart As Integer, rend As Integer, current As Integer
Dim cstart As Integer, cend As Integer
 

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.

Dim int1, int2, int3 As Integer
 
Debug.Print TypeName(int1)
Debug.Print TypeName(int2)
Debug.Print TypeName(int3)
 

[Rabbit]

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.

Dim int1, int2, int3 As Integer
 
Debug.Print TypeName(int1)
Debug.Print TypeName(int2)
Debug.Print TypeName(int3)
 

At least now I know why I didn't get the context menu for rst1 when I used:
Dim rst1, rst2 As Recordset

[LODIE]

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);

 
'Increment to next road section
            current = cend + 1
            cstart = current
 
'I thought do while loop would take care of this but it didn't
            If current < rend Then
 
'check to see if a segment exists for the new cstart value
 
            Set rst4 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM seal_records WHERE ne_name = '" & rst1!ne_name & "'" & _
                " AND " & cstart & " BETWEEN from_chain AND to_chain ORDER BY year DESC;")
 
'if it doesn't exist find the start of the next sealed section and make this the new start
 
            If rst4.RecordCount = 0 Then
 
                 Set rst5 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM seal_records WHERE ne_name = '" & rst1!ne_name & "'" & _
                " AND from_chain > " & cstart & " ORDER BY from_chain, year DESC;")
 
                current = rst5!from_chain
 
            Else                      'carry on
            current = cstart
             End If
 

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

[Rabbit]

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);

 
'Increment to next road section
            current = cend + 1
            cstart = current
 
'I thought do while loop would take care of this but it didn't
            If current < rend Then
 
'check to see if a segment exists for the new cstart value
 
            Set rst4 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM seal_records WHERE ne_name = '" & rst1!ne_name & "'" & _
                " AND " & cstart & " BETWEEN from_chain AND to_chain ORDER BY year DESC;")
 
'if it doesn't exist find the start of the next sealed section and make this the new start
 
            If rst4.RecordCount = 0 Then
 
                 Set rst5 = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM seal_records WHERE ne_name = '" & rst1!ne_name & "'" & _
                " AND from_chain > " & cstart & " ORDER BY from_chain, year DESC;")
 
                current = rst5!from_chain
 
            Else                      'carry on
            current = cstart
             End If
 

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.