Parsing a record into multiple records by date range

[dtreffery]

I'm a green horn when it comes to Access, so please bare with me.

I have a record in a table (Table1) with the following:

Item Start End
A 2012 2030

I have another table (Table2) with the following:

Item Start
A 2018


A 2025

What I would like to do is update/append Table1 so that it looks like the following:

Item Start End
A 2012 2017
A 2018 2024
A 2025 2030

The order of the records in Table2 should be irrelevant in the solution.

I've been really struggling on where to begin with this one . I look forward to any suggestions. Thanks!

Edit: I forgot to mention that there are many Items (A,B,C,D,etc.) in both tables.

[June7]

I expect this will require VBA code that opens and manipulates recordset object and writes records to table.

How many records in Table2 for each Item?

[ranman256]

Your data is really messed up, so , lets clean up tbl1,
make 2 queries using table1
append data from tbl1 into tbl2 using only ITEM, START
then append data in tbl1 into tbl2 using only ITEM, END

then run the code sent to parse out table2 into table3. (never overwrite the original)
change the tables in my code to match yours.

Public Function CollectTime()
Dim sSql As String
Dim vItm, vItm1, vStart, vStart1, vEnd
Dim rst 'As Recordset


sSql = "select * from TABLE2"
Set rst = CurrentDb.OpenRecordset(sSql)
With rst
      vItm = .Fields("Item").Value & ""
      vStart = .Fields("Start").Value & ""
      vEnd = vStart - vStart1 - 1
        
      If vItm = vItm1 Then
            If vStart1 <> "" Then
                  'post emails to a table
              sSql = "Insert into table3 ([Item],[Start],[End]) values ('" & vItm & "','" & vStart & "','" & vEnd & "')"
              DoCmd.RunSQL sSql
            End If
      Else
         vStart1 = ""
      End If
      
      vStart1 = vStart
      vItm1 = vItm
   .MoveNext
End With


rst.Close
Set rst = Nothing
End Function

[dtreffery]

Thanks for the response ranman! I create TABLE2 using the append queries as instructed, but when I execute the function, it doesn't actually do anything (including errors). Do I have to create TABLE3 before the function attempts to Insert records?

[June7]

Yes, establish table 3 first. Surprised there were no error messages.