請(qǐng)問(wèn)個(gè) 在vba中sql聯(lián)合查詢(xún)語(yǔ)句的寫(xiě)法

crlcn

比如一段代碼如下：
Private Sub 查詢(xún)_Click()
Dim strSQL As String
Dim chaxun As QueryDef
strSQL = "SELECT 成績(jī)表.學(xué)校, 成績(jī)表.班級(jí), Count(成績(jī)表.姓名) AS 人數(shù), Sum(成績(jī)表.數(shù)學(xué)) AS 數(shù)學(xué), Sum(成績(jī)表.語(yǔ)文) AS 語(yǔ)文, Sum(成績(jī)表.英語(yǔ)) AS 英語(yǔ)
FROM 成績(jī)表
GROUP BY 成績(jī)表.學(xué)校, 成績(jī)表.班級(jí)
UNION All SELECT 成績(jī)表.學(xué)校 & "合計(jì)" as 學(xué)校, "" AS 班級(jí), Count(成績(jī)表.姓名) AS 人數(shù), Sum(成績(jī)表.數(shù)學(xué)) AS 數(shù)學(xué), Sum(成績(jī)表.語(yǔ)文) AS 語(yǔ)文, Sum(成績(jī)表.英語(yǔ)) AS 英語(yǔ)
FROM 成績(jī)表
GROUP BY 成績(jī)表.學(xué)校
UNION ALL SELECT "學(xué)�？傆�(jì)" AS 學(xué)校, "" AS 班級(jí), Count(成績(jī)表.姓名) AS 人數(shù), Sum(成績(jī)表.數(shù)學(xué)) AS 數(shù)學(xué), Sum(成績(jī)表.語(yǔ)文) AS 語(yǔ)文, Sum(成績(jī)表.英語(yǔ)) AS 英語(yǔ)
FROM 成績(jī)表
ORDER BY 學(xué)校, 班級(jí);"
Set chaxun = CurrentDb.CreateQueryDef("linshi-jichu", strSQL)
DoCmd.OpenQuery chaxun.Name
-------------------------------------------------------------------------------------------
以上代碼 如果去掉聲明 只是sql語(yǔ)句 可以運(yùn)行 但在vba代碼編輯模式 不能運(yùn)行 改成如下可以運(yùn)行
-------------------------------------------------------------------------------------------
Private Sub 查詢(xún)_Click()
Dim strSQL As String
Dim chaxun As QueryDef
strSQL = "SELECT 成績(jī)表.學(xué)校, 成績(jī)表.班級(jí), Count(成績(jī)表.姓名) AS 人數(shù), Sum(成績(jī)表.數(shù)學(xué)) AS 數(shù)學(xué), Sum(成績(jī)表.語(yǔ)文) AS 語(yǔ)文, Sum(成績(jī)表.英語(yǔ)) AS 英語(yǔ) FROM 成績(jī)表 GROUP BY 成績(jī)表.學(xué)校, 成績(jī)表.班級(jí)"
Set chaxun = CurrentDb.CreateQueryDef("linshi-jichu", strSQL)
DoCmd.OpenQuery chaxun.Name
想請(qǐng)問(wèn) 怎么寫(xiě)那兩段union語(yǔ)句 才是正確的vba寫(xiě)法
不勝感激 謝謝！
附能在sql模式運(yùn)行的語(yǔ)句：
SELECT 成績(jī)表.學(xué)校, 成績(jī)表.班級(jí), Count(成績(jī)表.姓名) AS 人數(shù), Sum(成績(jī)表.數(shù)學(xué)) AS 數(shù)學(xué), Sum(成績(jī)表.語(yǔ)文) AS 語(yǔ)文, Sum(成績(jī)表.英語(yǔ)) AS 英語(yǔ)
FROM 成績(jī)表
GROUP BY 成績(jī)表.學(xué)校, 成績(jī)表.班級(jí)
UNION All SELECT 成績(jī)表.學(xué)校 & "合計(jì)" as 學(xué)校, "" AS 班級(jí), Count(成績(jī)表.姓名) AS 人數(shù), Sum(成績(jī)表.數(shù)學(xué)) AS 數(shù)學(xué), Sum(成績(jī)表.語(yǔ)文) AS 語(yǔ)文, Sum(成績(jī)表.英語(yǔ)) AS 英語(yǔ)
FROM 成績(jī)表
GROUP BY 成績(jī)表.學(xué)校
UNION ALL SELECT "學(xué)�？傆�(jì)" AS 學(xué)校, "" AS 班級(jí), Count(成績(jī)表.姓名) AS 人數(shù), Sum(成績(jī)表.數(shù)學(xué)) AS 數(shù)學(xué), Sum(成績(jī)表.語(yǔ)文) AS 語(yǔ)文, Sum(成績(jī)表.英語(yǔ)) AS 英語(yǔ)
FROM 成績(jī)表
ORDER BY 學(xué)校, 班級(jí);

todaynew

雙引號(hào)要改為單引號(hào)：

ssql="SELECT 學(xué)校, 班級(jí), Count(姓名) AS 人數(shù), Sum(數(shù)學(xué)) AS 數(shù)學(xué), Sum(語(yǔ)文) AS 語(yǔ)文, Sum(英語(yǔ)) AS 英語(yǔ) "
ssql=ssql & "FROM 成績(jī)表 GROUP BY 學(xué)校, 班級(jí) "
ssql=ssql & "UNION All SELECT 學(xué)校 & '合計(jì)' as 學(xué)校, '' AS 班級(jí), Count(姓名) AS 人數(shù), Sum(數(shù)學(xué)) AS 數(shù)學(xué), Sum(語(yǔ)文) AS 語(yǔ)文, Sum(英語(yǔ)) AS 英語(yǔ) "
ssql=ssql & "FROM 成績(jī)表 GROUP BY 學(xué)校 "
ssql=ssql & "UNION ALL SELECT '學(xué)校總計(jì)' AS 學(xué)校, '' AS 班級(jí), Count(姓名) AS 人數(shù), Sum(數(shù)學(xué)) AS 數(shù)學(xué), Sum(語(yǔ)文) AS 語(yǔ)文, Sum(英語(yǔ)) AS 英語(yǔ) "
ssql=ssql & "FROM 成績(jī)表 "
ssql=ssql & "ORDER BY 學(xué)校, 班級(jí);"

hjb016

正解。

crlcn

哈哈哈 謝謝兩位了 是這么回事兒

shitou1ge

學(xué)習(xí)學(xué)習(xí)