Access 問題 ~~15 points~~

如果某個 section 冇人有A，query 用 Count(), Group By 一定不會有 A = 0 的數目，因為個 Query 怎會知道你想要 A 的數量，而不是 P，或 X, Y, Z 的數量。

一個做到的方法是每一個 grade 加一個 dummy 記錄，你有A-E,Absent 六個 grade 就加六個dummy。

7A | A | A | A | A | A | A
7A | B | B | B | B | B | B
7A | C | C | C | C | C | C
7A | D | D | D | D | D | D
7A | E | E | E | E | E | E
7A | abs | abs | abs | abs | abs | abs

SQL statement:
select class, 'Section A' as section , sectionA as grade, count(sectionA) - 1 as grade_count from table1
group by class, 'Section A', sectionA
union
select class, 'Section B', sectionB, count(sectionB) - 1 from table1
group by class, 'Section B', sectionB
union
select class, 'Section C', sectionC, count(sectionC) - 1 from table1
group by class, 'Section C', sectionC
union
.
.
.
union
select class, 'Overall', overall, count(overall) - 1 from table1
group by class, 'Overall', overall

Result:
class | section | grade | grade_count
7A | Section A | A | 5
7A | Section A | B | 6
7A | Section A | C | 7
7A | Section A | D | 4
7A | Section A | E | 2
7A | Section A | abs | 0
7A | Section B | A | 4
7A | Section B | B | 4
7A | Section B | C | 8
7A | Section B | D | 5
7A | Section B | E | 3
7A | Section B | abs | 0
.
.
.
7A | Overall | A | 5
7A | Overall | B | 6
7A | Overall | C | 7
7A | Overall | D | 4
7A | Overall | E | 2
7A | Overall | abs | 0

2008-07-01 05:59:06 補充：
將已有的數據放入 Excel，每個 section 做一個 sum，不是比 Access 容易多嗎﹖