Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2017
    Posts
    2

    Unanswered: how to insert N rows into a table, and N is determined by another table


    Hi there,

    I have a tough question to consult. I have a table-A, with following information
    Name, Awards
    Tom,3
    Mike,4

    I want to insert rows into another table-B, like this
    Name, AwardsSequence
    Tom,1
    Tom,2
    Tom,3
    Mike,1
    Mike,2
    Mike,3
    Mike,4

    I hope 1) once Table-A changes or updates, Table-B changes acoordingly automatically. 2) less VBA, better, if not, that is okay to use VBA

    thanks in advance!

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    628
    Provided Answers: 32
    alter the table names to yours, then run AddNrecs (either from button click or in VBE)

    Code:
    Public Sub AddNrecs()
    Dim rst
    Dim sSql As String
    Dim iNum As Integer, i As Integer
    Dim vName
    
    DoCmd.SetWarnings False
    Set rst = CurrentDb.OpenRecordset("tTable1")
    With rst
      While Not .EOF
          vName = .Fields("name").Value & ""
          iNum = .Fields("Awards").Value
          
           For i = 1 To iNum
              sSql = "Insert into table2 (NAME ,AwardsSequence) VALUES ('" & vName & "'," & i & ")"
              DoCmd.RunSQL sSql
           Next
           
         .MoveNext   'next name/award
      Wend
    End With
    DoCmd.SetWarnings True
    MsgBox "Done"
    
    Set rst = Nothing
    End Sub

  3. #3
    Join Date
    Nov 2017
    Posts
    2
    Quote Originally Posted by ranman256 View Post
    alter the table names to yours, then run AddNrecs (either from button click or in VBE)

    Code:
    Public Sub AddNrecs()
    Dim rst
    Dim sSql As String
    Dim iNum As Integer, i As Integer
    Dim vName
    
    DoCmd.SetWarnings False
    Set rst = CurrentDb.OpenRecordset("tTable1")
    With rst
      While Not .EOF
          vName = .Fields("name").Value & ""
          iNum = .Fields("Awards").Value
          
           For i = 1 To iNum
              sSql = "Insert into table2 (NAME ,AwardsSequence) VALUES ('" & vName & "'," & i & ")"
              DoCmd.RunSQL sSql
           Next
           
         .MoveNext   'next name/award
      Wend
    End With
    DoCmd.SetWarnings True
    MsgBox "Done"
    
    Set rst = Nothing
    End Sub
    Hi Ranman!

    Thank you for your reply,

    It works on my pc! Much appreciated!

    May I ask what VBE is? At this moment can I set it to run automatically when main table is updated?

  4. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    VBE = Visual Basic Editor
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •