MS Access 2007中多个union语句中的嵌套错误

问题描述：

我收到一个错误 - '无法启动事务;太多的交易已经嵌套了。“当我使用7个联合语句进行查询时。如果我删除一个联合声明并使其成为6，那么一切都运行完美。解决此问题的任何帮助都将非常有价值。MS Access 2007中多个union语句中的嵌套错误

谢谢！

School_TBL由课程柱，和学生柱：

我使用的表格。一个学生可以采取多场（所以它是一个多值字段）

这里是SQL语句：

select 'John' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English 
    From School_TBL 
    where (School_TBL.StudentID.value = 2 and School_TBL.CourseID.Value=1) 
    GROUP BY School_TBL.StudentID.Value 

    UNION ALL 
    select 'Jane' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English 
    From School_TBL 
    where (School_TBL.StudentID.value = 3 and School_TBL.CourseID.Value=1) 
    GROUP BY School_TBL.StudentID.Value 

    UNION ALL 
    select 'Jim' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English 
    From School_TBL 
    where (School_TBL.StudentID.value = 4 and School_TBL.CourseID.Value=1) 
    GROUP BY School_TBL.StudentID.Value 

UNION ALL 
    select 'Hank' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English 
    From School_TBL 
    where (School_TBL.StudentID.value = 5 and School_TBL.CourseID.Value=1) 
    GROUP BY School_TBL.StudentID.Value 

    UNION ALL 
    select 'Jay' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English 
    From School_TBL 
    where (School_TBL.StudentID.value = 6 and School_TBL.CourseID.Value=1) 
    GROUP BY School_TBL.StudentID.Value 

    UNION ALL 
    select 'Anna' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English 
    From School_TBL 
    where (School_TBL.StudentID.value = 7 and School_TBL.CourseID.Value=1) 
    GROUP BY School_TBL.StudentID.Value 

    UNION ALL 
    select 'Amy' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English 
    From School_TBL 
    where (School_TBL.StudentID.value = 8 and School_TBL.CourseID.Value=1) 
    GROUP BY School_TBL.StudentID.Value 

    UNION ALL 
    select 'Jason' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English 
    From School_TBL 
    where (School_TBL.StudentID.value = 9 and School_TBL.CourseID.Value=1) 
    GROUP BY School_TBL.StudentID.Value

看起来您有设计问题。最好从学校表格发布一些样本数据并提供关于期望结果的说明。 – Fionnuala 2012-02-13 20:53:06

答

你需要持有学生细节的第二个表，如果您还没有有一个。您只需要一条选择语句，在该表上进行加入以提取学生姓名

SELECT Student.studentName, Sum(IIf([CourseID]=1,1,0)) AS math, Sum(IIf([CourseID]=2,1,0)) AS english 
FROM school_tbl INNER JOIN Student ON school_tbl.studentId = Student.Studentid 
GROUP BY Student.studentName;

MS Access 2007中多个union语句中的嵌套错误

相关推荐