Sorry, but struggling to come up with your exact requirements and can't really devote any more time to this at present.
Best I can advise is you need a look within a loop for two recordsets representing the two tables - along the lines of
Code:
dim rsP as dao.recordset
dim rsA as dao.recordset
dim issued as long 'how to use as a carry forward?
set rsP=currentdb.openrecordset("SELECT * FROM tblPlanned ORDER BY materialID, PlannedProductionDate") 'include a date range criteria for period?
while not rsp.eof 'outer loop
set rsA =currentdb.openrecordset("SELECT * FROM tblActual WHERE materialID=" & rsP!materialID & " ORDER BY ActualProductionDate") 'include a date range criteria around production date?
while not rsA.EOF 'inner loop
if rsA!actualproductiondate>rsp!plannedproductiondate and rsA!actualproductiondate<=rsp!plannedproductiondate+5 then 'this is after
'if this is the last record in the middle of a recordset - how to determine if the 'after' should be zero if there is no later record after the date range specified? perhaps a dlookup?
elseif rsA!actualproductiondate>rsp!plannedproductiondate-14 and rsA!actualproductiondate<=rsp!plannedproductiondate then 'this is prior
'if this is the first record in the middle of a recordset - how to determine if the prior is a 'leftover' from a planned record that precedes the date range specified
'issue: to get off the starting block the prior value needs to be adjusted for any preceding record whilst the after record needs to be adjusted depending on whether there is any planned record within 14 days
end if
rsA.movenext
wend 'inner loop
rsP.movenext
wend 'outer loop
I've annotated the code, you will need to resolve the questions I've raised.