[已解决]组合货品总价计算问题，寻求帮助！！！

weixin321

组合货品总价计算问题，寻求帮助！！！

attach]246138[/attach]


补充说明

最佳答案

月排行榜 / 总排行榜

mxg825

2012-8-23 14:10

或者用以下的代码

1. 
   
2. Private Sub CommandButton2_Click()
   
3. Range("L5:T65535").ClearContents
   
4. Dim intRow As Integer, t As Single
   
5.     Dim ARow As Integer, SheDate As String
   
6.     t = Timer
   
7.     Dim cn As New ADODB.Connection, sql As String
   
8.     intRow = Sheet1.Range("B65536").End(xlUp).Row
   
9.     SheDate = "sheet1$B4:J" & intRow
   
10.     cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
    
11.     sql = "select A.货品编号,A.产地,A.货品名称,A.规格,sum(A.总数量),A.单位,sum(B.新总价),ABS(SUM(B.新总价)/sum(A.总数量)) from (" & _
    
12.         "(select 货品编号,产地,货品名称,规格,单位,单号,sum(数量) AS 总数量 from [" & SheDate & "] WHERE 类型 ='组合货品'GROUP BY 货品编号,产地,货品名称,规格,单位,单号) as a " & _
    
13.             " left join " & _
    
14.         "(select 单号,sum(总价) as 新总价 from [" & SheDate & "] WHERE 类型 ='明细货品' GROUP BY 单号) as B " & _
    
15.         "ON A.单号=B.单号) GROUP BY A.货品编号,A.产地,A.货品名称,A.规格,A.单位"
    
16.     Sheet1.Range("L5").CopyFromRecordset cn.Execute(sql)
    
17.     cn.Close
    
18.     Set cn = Nothing
    
19. End Sub
    

复制代码

跳转到最佳答案楼层

mxg825

无法理解！。。。。。请重新表达一下！

weixin321

mxg825 发表于 2012-8-23 12:38
无法理解！。。。。。请重新表达一下！

tsigms

尝试了，无法理解，请重新表达……

mxg825

写好了！
代码如下

1. 
   
2. Private Sub CommandButton2_Click()
   
3. Range("l5:T65535").ClearContents
   
4. Dim intRow As Integer, t As Single
   
5.     Dim ARow As Integer, SheDate As String
   
6.     t = Timer
   
7.     Dim cn As New ADODB.Connection, sql As String
   
8.     intRow = Sheet1.Range("B65536").End(xlUp).Row
   
9.     SheDate = "sheet1$B4:J" & intRow
   
10.     cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
    
11.     sql = "select A.货品编号,A.产地,A.货品名称,A.规格,sum(A.数量),A.单位,sum(B.新总价),ABS(SUM(B.新总价)/sum(A.数量)) from (" & _
    
12.         "(select * from [" & SheDate & "] WHERE 类型 ='组合货品') as a " & _
    
13.             " left join " & _
    
14.         "(select 单号,sum(总价) as 新总价 from [" & SheDate & "] WHERE 类型 ='明细货品' GROUP BY 单号) as B " & _
    
15.         "ON A.单号=B.单号) GROUP BY A.货品编号,A.产地,A.货品名称,A.规格,A.单位"
    
16.     Sheet1.Range("L5").CopyFromRecordset cn.Execute(sql)
    
17.     cn.Close
    
18.     Set cn = Nothing
    
19. End Sub

复制代码

weixin321

mxg825 发表于 2012-8-23 13:15
写好了！
代码如下

老师发现一个错误！

主表.rar (20.05 KB, 下载次数: 9)

2012-8-23 13:47 上传

点击文件名下载附件

mxg825

你自己数据源有误！

mxg825

两个组合 名称相同 还用 相同的 单号！

mxg825

或者用以下的代码

1. 
   
2. Private Sub CommandButton2_Click()
   
3. Range("L5:T65535").ClearContents
   
4. Dim intRow As Integer, t As Single
   
5.     Dim ARow As Integer, SheDate As String
   
6.     t = Timer
   
7.     Dim cn As New ADODB.Connection, sql As String
   
8.     intRow = Sheet1.Range("B65536").End(xlUp).Row
   
9.     SheDate = "sheet1$B4:J" & intRow
   
10.     cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
    
11.     sql = "select A.货品编号,A.产地,A.货品名称,A.规格,sum(A.总数量),A.单位,sum(B.新总价),ABS(SUM(B.新总价)/sum(A.总数量)) from (" & _
    
12.         "(select 货品编号,产地,货品名称,规格,单位,单号,sum(数量) AS 总数量 from [" & SheDate & "] WHERE 类型 ='组合货品'GROUP BY 货品编号,产地,货品名称,规格,单位,单号) as a " & _
    
13.             " left join " & _
    
14.         "(select 单号,sum(总价) as 新总价 from [" & SheDate & "] WHERE 类型 ='明细货品' GROUP BY 单号) as B " & _
    
15.         "ON A.单号=B.单号) GROUP BY A.货品编号,A.产地,A.货品名称,A.规格,A.单位"
    
16.     Sheet1.Range("L5").CopyFromRecordset cn.Execute(sql)
    
17.     cn.Close
    
18.     Set cn = Nothing
    
19. End Sub
    

复制代码

weixin321

mxg825 发表于 2012-8-23 14:04
两个组合 名称相同 还用 相同的 单号！

不好意思，是我没有注意到，非常抱歉！！！