CODEWITHSUNDEEP
sqlms-accessvba
Sailormoon
Sailormoon

Reputation: 267

Loop Records Access

I have five Stops

--------A-------------B------------C------------D------------E

I have Three columns in original table [Trip], I want to code the direction base on the logic

If my car goes from A to E, or any stops for that direction(B-D,C-E),the direction is South

If my car goes from E to A,or any stops for that direction(E-C,D-A),the direction is North

Trip    CarID   Stops
1      1000      A      
1      1000      B     
1      1000      C     
2      1001      C     
2      1001      D     
2      1001      E     
3      1002      D     
3      1002      C      
3      1002      B

I want the Result below: 

Trip    CarID   Stops   Direction
1      1000      A      South
1      1000      B      South
1      1000      C      South
2      1001      C      South
2      1001      D      South
2      1001      E      South
3      1002      D      North
3      1002      C      North
3      1002      B      North

Anyone knows how to code this in access vba or query? The real table has more than 5 stops.

Upvotes: 0

Views: 54

Answers (1)

John
John

Reputation: 1004

I guess this will give some ideas 

 Private Sub Command0_Click()
 On Error Resume Next
 Dim rst As DAO.Recordset
 Dim PreviousStop As String
 Dim Direction As String
 Dim PreviousDirection As String
 Dim rstCounter As Integer
 Dim currentCar As String
 Set rst = CurrentDb.OpenRecordset("Trip")
 With rst
    rstCounter = rst.RecordCount
    .MoveFirst
    Do Until .EOF
        If rstCounter = 1 Then
            Direction = calculatedDirection(PreviousStop, .Fields("Stops"))
            .Edit
            .Fields("Direction") = Direction
            .Update
            Exit Do
        Else
            PreviousStop = .Fields("Stops")
            currentCar = .Fields("CarID")
            .MoveNext
            If currentCar <> .Fields("CarID") Then
                PreviousDirection = ""
            Else
                If Len(PreviousDirection) > 0 Then
                    Direction = PreviousDirection
                Else
                    Direction = calculatedDirection(PreviousStop,     .Fields("Stops"))
                    PreviousDirection = Direction
                End If
            End If
            .MovePrevious
            If Len(Direction) > 0 Then
                .Edit
                .Fields("Direction") = Direction
                .Update
            End If
            .MoveNext
        End If
        rstCounter = rstCounter - 1
      Loop
  End With
End Sub  

Private Function calculatedDirection(PreviousStop As String, CurrentStop As String) As String
If PreviousStop > CurrentStop Then
            calculatedDirection = "North"
        Else
            calculatedDirection = "South"
        End If
End Function

Upvotes: 1

Related Questions