Access 問題???

Question

想請問一下‧‧‧http://www.chinesefreewebs.com/jeff0506/test.mdb
如果要做一個查詢，統計a1、b1、c1、d1在資料庫中各出現過幾次，該怎麼用呢?

因為Data裡的資料(a1 b1 c1 d1...)是不固定的，也許還會有e1 f1...很多，所以有其它方式可以做嗎?

Answer 1

我的方法是使用SQL建立一個動態「查詢」
建立一個新查詢
然後用"SQL檢視"將下面內容填入
再將該查詢儲存
-------------------------------------------------------
SELECT S As Sort, Sum(C) AS Total
FROM (SELECT Data1 AS S, Count(Data1) AS [C] FROM Data GROUP BY Data1
Union SELECT Data2 AS S, Count(Data2) AS [C] FROM Data GROUP BY Data2
Union SELECT Data3 AS S, Count(Data3) AS [C] FROM Data GROUP BY Data3)
Where S <> "" GROUP BY S;
-------------------------------------------------------

你的原始範例檔我放在
http://specterglobe.myweb.hinet.net/test.mdb
開啟後,點選「查詢1」即為你要的解答...
雖然你再次使用SQL檢視時,內容可能已經和上面不一樣了
不過答案是一樣的

2006-12-21 08:26:14 補充：
SELECT S As Sort, Sum(C) AS Total
FROM (SELECT 1 AS ID, Data1 AS S, Count(Data1) AS [C] FROM Data GROUP BY Data1
Union SELECT 2 AS ID, Data2 AS S, Count(Data2) AS [C] FROM Data GROUP BY Data2
Union SELECT 3 AS ID, Data3 AS S, Count(Data3) AS [C] FROM Data GROUP BY Data3)
Where S <> "" GROUP BY S

2006-12-21 08:38:41 補充：
呀!我是bug大王 XD
謝謝W.J.S大大的提醒.....已更正如上.....
其實我昨天有用這個版本比對過原始資料,後來覺得ID欄可以省略..
於是就把它刪掉,結果刪除後的結果就不正確了...
更正的測試檔我放在...
http://specterglobe.brinkster.net/test.rar

Answer 2

'應該有SOL的語法吧,不過我寫不出來
圖片參考：http://tw.yimg.com/i/tw/blog/smiley/8.gif

Private Sub Command0_Click()
Dim Re As DAO.Recordset, F As DAO.Field, S$, K$, M$

Set Re = CurrentDb.OpenRecordset("Data")
If Re.RecordCount Then
Re.MoveFirst: S = Chr(0)
Do Until Re.EOF
For Each F In Re.Fields
If Not IsNull(F) And F <> "" Then S = S & F & Chr(0)
Next
Re.MoveNext
Loop
Do Until S = Chr(0)
K = Mid$(S, 2, InStr(2, S, Chr(0)) - 2)
M = M & K & " = " & UBound(Split(S, K)) & vbCrLf
S = Replace(S, Chr(0) & K, "")
Loop
End If
Re.Close
Set Re = Nothing
MsgBox M
End Sub

2006-12-21 02:46:11 補充：
pop大大:您過獎了,大家集思廣益,才能更上一層樓^_^
亡靈兄:請受小弟一拜Orz

2006-12-21 03:05:11 補充：
To 亡靈兄:
好像有點BugㄋㄟXD,正確答案應是a1=3,b1=4,c1=4,d1=1
但您的查詢1顯示是:a1=3,b1=2,c1=4,d1=1

2006-12-22 02:17:25 補充：
真的是SQL才是王道,亡靈兄,我真的想請一尊回去供奉,早晚三柱香Or2

Answer 3

1:在 Data 表單作一個 指令按鈕 名稱 Command0
2:在 click 輸入(或copy) 下列:
Private Sub Command0_Click()
Dim S1 As DAO.Recordset
Set S1 = CurrentDb.OpenRecordset("Data")
For S = 1 To S1.RecordCount
For S2 = 0 To S1.Fields.Count - 1
If S1.Fields(S2) <> "" Then
If S1.Fields(S2) = "a1" Then K1 = K1 + 1: K11 = "a1=" & K1 & "次"
If S1.Fields(S2) = "b1" Then K2 = K2 + 1: K22 = "b1=" & K2 & "次"
If S1.Fields(S2) = "c1" Then K3 = K3 + 1: K33 = "c1=" & K3 & "次"
If S1.Fields(S2) = "d1" Then K4 = K4 + 1: K44 = "d1=" & K4 & "次"
End If
Next
S1.MoveNext
Next
MsgBox K11 & Chr(10) & K22 & Chr(10) & K33 & Chr(10) & K44
End Sub
希望有幫上你的忙.

2006-12-20 20:35:03 補充：
W.J.S:好
又快又精準的回答效率.高手!!
有你作伴.路將愈走愈寬闊!
謝謝!! 晚安