请问个 在vba中sql联合查询语句的写法

crlcn

比如一段代码如下：
Private Sub 查询_Click()
Dim strSQL As String
Dim chaxun As QueryDef
strSQL = "SELECT 成绩表.学校, 成绩表.班级, Count(成绩表.姓名) AS 人数, Sum(成绩表.数学) AS 数学, Sum(成绩表.语文) AS 语文, Sum(成绩表.英语) AS 英语
FROM 成绩表
GROUP BY 成绩表.学校, 成绩表.班级
UNION All SELECT 成绩表.学校 & "合计" as 学校, "" AS 班级, Count(成绩表.姓名) AS 人数, Sum(成绩表.数学) AS 数学, Sum(成绩表.语文) AS 语文, Sum(成绩表.英语) AS 英语
FROM 成绩表
GROUP BY 成绩表.学校
UNION ALL SELECT "学校总计" AS 学校, "" AS 班级, Count(成绩表.姓名) AS 人数, Sum(成绩表.数学) AS 数学, Sum(成绩表.语文) AS 语文, Sum(成绩表.英语) AS 英语
FROM 成绩表
ORDER BY 学校, 班级;"
Set chaxun = CurrentDb.CreateQueryDef("linshi-jichu", strSQL)
DoCmd.OpenQuery chaxun.Name
-------------------------------------------------------------------------------------------
以上代码 如果去掉声明 只是sql语句 可以运行 但在vba代码编辑模式 不能运行 改成如下可以运行
-------------------------------------------------------------------------------------------
Private Sub 查询_Click()
Dim strSQL As String
Dim chaxun As QueryDef
strSQL = "SELECT 成绩表.学校, 成绩表.班级, Count(成绩表.姓名) AS 人数, Sum(成绩表.数学) AS 数学, Sum(成绩表.语文) AS 语文, Sum(成绩表.英语) AS 英语 FROM 成绩表 GROUP BY 成绩表.学校, 成绩表.班级"
Set chaxun = CurrentDb.CreateQueryDef("linshi-jichu", strSQL)
DoCmd.OpenQuery chaxun.Name
想请问 怎么写那两段union语句 才是正确的vba写法
不胜感激 谢谢！
附能在sql模式运行的语句：
SELECT 成绩表.学校, 成绩表.班级, Count(成绩表.姓名) AS 人数, Sum(成绩表.数学) AS 数学, Sum(成绩表.语文) AS 语文, Sum(成绩表.英语) AS 英语
FROM 成绩表
GROUP BY 成绩表.学校, 成绩表.班级
UNION All SELECT 成绩表.学校 & "合计" as 学校, "" AS 班级, Count(成绩表.姓名) AS 人数, Sum(成绩表.数学) AS 数学, Sum(成绩表.语文) AS 语文, Sum(成绩表.英语) AS 英语
FROM 成绩表
GROUP BY 成绩表.学校
UNION ALL SELECT "学校总计" AS 学校, "" AS 班级, Count(成绩表.姓名) AS 人数, Sum(成绩表.数学) AS 数学, Sum(成绩表.语文) AS 语文, Sum(成绩表.英语) AS 英语
FROM 成绩表
ORDER BY 学校, 班级;

todaynew

双引号要改为单引号：

ssql="SELECT 学校, 班级, Count(姓名) AS 人数, Sum(数学) AS 数学, Sum(语文) AS 语文, Sum(英语) AS 英语 "
ssql=ssql & "FROM 成绩表 GROUP BY 学校, 班级 "
ssql=ssql & "UNION All SELECT 学校 & '合计' as 学校, '' AS 班级, Count(姓名) AS 人数, Sum(数学) AS 数学, Sum(语文) AS 语文, Sum(英语) AS 英语 "
ssql=ssql & "FROM 成绩表 GROUP BY 学校 "
ssql=ssql & "UNION ALL SELECT '学校总计' AS 学校, '' AS 班级, Count(姓名) AS 人数, Sum(数学) AS 数学, Sum(语文) AS 语文, Sum(英语) AS 英语 "
ssql=ssql & "FROM 成绩表 "
ssql=ssql & "ORDER BY 学校, 班级;"

hjb016

正解。

crlcn

哈哈哈 谢谢两位了 是这么回事儿

shitou1ge

学习学习