Access 問題 ~~15 points~~

2008-06-27 5:31 pm
http://hk.knowledge.yahoo.com/question/question?qid=7008060800654
這個問題是上面問題的延續,回答前請先看看上面的網址...

我選用左 garlic2010 的方法,以下還有一些問題不知道該如何處理,
請各位幫幫忙... ^^

1.如何顯示全部A,B,C,D,E,F,U,abs,即使result 是零?
2.將6個查詢再合併成1個查詢 or 將6個查詢再引入同一表單?

除了在yahoo!知識回答這問題外,我還希望大家留下email address,
方便聯絡(因為我部電腦send吾到補充回答)...
My email address: [email protected]

回答 (1)

2008-07-01 1:39 pm
✔ 最佳答案
如果某個 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 容易多嗎﹖


收錄日期: 2021-04-13 17:54:53
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20080627000051KK00460

檢視 Wayback Machine 備份