标题: 请问个 在vba中sql联合查询语句的写法 [打印本页] 作者: crlcn 时间: 2010-10-1 13:07 标题: 请问个 在vba中sql联合查询语句的写法 比如一段代码如下：
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 时间: 2010-10-1 13:30
双引号要改为单引号：
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 学校, 班级;"
