Question

vb access 统计 求和

vb+access小程序，数据库连接方式如下
-----------------------------------------------

Public CNN As New ADODB.Connection

Public RST As New ADODB.Recordset
Public STM As New ADODB.Stream
Public Stpath As String
Public Stpath2 As String
Public SQL As String
Public i As Integer
Public TP As Integer
--------------------------
Sub OpenCNN()
Stpath = App.Path & "\kqxt.mdb"
CNN.Open "provider=Microsoft.jet.OLEDB.4.0;data source=" & Stpath & ""
End Sub

--------------
数据类型示例如下
日期 类型
乙 2014-06-28 迟到
甲 2014-07-10 迟到
乙 2014-07-13 早退
丙 2014-07-15 迟到
甲 2014-07-15 早退
现在要按日期范围统计
1、数据库中甲、乙、丙出现的总次数
2、分别统计甲乙丙迟到、早退的次数
如;
日期范围：2014-07-1 至 2014-07-1
总次数 迟到 早退

甲 ? ? ?
乙 ? ? ?
丙 ? ? ?

怎么查询数据库返回结果?可以是表格也可以直接用text文本框显示数据，谢谢
没有编程基础，修改的代码，最好帮我直接写出代码，就是查询的结果怎么在text显示出来

Answer 1

给你个例子，你自己修改你的代码
Private Sub Command1_Click()
Dim i As Integer
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String
Dim cnnStr As String
cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\1.mdb;Persist Security Info=False"
cnn.CursorLocation = adUseClient
cnn.Open cnnStr
SQL = "SELECT a.姓名, a.总次数, b.迟到, c.早退 " & _
"FROM ((select 姓名, count(姓名) as 总次数 from 表1 group by 姓名) AS a " & _
"inner JOIN (select 姓名, count(类型) as 迟到 from 表1 where 类型='迟到' group by 姓名,类型 ) AS b ON a.姓名 = b.姓名) " & _
"inner JOIN (select 姓名, count(类型) as 早退 from 表1 where 类型='早退' group by 姓名,类型 ) AS c ON a.姓名 = c.姓名"
rs.Open SQL, cnn, adOpenKeyset, adLockPessimistic
If rs.RecordCount > 0 Then
For i = 1 To rs.RecordCount
Print rs.Fields("姓名"); rs.Fields("总次数"); rs.Fields("迟到"); rs.Fields("早退")
rs.MoveNext
Next
rs.Close
End If
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

Answer 2

access的查询语句：

SELECT 表.编号,
DCount("类型","表"," 编号='" & [编号] & "' and 日期>=#" & [开始日期] & "# and 日期<=#" & [结束日期] & "#") AS 合计,
DCount("类型","表","类型='迟到' and 编号='" & [编号] & "' and 日期>=#" & [开始日期] & "# and 日期<=#" & [结束日期] & "#") AS 迟到,
DCount("类型","表","类型='早退' and 编号='" & [编号] & "' and 日期>=#" & [开始日期] & "# and 日期<=#" & [结束日期] & "#") AS 早退
FROM 表
GROUP BY 表.编号;

追问

谢谢你的回答。是不是不需要这么复杂，返回的结果我没有加表格控件，用文本显示的
SQL = "select * from kq where 姓名='甲'"
RST.Open SQL, CNN, 1, 3

Text1.Text = RST.RecordCount
RST.Close
CNN.Close
End Sub这样一个个写结果是统计出来，不过感觉比较繁琐，有没有更专业的方法呢？