循环记录存取

Question

我有五站

我原表行程中有三列，我想根据逻辑对方向进行编码。

如果我的车从A到E，或者在那个方向(B，the )的任何一站，方向是南边。

如果我的车从E到A，或在那个方向(E-C，D-A)的任何一站，方向是北。

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      

我希望得到以下结果：

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

有人知道如何在access、vba或query中对其进行编码吗？真正的桌子有超过5站。

Answer 1

我想这会给你一些想法

 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