求 统计Access数据库 进销存数据

lichangjun2012

统计Access数据库 进销存.zip (133.58 KB, 下载次数: 40)

2013-7-13 19:16 上传

点击文件名下载附件

zhaogang1960

短信收到，请测试：

1. Sub 宏1()
   
2.     Dim cnn As New ADODB.Connection
   
3.     Dim Icount%, Stpath, SQL As String
   
4.     Stpath = ThisWorkbook.Path & "\测试.accdb"
   
5.     cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;data Source=" & Stpath
   
6.     SQL = "select a.首批日期,a.供应商,a.商品名称,a.首批件数,a.进货价,a.零售价,b.进货件数,c.销售件数,d.退货件数 from (((select * from SPZL where 组别='" & [c2] & "') a" _
   
7.         & " left join (select 供应商,商品名称,sum(进货件数) as 进货件数 from JH group by 供应商,商品名称) b on a.供应商&a.商品名称=b.供应商&b.商品名称)" _
   
8.         & " left join (select 供应商,商品名称,sum(销售件数) as 销售件数 from XS group by 供应商,商品名称) c on a.供应商&a.商品名称=c.供应商&c.商品名称)" _
   
9.         & " left join (select 供应商,商品名称,sum(退货件数) as 退货件数 from TH group by 供应商,商品名称) d on a.供应商&a.商品名称=d.供应商&d.商品名称"
   
10.     Range("B6:L" & Rows.Count).ClearContents
    
11.     [b6].CopyFromRecordset cnn.Execute(SQL)
    
12.     cnn.Close
    
13.     Set cnn = Nothing
    
14. End Sub

复制代码

zhaogang1960

请看附件
统计Access数据库 进销存.rar (110.53 KB, 下载次数: 61)

2013-7-14 23:31 上传

点击文件名下载附件

lichangjun2012

zhaogang1960 发表于 2013-7-14 23:32
请看附件

非常感谢，正是这种效果，我稍微改动完善了一下，代码如下：

    SQL = "SELECT a.*,b.进件合计,c.销件合计,d.退件合计,"
    SQL = SQL & "IIF(ISNULL(b.进件合计),0,b.进件合计) - IIF(ISNULL(c.销件合计),0,c.销件合计) - IIF(ISNULL(d.退件合计),0,d.退件合计) as 库存件数,"
    SQL = SQL & "IIF(ISNULL(b.进金合计),0,b.进金合计) - IIF(ISNULL(c.销金合计),0,c.销金合计) - IIF(ISNULL(d.退金合计),0,d.退金合计) as 库存金额 "
   
    SQL = SQL & "from (((select 首批日期,供应商,商品名称,进货价,零售价,首批件数 from SPZL where 组别='" & Cells(2, 3) & "') a "
    SQL = SQL & "left join (select 供应商,商品名称,sum(进货件数) as 进件合计,sum(进货金额) as 进金合计 from JH where 组别='" & Cells(2, 3) & "' group by 供应商,商品名称) b on a.供应商&a.商品名称 = b.供应商&b.商品名称) "
    SQL = SQL & "left join (select 供应商,商品名称,sum(销售件数) as 销件合计,sum(销售金额) as 销金合计 from XS where 组别='" & Cells(2, 3) & "' group by 供应商,商品名称) c on a.供应商&a.商品名称 = c.供应商&c.商品名称) "
    SQL = SQL & "left join (select 供应商,商品名称,sum(退货件数) as 退件合计,sum(退货金额) as 退金合计 from TH where 组别='" & Cells(2, 3) & "' group by 供应商,商品名称) d on a.供应商&a.商品名称 = d.供应商&d.商品名称"