Create/Insert

[TJBarr]

When I try to execute this code I get an error in my insert statement.
I would prefer a create table anyway but not sure how to do this.
This is code behind a form in which a user has to pick first a geographical area and second any number of quarters. e.g. 94-1,94-2,96-4.........






Option Compare Database

Private Sub Command12_Click()
Dim db As Database
Dim rs As Recordset
Dim strSQL, stremp, strmain, strINSERT As String
Dim i As Integer
Dim arrsic(3) As String
Dim group_ch As Integer
Dim quarter_ch As String
Dim strItems As String
Dim intCurrentRow As Integer


quarter_ch = quarter_list.Column(1)
group_ch = geo_list.Column(0)
icntqtr = quarter_list.ColumnCount

Set db = CurrentDb

' Need to delete entries in tbl_emp_report
stremp = ""


For intCurrentRow = 0 To quarter_list.ListCount - 1
If quarter_list.Selected(intCurrentRow) Then
strItems = strItems & "'" & quarter_list.Column(1, _
intCurrentRow) & "',"
End If

Next intCurrentRow

i = Len(strItems)
strItems = Mid(strItems, 1, i - 1)
MsgBox ("quarters " & strItems)

strSQL = "Select * from tbl_Quarter where quarter in (" & strItems & ");"
Debug.Print (strSQL)
Set rs = db.OpenRecordset(strSQL)

Do Until rs.EOF
stremp = stremp & "Sum(tbl_Employment.[" & rs!quarter & "]) as [" & rs!quarter & "],"
strINSERT = strINSERT & "'[" & rs!quarter & "]',"
rs.MoveNext
Loop


i = Len(stremp)
stremp = Mid(stremp, 1, i - 1)
i = Len(strINSERT)
strINSERT = Mid(strINSERT, 1, i - 1)

arrsic(1) = "mid([sic],1,2)"
arrsic(2) = "mid([sic],1,3)"
arrsic(3) = "sic"

ilen = Len(strSQL)
strSQL = Mid(strSQL, 1, ilen - 1) & ";"
Set rs = db.OpenRecordset(strSQL)
Debug.Print (strSQL)

arrsic(1) = "mid([sic],1,2)"
arrsic(2) = "mid([sic],1,3)"
arrsic(3) = "sic"
' Add loop for counties and to add or remove zips as needed

For i = 0 To 3 'Loop through SIC array

strmain = "INSERT INTO tbl_emp_report ( sicnew, " & strINSERT & ") " & _
"SELECT " & arrsic(i) & " as sicnew," & _
stremp & _
" FROM module_groups INNER JOIN 40_emp ON module_groups.cnty_id = [40_emp].CountyID " & _
"WHERE module_groups.group_no = ( " & group_ch & " )" & _
" GROUP BY " & arrsic(i) & ";"


db.Execute (strmain)

Next i

Debug.Print (strmain)

End Sub

[andreys]

strmain = "INSERT INTO tbl_emp_report ( sicnew, " & strINSERT & ") VALUES (" & _
"SELECT " & arrsic(i) & " as sicnew," & _
stremp & _
" FROM module_groups INNER JOIN 40_emp ON module_groups.cnty_id = [40_emp].CountyID " & _
"WHERE module_groups.group_no = ( " & group_ch & " )" & _
" GROUP BY " & arrsic(i) & ");"

[TJBarr]

Thanks but now it doesn't like the next line

[TJBarr]

Thanks I fiqured it out. Now I am trying to create the table. I get a field definition error.


For i = 0 To 3 'Loop through SIC array

strCreate = "CREATE TABLE tbl_emp_report (sicnew text(10), ((" & strINSERT & ")text(10)))"
strmain = "INSERT INTO tbl_emp_report ( sicnew, " & strINSERT & ")" & _
"SELECT " & arrsic(i) & " as sicnew," & _
stremp & _
" FROM module_groups INNER JOIN (tbl_sic_new INNER JOIN tbl_employment ON tbl_sic_new.value = tbl_employment.SIC) ON module_groups.cnty_id = tbl_employment.CountyID" & _
" WHERE module_groups.group_no = ( " & group_ch & ")" & _
" GROUP BY " & arrsic(i) & ";"
Debug.Print (strmain)
db.Execute (strCreate)
db.Execute (strmain)


Next i

End Sub