用sql实现多条件求和

shuyee

表头格式要固定啊.
用msquery+透视表,sheet3,是先导入到excel,再用多数据源做的透视表.
说一下查询语句:
SELECT iif(isnull(fdcc),dgfd,fdcc) AS '规格', iif(isnull(xklx),dgfdk,xklx) AS '类型', fdgs+dgfdgs AS '数量'
FROM `C:\temp\Database`.消费明细 A
其实在这句的基础上,也可以直接统计的(用group by),你自己想吧,因为你担心数据量问题,我就转到透视表求和了.

baowang519

谢谢前辈，我试试！我这几天以为问题无法解决，又一直在外地，回复晚了！

baowang519

shuyee前辈您好！这些数据是自动跟新还是需要手动跟新，不怕您笑话，我不懂您是怎么做到的！您说的msquery我也不会，group by我也没试成功！{:soso_e106:}
"select iif(isnull(fdcc),dgfd,fdcc) AS '规格', iif(isnull(xklx),dgfdk,xklx) AS '类型', fdgs+dgfdgs AS '数量' from 消费明细 GROUP BY 。。。后面不会补

baowang519

代码这样写不行，没反应！
Private Sub CommandButton1_Click()
    Dim conn As Object
    Dim Sql$
    Sql = "select iif(isnull(fdcc),dgfd,fdcc) AS '规格', iif(isnull(xklx),dgfdk,xklx) AS '类型', fdgs+dgfdgs AS '数量' from 消费明细 GROUP BY iif(isnull(fdcc),dgfd,fdcc) AS '规格', iif(isnull(xklx),dgfdk,xklx) AS '类型'"
    Set conn = CreateObject("adodb.connection")
    With conn
        .Provider = "microsoft.ACE.oledb.12.0"
        .ConnectionString = "Data Source =" & ThisWorkbook.Path & "\Database.accdb"
        .Open
    End With
    [C3].CurrentRegion.Offset(1).ClearContents
    [C4].CopyFromRecordset conn.Execute(Sql)
    Set conn = Nothing
End Sub

zhangjimfu

用数据透视表啊

zhangjimfu

多条件查询求和.rar (26.62 KB, 下载次数: 87)

2011-8-25 13:02 上传

点击文件名下载附件

1. Private Sub CommandButton1_Click()
   
2.     Dim rst, cnn, sql$
   
3.     Set cnn = CreateObject("ADODB.Connection")
   
4.     Set rst = CreateObject("ADODB.Recordset")
   
5.     cnn.Open "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & ThisWorkbook.Path & "" & "Database.mdb"
   
6.     sql = "Select dgfd,dgfdk,Sum(dgfdgs) as 数量 From 消费明细 Where len(dgfd)>0 Group by dgfd,dgfdk"
   
7.     Set rst = cnn.Execute(sql)
   
8.     Range("m3").CopyFromRecordset rst
   
9. End Sub

复制代码

baowang519

感谢zhangjimfu前辈，我去试试！

shuyee

数据透视表,右击,在弹出菜单下选择刷新.
如果有必要,还可以设置"透视表属性":打开文件时刷新,或者每隔多长时间刷新.
象比如我监控生产进度,就是每20分钟刷新.

baowang519

谢谢，非常感谢！

zhangjimfu

呵呵....问题解决了吧