Copying (multiple) records

[dlugirapfr]

Hi everyone,

I have two tables: tbl1 and tbl2. In tbl1 I have filed "howmuch" - it is number. Is is possible to copy records from tbl1 to tbl2 times (3,4 and more) like number in tbl1 ?

Thanks for replays.

 

[CJ_London]

yes!

but need to know more to provide a solution - can you show an example with data in table 1 and what you want to see in table 2

 

[dlugirapfr]

For example tbl1
name, surname, brand, how much
John, X, Mac, 3
Anna, Z, Opel, 1

And how to make macro in vba that i will receive in tbl2

name, surname, brand
John, X, Mac
John, X, Mac
John, X, Mac
Anna, Z, Opel

 

[dlugirapfr]

Is it possible that you will answer?

Thanks a lot I have big problem with it and i don't know how to resolve it

 

[CJ_London]

Sorry, been busy

Still not clear. Are you saying that table 1 has 2 rows and you want to copy the first row 3 times (the how much value) into table 2 and the second row 1 times into table2?

 

[dlugirapfr]

Exactly

 

[CJ_London]

I don't beleive it can be done purely in sql but this will do it in VBA (I don't use macros)

Table 2 needs to exist and I have used the same names as you provided (but modified name as it is a reserved word)

Dim Mydb as database
Dim rs as recordset
Dim i as integer
 
 
set mydb=currentdb
set rs = mydb.openrecordset("SELECT * from Table1")
while not rs.eof
    for i=1 to rs.fields("Howmuch")
        mydb.execute("INSERT INTO Table2 ( TName, Surname, Brand ) SELECT '" & rs.fields("TName") & "', '" & rs.fields("Surname") & "', '" & rs.fields("Brand") & "'"
    next i
    rs.movenext
wend
 
set rs=nothing
set mydb=nothing

 

[dlugirapfr]

Dear CJ_London.

Smth. is wrong with code.

Let me explain:

tbl1
name; surname; brand; howmuch;
karol sss opel 3
anna ddd nokia 5

tbl2
name2 surname2 brand2

When I try tu run macro from form i receive syntax error

Dim Mydb As Database
Dim rs As Recordset
Dim i As Integer
 
 
Set Mydb = CurrentDb
Set rs = Mydb.OpenRecordset("SELECT * from tbl1")
While Not rs.EOF
    For i = 1 To rs.Fields("howmuch")
        mydb.execute("INSERT INTO tbl2 ( name2, surname2, brand2 ) SELECT rs.fields("name2"), rs.fields("surname2"), rs.fields("brand2")
    Next i
    rs.MoveNext
Wend
 
Set rs = Nothing
Set Mydb = Nothing

 

[CJ_London]

you need to rename tbl1 field names by adding a 2 to the end

tbl1
name; surname; brand; howmuch;

SELECT rs.fields("name2"), rs.fields("surname2"), rs.fields("brand2")

 

[dlugirapfr]

Thanks for you answers but still there is a problem.

Dim Mydb As Database
Dim rs As Recordset
Dim i As Integer
 
 
Set Mydb = CurrentDb
Set rs = Mydb.OpenRecordset("SELECT * from tbl1")

While Not rs.EOF
    For i = 1 To rs.Fields(howmuch)
        
        Mydb.Execute "INSERT INTO tbl2 (name2, surname2, brand2) SELECT rs.Fields(name), rs.Fields(surname), rs.Fields(brand)"
    Next i
    rs.MoveNext
Wend

Set rs = Nothing
Set Mydb = Nothing

I remove "(" before Insert, still is problem that rs.Fields is not defined.

 

[CJ_London]

now you have removed the quotation marks!

SELECT rs.fields("name2"), rs.fields("surname2"), rs.fields("brand2")

Also here

For i = 1 To rs.Fields("howmuch")

 

[dlugirapfr]

But if i remove " the i receive error:

Compile error:
Expected: end of statement

 

[eblieb]

Explained by next post below

 

[CJ_London]

Lets go back to my original code, you have not copied it correctly - just correct it for the field names you are using in tbl1 and tbl2:

Dim Mydb as database
Dim rs as recordset
Dim i as integer
 
 
set mydb=currentdb
set rs = mydb.openrecordset("SELECT * from Table1")
while not rs.eof
    for i=1 to rs.fields("Howmuch")
        mydb.execute("INSERT INTO Table2 ( TName, Surname, Brand ) SELECT '" & rs.fields("TName") & "', '" & rs.fields("Surname") & "', '" & rs.fields("Brand") & "'"
    next i
    rs.movenext
wend
 
set rs=nothing
set mydb=nothing

 

[dlugirapfr]

Dear CJ_London,

Tanks for your time and any replies. Everything is working now. Greetings.

 

[dlugirapfr]

Dear CJ_London,

I adapted this code to my orginal project

Set Mydb = CurrentDb
' WHERE tblCalcTmp.taknie=yes")
Set rs = Mydb.OpenRecordset("SELECT * from tblCalcTmp")
While Not rs.EOF
    For i = 1 To rs.fields("krotnosc")
        Mydb.Execute ("INSERT INTO tblCalcTmp2 ( nr_umowy2, nr_klienta2 ) SELECT '" & rs.fields("nr_umowy") & "', '" & rs.fields("nr_kleinta") & "'")
    Next i
    rs.MoveNext
Wend

Set rs = Nothing
Set Mydb = Nothing
End Sub

End when I want to run this code from form and I receive error type mismatch: Set rs = Mydb.OpenRecordset("SELECT * from tblCalcTmp"). My name of table is good. tbl1 = tblCalcTmp tbl2=tblCalcTmp1. Please help and thank you.