title: Access SQL: Updating Children in a 1 to Many relationship based on criteria
body: <p>My question is given a 1-Many relation ship where the children are stored on 1 ChildTable,</p>
  
  <pre><code> ChildID | ParentID | Data2 | InputDate 
   ------------------------------------
    1      |  345     |   100 | 3-5-2016
    2      |  345     |   0   | 3-12-2016
    3      |  345     |  150  | 3-19-2016
    4      |  345     |   0   | 4-20-2016
   ... more children with different parent IDs
  </code></pre>
  
  <p>and also given that the parents are stored on their own ParentTable with ParentID... Data etc.</p>
  
  <p>My question is how does one partition or update query a similar database so that if the data column has a value 0 then it updates with the last input data. (Say a web scraper failed to pull the data and I want to approximate for analysis). The raw data doesn't have to be updated on the base table, but also could be updated in a query pulling it. I believe there's a way using SQL to make this doable. My attempt was incredibly slow and didn't work for unknown reasons..</p>
  
  <p>I've tried writing some VBA-Access code to loop through a flat file ChildTable + ParentTable and update every 0-value cell in the column Data using an SQL Lookup. If the cell = 0 then go to last available data that is nonzero.
  The problem si that this takes years to run.</p>
  
  <pre><code>Option Compare Database
  
  Sub SoldOut()
  On Error GoTo ProcError
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("MainDataTable") 'This is the flat file version of the data above
  
  'Check to see if the recordset actually contains rows
  If Not (rs.EOF And rs.BOF) Then
      rs.MoveLast
      rs.MoveFirst 'Unnecessary in this case, but still a good habit
      Do Until rs.EOF = True
      GetLastWeek = 0
          If Not rs("Data") &gt; 0 Then
              rs.Edit
              rs("Data") = GetLastWeek(rs('ChildID'), rs('ParentID'), rs('Data'), rs('InputDate'))
              rs.Update
          End If
  
          'Move to the next record. Don't ever forget to do this.
          rs.MoveNext
      Loop
  Else
      MsgBox "There are no records in the recordset."
  End If
  
  ProcExit:
  On Error Resume Next
  rs.Close 'Close the recordset
  Set rs = Nothing 'Clean up
  Exit Sub
  
  ProcError:
      MsgBox Err.Description
      Resume ProcExit
  
  End Sub
  
  Private Function GetLastWeek(ChildID, ParentID As Long, InputDate As Date) As Integer
      'given a record it looks up the weeks before and returns it if it exists
      Dim rst As DAO.Recordset, strSQL As String, rc As Integer ' SQL Code for seeing if last week's data exists.
  
      strSQL = "SELECT * " &amp; _
      "FROM MainDataTable " &amp; _
      "WHERE MainDataTable.[ParentId] = " &amp; ParentID &amp; "AND MainDataTable.[InputDate] &lt;# " &amp; InputDate &amp; "AND NOT MainDataTable.[Data] = 0 
      ORDER BY MainDataTable.[InputDate] DESC;"
  
      Set rst = CurrentDb.OpenRecordset(strSQL): rst.MoveLast: rst.MoveFirst
      rc = rst.RecordCount
      If rc = 0 Then GoTo Cleanup 'if no record, then we are out of luck
      If rc &gt; 0 Then 'If there's some Record
          Do Until rs.EOF = True Or GetLastWeek &gt; 0
              Dim price As Integer: price = rst("Data")
              If price &gt; 0 Then: GetLastWeek = price
              rs.MoveNext
          Loop
      End If
  
  Cleanup:
      rst.Close
      Set rst = Nothing
      If GetLastWeek = 0 Then GetLastWeek = 1 '1 means no data was found
      'Set so the output if nothing is found to 1 so that the code doesn't have to run on the same rows every single week
  End Function
  </code></pre>