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.