Populating an inventory value in one record with data from the prior record

[Jack Zasadzinski]

I have an inventory ledger file of sales and inventory information by store that only adds a record if there is activity that day or week. I am trying to use this to do rollups of inventory levels. For example, I may have a record for an item in week 1 in store 1 with sales and inventory, but there was no activity in weeks 2 and 3 so the next record is in week 4. The inventory level did not change since there was no sales activity. If I sum all of the records for all stores by week, the inventory in weeks 2 and 3 would be understated because it did not have a record for that store for those weeks. I have created a table that created records for all of the missing weeks but I don't know an easy way to populate the inventory fields in the missing weeks. My current table looks like this.

Week Store Beginning Inventory Sales Ending Inventory


1 1 100 5 95
2 1
3 1
4 1 95 10 85

The beginning and ending inventories for 2 and 3 are 95 since there was no sales activity. I know how to do this in Excel but the file has 5 million records so it's not practical to move the data. Any help would be appreciated.

[ranman256]

Heres some code you could run to scan the records ,paste code into a module or form.
if it sees a blank, it fills it in.
Sort the records and save the query as : qsInventorySort

Public Sub FillGaps()
Dim rst
Dim vStore, vStart, vEnd
Dim vStorePrev, vStartPrev, vEndPrev
Const kBEGfld = "[Beginning Inventory]"
Const kENDfld = "[Ending Inventory]"
Set rst = CurrentDb.OpenRecordset("qsInventorySort")
With rst
  While Not .EOF
      vStore = .Fields("Store").Value & ""
      vStart = .Fields(kBEGfld).Value & ""
      vEnd = .Fields(kENDfld).Value & ""
     
     If vStore = vStorePrev Then
        If vStart = "" Then
           .Fields(kBEGfld).Value = vStartPrev
           .Fields(kENDfld).Value = vStartPrev
        End If
     End If
     
     vStorePrev = vStore
     vStartPrev = vStart
     vEndPrev = vEnd
     .MoveNext
  Wend
End With
Set rst = Nothing
End Sub