duplicate table records multiple times according to condition

[major6000]

Hi All.

I have one table like:

ID Type No
-----------------
1 FL 2
2 DL 3


I need the following table to be created using first table , each record should be duplicated according to value in No field:

ID Type No
-----------------
1 FL 2
1 FL 2
2 DL 3
2 DL 3
2 DL 3





Any help will be appreciated

[June7]

Run multiple INSERT sql actions. You can manually run an Access query or use VBA code and recordset to automate.

[recyan]

Just check if below crude function gives some guidelines :

myTable - the original table
myTable_2 - the final table

Public Function CopyRecordsVariableTimes()
    Dim strsqlMakeTable
    Dim strsql
    Dim strsql_1
    Dim strsql_2
    Dim i
    Dim j
    Dim IDCount
    Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
    Dim rsta As DAO.Recordset
    Dim rstb As DAO.Recordset
    Dim TheNoOffa

    'Now the code sets the current database to the one we jsut worked on
    Set db = CurrentDb()

    ' Create the new table
    strsqlMakeTable = "SELECT myTable.ID, myTable.Type, myTable.NoOff INTO myTable_2 FROM myTable WHERE 1 = 2;"
    db.Execute strsqlMakeTable, dbFailOnError

    ' get the count of Records in the Main table
    strsql = "SELECT Count(myTable.ID) AS CountOfID FROM myTable;"
    'Debug.Print strsql
    
    Set rsta = db.OpenRecordset(strsql, dbOpenDynaset)
    
    IDCount = rsta![CountOfID]

    For i = 1 To IDCount
    
        strsql_1 = "SELECT TOP 1 a.ID, a.Type, a.NoOff AS TheNoOff FROM (SELECT TOP " & i & " [ID], [Type], NoOff FROM myTable ORDER BY [ID]) AS a ORDER BY [ID] DESC;"
        'Debug.Print strsql_1
        
        Set rstb = db.OpenRecordset(strsql_1, dbOpenDynaset)
        
        TheNoOffa = rstb![TheNoOff]
        'Debug.Print TheNoOffa

        For j = 1 To TheNoOffa
        
            strsql_2 = "INSERT INTO myTable_2 ( ID, Type, NoOff ) " & strsql_1
            db.Execute strsql_2, dbFailOnError
            'Debug.Print strsql_2
            
        Next j
        
    Next i

MsgBox "Completed successfully."

End Function

Thanks