title: Loop Records Access
body: <p>I have five Stops</p>
  
  <p>--------A-------------B------------C------------D------------E</p>
  
  <p>I have Three columns in original table [Trip], I want to code the direction base on the logic </p>
  
  <p>If my car goes from A to E, or any stops for that direction(B-D,C-E),the direction is South</p>
  
  <p>If my car goes from E to A,or any stops for that direction(E-C,D-A),the direction is North</p>
  
  <pre><code>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      
  </code></pre>
  
  <p>I want the Result below: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Anyone knows how to code this in access vba or query? The real table has more than 5 stops.</p>