Microsoft Access 在一個資料表中, e.g.
class | section A | section B | section C | section D | section E | overall
------------------------------------------------------------------------------
7A | A | C | A | A | A | A
7A | B | A | B | B | B | B
7A | D | C | C | C | C | C
7A | abs | abs | abs | abs | abs | abs
7B | D | A | A | C | A | B
7B | B | A | A | A | A | A
有冇辦法分別數到7A,7B,7A&7B的section A,B,C,D,E,overall有多少個A,B,C,D,E,F,U,abs?
如果先用查詢, 再用count() e個function, 就至少需要1*3*6*8=144個查詢...
*3: 7A, 7B, 7A&7B
*6: section A,B,C,D,E,overall
*8: A,B,C,D,E,F,U,abs
有冇最簡單方法做到(i.e.吾駛開好多個查詢,吾用巨集同VB)? THZ.. ^^
Access 問題 ***10 points***
2008-06-08 7:01 pm
回答 (2)
2008-06-10 9:24 am
✔ 最佳答案
唔明點解要144個詢，5個就夠。select class, sectionA,count(sectionA) from table1 group by class, sectionA
result for section A
7A A 5
7A B 3
7A D 2
7A abs 1
7A E 4
7B A 3
... etc.
每個 section 做一次
select class, sectionB,count(sectionB) from table1 group by class, sectionB
select class, sectionC,count(sectionC) from table1 group by class, sectionC
... etc.
2008-06-10 01:31:27 補充：
加埋 7A & 7B 加多5個
select '7A & 7 B' as class,sectionA,count(sectionA) from table1 group by '7A & 7 B',sectionA
select '7A & 7 B' as class,sectionB,count(sectionB) from table1 group by '7A & 7 B',sectionB
select '7A & 7 B' as class,sectionC,count(sectionC) from table1 group by '7A & 7 B',sectionC
... etc.
2008-06-10 01:35:17 補充：
包埋 overall 各加一個
select class, overall,count(overall) from table1 group by class, overall
select '7A & 7 B' as class,overall,count(overall) from table1 group by '7A & 7 B',overall
2008-06-10 01:36:55 補充：
某些 grade 沒有在 result show 出來就是零。
2008-06-08 9:32 pm
你這情況是將field變record, 即橫變直. 我看要用較多的sub query才能一條做到. 但是你的table好像沒有primary key, 用大量的sub query會容易出錯, 要很小心的.
收錄日期: 2021-04-13 17:54:54
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20080608000051KK00654