title: Fill data down based on prior Row information similar to Excel auto-fill
body: <p>I have a MS Access Database table with 3 fields.  I need to populate a 4th field with the results from the 3rd field of a prior Row/record.  However I need it to keep populating that same result down until certain criteria in field 1 is met, then it should change what is being filled down and start populating the new results down.</p>
  
  <p>Example of existing data</p>
  
  <pre><code>    ID--Field1--Field2
      01-----A-----ACH
      02-----H-----7
      03-----D-----Sun
      04-----A-----DTD
      05-----H-----123456
      06-----T-----9
      07-----B-----Moon
      08-----P-----1
      09-----A-----EBX
      10-----H-----Stars
      11-----T-----25
      12-----B-----97Street
  </code></pre>
  
  <p>I want the data to populate Field 3 with the same results in each row until the criteria in Field 1 = "A" and then change to what Field 2 is for the next group.  (having a hard time explaining sorry)</p>
  
  <p>Example of what I want:</p>
  
  <pre><code>    ID--Field1--Field2---Field 3
      01-----A-----ACH-----ACH
      02-----H-----7-------ACH
      03-----D-----Sun-----ACH
      04-----A-----DTD-----DTD
      05-----H-----123456--DTD
      06-----T-----9-------DTD
      07-----B-----Moon----DTD
      08-----P-----1-------DTD
      09-----A-----EBX-----EBX
      10-----H-----Stars---EBX
      11-----T-----25------EBX
      12-----B-----97St----EBX
  </code></pre>
  
  <p>I built a query that does this but locks up the system because it is too cumbersome.</p>
  
  <p>Field3: IIf([field1]="A",[field2],(DLookUp("[Field3]","Query1","[ID]=" &amp; [ID]-1)))</p>
  
  <p>Basically I use the If statement to get when the Change should happen and the Dlookup to pull whatever was in the prior line of Field 3.  You can see why it might be cumbersome when processing hundreds of records.</p>
  
  <p>I searched through many resources and found similar problems with solutions and sql code to pull the prior information but can't seem to find a solution to populate down until the next "A" is found in Field 1.</p>
  
  <p>Please help!!!  Thanks</p>