'How to generate a range of numbers between two numbers then duplicate entries from that range?

I would love some help with a query to expand an Access Table I'm working with. I need to generate a range between the min and max numbers and then duplicate the Code for that range.

Code |Number-min|Number-max
----------------------------
1342 |0109724 | 0109728
20040|0116764 | 0116766

etc

This is what I am hoping to achieve:

Code| NumberALL
----------------
1342 | 0109724  
1342 | 0109725
1342 | 0109726
1342 | 0109727  
1342 | 0109728
20040 | 0116764
20040 | 0116765
20040 | 0116766

Any help is greatly appreciated!! Thank you!

sqlms-access


Solution 1:[1]

Let's assume you have a table tblInp with the fields Code, MinNr (dataype long), MaxNr (datatype long) and a tblOut with the fields Code, allNr (datatype long) then the following code will create the data in tblOut as described in the post. It just loops through all records in tblInp and with a for loop the mutliple recorde in tblOut are created.

Option Compare Database
Option Explicit

Sub createData()
    Dim db As Database
    Set db = CurrentDb    

    Dim rs As Recordset, rsOut As Recordset
    Set rs = db.OpenRecordset("tblInp")    
    Set rsOut = db.OpenRecordset("tblOut")

    Dim i As Long

    Do While Not rs.EOF
        With rsOut
            For i = rs.Fields("MinNr") To rs.Fields("MaxNr")
                .AddNew
                .Fields("Code").Value = rs.Fields("Code").Value
                .Fields("allNr").Value = i
                .Update
            Next i
            rs.MoveNext
        End With
    Loop

    rs.Close
    rsOut.Close
End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Storax

Related Questions