如何在 Access 2000中, 做文字的計數
我有下列資料
ID orientation Managers evaluation
--------------------------------------------------------
2C NC C
3NC C C
4NA C C
5C C C
6C NC C
7C NC C
8C NC NC
------------------------------------------------------
***如何針對 orientation Managers evaluation 中回答 C NC NA做計數
我用過SQL 中語法
SELECT Interview.[orientation], Count(Interview.[Managers]) AS [Managers之筆數], Count(Interview.[evaluation]) AS [evaluation之筆數], Count(Interview.[orientation]) AS [orientation之筆數]
FROM Interview
GROUP BY Interview.[ orientation];
但出來的計數均為orientation那欄的計數 其他欄的計數雖然有出來 但結果不對
orientation Managers之筆數evaluation之筆數 orientation之筆數
-------------------------------------------------------------------------------------
C 5 5 5
NC 1 1 1
NA 1 1 1
--------------------------------------------------------------------------------------
正確結果應為
-------------------------------------------------------------------------------------
C 5 3 6
NC 1 4 1
NA 1 0 0
--------------------------------------------------------------------------------------
誰能跟我說要如何寫SQL或在查詢檢視中 如何設定 才可以有正確結果
Update:
Comments
版主好:
SELECT Interview.orientation , T1.orientation之筆數, T2.Managers之筆數, T3.evaluation之筆數
FROM (( Interview LEFT JOIN [SELECT Interview.orientation, Count(Interview.orientation) AS orientation之筆數 FROM Interview
GROUP BY Interview.orientation]. AS T1 ON Interview.orientation = T1.orientation) LEFT JOIN [SELECT Interview.Managers, Count(Interview.Managers) AS Managers之筆數 FROM Interview
GROUP BY Interview.Managers]. AS T2 ON Interview.orientation = T2.Managers) LEFT JOIN [SELECT Interview.evaluation, Count(Interview.evaluation) AS evaluation之筆數 FROM Interview
GROUP BY Interview.evaluation]. AS T3 ON Interview.orientation = T3.evaluation GROUP BY Interview.orientation, T1.orientation之筆數, T2.Managers之筆數, T3.evaluation之筆數;
希望有幫上你的忙!
試試看這樣
SELECT Interview.orientation, DCount("orientation","Interview"," orientation='" & [orientation] & "'") AS orientation之筆數, DCount("Managers","Interview"," Managers='" & [orientation] & "'") AS Managers之筆數, DCount("evaluation","Interview"," evaluation='" & [orientation] & "'") AS evaluation之筆數
FROM Interview
GROUP BY Interview.orientation, DCount("orientation","Interview"," orientation='" & [orientation] & "'"), DCount("Managers","Interview"," Managers='" & [orientation] & "'"), DCount("evaluation","Interview"," evaluation='" & [orientation] & "'");
2009-01-17 11:17:59 補充：
提供E-mail帳號,我寄範例給你~!!!