Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Apr 29th, 2012  |  syntax: None  |  size: 2.27 KB  |  hits: 16  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Nested Error in multiple union statement in MS Access 2007
  2. select 'John' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English
  3.     From School_TBL
  4.     where (School_TBL.StudentID.value = 2 and School_TBL.CourseID.Value=1)
  5.     GROUP BY School_TBL.StudentID.Value
  6.  
  7.     UNION ALL
  8.     select 'Jane' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English
  9.     From School_TBL
  10.     where (School_TBL.StudentID.value = 3 and School_TBL.CourseID.Value=1)
  11.     GROUP BY School_TBL.StudentID.Value
  12.  
  13.     UNION ALL
  14.     select 'Jim' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English
  15.     From School_TBL
  16.     where (School_TBL.StudentID.value = 4 and School_TBL.CourseID.Value=1)
  17.     GROUP BY School_TBL.StudentID.Value  
  18.  
  19. UNION ALL
  20.     select 'Hank' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English
  21.     From School_TBL
  22.     where (School_TBL.StudentID.value = 5 and School_TBL.CourseID.Value=1)
  23.     GROUP BY School_TBL.StudentID.Value
  24.  
  25.     UNION ALL
  26.     select 'Jay' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English
  27.     From School_TBL
  28.     where (School_TBL.StudentID.value = 6 and School_TBL.CourseID.Value=1)
  29.     GROUP BY School_TBL.StudentID.Value
  30.  
  31.     UNION ALL
  32.     select 'Anna' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English
  33.     From School_TBL
  34.     where (School_TBL.StudentID.value = 7 and School_TBL.CourseID.Value=1)
  35.     GROUP BY School_TBL.StudentID.Value
  36.  
  37.     UNION ALL
  38.     select 'Amy' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English
  39.     From School_TBL
  40.     where (School_TBL.StudentID.value = 8 and School_TBL.CourseID.Value=1)
  41.     GROUP BY School_TBL.StudentID.Value
  42.  
  43.     UNION ALL
  44.     select 'Jason' as Student Name, Sum(IIf([CourseID]=1,1,0)) as Math, Sum(IIf([CourseID]=2,1,0)) AS English
  45.     From School_TBL
  46.     where (School_TBL.StudentID.value = 9 and School_TBL.CourseID.Value=1)
  47.     GROUP BY School_TBL.StudentID.Value
  48.        
  49. SELECT Student.studentName, Sum(IIf([CourseID]=1,1,0)) AS math, Sum(IIf([CourseID]=2,1,0)) AS english
  50. FROM school_tbl INNER JOIN Student ON school_tbl.studentId = Student.Studentid
  51. GROUP BY Student.studentName;
create a new version of this paste RAW Paste Data