SELECT INTO仅插入最后一行- VBA、Access 2000？

Question

我有一个在循环中创建的SQL字符串：

"SELECT '" & Trim(Str(j)) & "' AS cpa, Count(Val('" & rsCPANezbirni("tipprod") & "')) AS BrojProd, Sum(Val('" & rsCPANezbirni("povrsina") & "')) AS p, Sum(Val('" & rsCPANezbirni("pmagacin") & "')) AS pm INTO T14_KPD_CPA_samo_podatoci FROM CPA_nezbirni WHERE (t4k" & Trim(Str(j)) & "<>'' Or t4k" & Trim(Str(j)) & " Is Not Null);"

我使用的循环是：

Dim j As Integer
j = 1
Do While j <= 3
    cpaSelectSQL = "SELECT '" & Trim(Str(j)) & "' AS cpa, Count(Val('" & rsCPANezbirni("tipprod") & "')) AS BrojProd, Sum(Val('" & rsCPANezbirni("povrsina") & "')) AS p, Sum(Val('" & rsCPANezbirni("pmagacin") & "')) AS pm INTO T14_KPD_CPA_samo_podatoci FROM CPA_nezbirni WHERE (t4k" & Trim(Str(j)) & "<>'' Or t4k" & Trim(Str(j)) & " Is Not Null);"
    Debug.Print "j = " & Str(j) & ", cpa select SQL: " & cpaSelectSQL
    DoCmd.RunSQL cpaSelectSQL, True

    On Error GoTo ErrorHandler
    j = j + 1
Loop

我的问题是，只有最后生成的行被复制到T14_KPD_CPA_samo_podatoci中，即cpa =3

我想复制每个for作为cpa 1到3的值。

我做错了什么？

Answer 1

SELECT INTO是一个"Create table“查询，每次运行它都会覆盖目标表。

一旦表存在，您就需要INSERT INTO。

Answer 2

好的，我使用了这种SQL查询

INSERT INTO table1 (col1, col2, ..., coln) SELECT col1, col2,..., coln FROM table2 WHERE condition GROUP BY colx;