Loop through tables

[Zygor]

I am trying to loop through about 100 tables. Each named tblCopy1 ,tblCopy2 and so on.

I have an append query that hits each of these tables one at a time and appends data to a different table. How can I use the 1 query and pass into it the name of the table.

example
INSERT INTO tbl2Column ( [CPT4 Code], ESI ) SELECT tblCopy1.[CPT4 code], tblCopy1.[Procedure # 1] FROM tblCopy1 WHERE (((tblCopy1.[Procedure # 1]) Is Not Null));

I need to change tblCopy1 100 times.

 

[Remou]

Use VBA.

For i=1 to 100
strSQL="INSERT INTO tbl2Column ( [CPT4 Code], ESI ) SELECT tblCopy" & i _
& ".[CPT4 code], tblCopy" & i _
& ".[Procedure # 1] FROM tblCopy" & i _
& " WHERE tblCopy" & i & ".[Procedure # 1] Is Not Null"
CurrentDB.Execute strSQL
Next

 

[lameid]

Sub Example
	Dim i as integer
	dim strSQL as string
	Docmd.setwarnings False 'Turn Warnings off so you do not get prompted for each action query execution
	For i = 1 To 100
		strSQL = "INSERT INTO tbl2Column ( [CPT4 Code], ESI ) SELECT tblCopy" & i & ".[CPT4 code], " & _
			"tblCopy" & i & ".[Procedure # 1] " & _
			"FROM tblCopy" & i & _
			" WHERE (((tblCopy" & i & ".[Procedure # 1]) Is Not Null))"

		Docmd.runsql strSQL
	Next i
	Docmd.setwarnings True 'turn warnings back on
End sub

Now to see if anyone cross posted...

 

[lameid]

Remou and I are about the same... except I thougt my code would not wrap, <SIGH>.

Sub Example
    Dim i as integer
    dim strSQL as string
    Docmd.setwarnings False 'Turn Warnings off so you do
                            'not get prompted for each
                            'action query execution
    For i = 1 To 100
        strSQL = "INSERT INTO tbl2Column ( [CPT4 Code], ESI ) " & _ 
            "SELECT tblCopy" & i & ".[CPT4 code], " & _
            "tblCopy" & i & ".[Procedure # 1] " & _
            "FROM tblCopy" & i & _
            " WHERE (((tblCopy" & i & ".[Procedure # 1]) Is Not Null))"

        Docmd.runsql strSQL
    Next i
    Docmd.setwarnings True 'turn warnings back on
End sub

There much better... My code isn't tested either

 

[Zygor]

Worked awesome! Thanks!