'数据库ACCESS2000
'数据表名称 OutData 主要字段有ID、Name、Coding、Units、Quantity、TotalPrice
'想通过Name进行分组查询,得到数据集应该有Name、Coding、Units,Sum(Quantity),Sum(TotalPrice)'查询的主要代码如下
Dim cnSave As ADODB.Connection '存放用来打印的数据
Dim rsSave As ADODB.Recordset
Set cnSave = New ADODB.Connection
Set rsSave = New ADODB.Recordset
With cnSave
.ConnectionString = "Driver=Microsoft Access Driver (*.MDB);" & _
"DBQ=" & strCustomProDB
.Open
End With
strQuery = ??'哪位高手可以提供这一SQL语句,我已不行了'strQuery = "select A.Name,Sum(Quantity) as Quantity from OutData A Group by Name" 这一句不对 With rsSave
.ActiveConnection = cnSave
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Open strQuery
End With
If rsSave.RecordCount = 0 Then
rsSave.Close
cnSave.Close
Exit Sub
End If
.......
'数据表名称 OutData 主要字段有ID、Name、Coding、Units、Quantity、TotalPrice
'想通过Name进行分组查询,得到数据集应该有Name、Coding、Units,Sum(Quantity),Sum(TotalPrice)'查询的主要代码如下
Dim cnSave As ADODB.Connection '存放用来打印的数据
Dim rsSave As ADODB.Recordset
Set cnSave = New ADODB.Connection
Set rsSave = New ADODB.Recordset
With cnSave
.ConnectionString = "Driver=Microsoft Access Driver (*.MDB);" & _
"DBQ=" & strCustomProDB
.Open
End With
strQuery = ??'哪位高手可以提供这一SQL语句,我已不行了'strQuery = "select A.Name,Sum(Quantity) as Quantity from OutData A Group by Name" 这一句不对 With rsSave
.ActiveConnection = cnSave
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Open strQuery
End With
If rsSave.RecordCount = 0 Then
rsSave.Close
cnSave.Close
Exit Sub
End If
.......
改成:
Sum(Quantity) as Sum_Q 或者其他名字
在Access里面建一个查询看看
select Name、Sum(Quantity) as Quantiry from OutData Group by Name
则不为空集,加一个检索字段再执行就为空了,我检查了数据表,字段就如前所述,有两百条记录,不知啥原因?
实在搞不懂,为什么非得要加上数据表名称才行,
select OutData.Name,OutData.Units,OutData.Coding,Sum(OutData.Quantity) as Quantity,Sum(OutData.TotalPrice) as TotalPrice from OutData Group by Name,Coding,Units
select [Name],OutData.Units,OutData.Coding,Sum(OutData.Quantity) as Quantity,Sum(OutData.TotalPrice) as TotalPrice from OutData Group by Name,Coding,Units因为name是数据库保留字