Office中国论坛/Access中国论坛

标题: access求和 [打印本页]
作者: laomms    时间: 2014-1-15 12:58
标题: access求和
本帖最后由 laomms 于 2014-1-17 11:55 编辑

access表customer中有name和amount，它的值如下：

name      amount

tony        3000
kavin       2000
steven     2500
kavin       1500
tony        2000
steven     2500
kavin       1500
steven     1000
...            ...

现在有客户汇款过来，金额为5000，但是是三单合成的，只知道是同一个人的，如何按姓名列出所有的三单记录。
[attach]53011[/attach]

想到到的结果是(按单号大小顺序)：

其中一组的可能
108  STEVEN 1500
154  STEVEN 500
210  STEVEN 3000

另外一组的可能
134 TONY 1500
205 TONY 2500
245 TONY 1000






作者: laomms    时间: 2014-1-16 08:42
没有高手回复，顶一次。
作者: leonshi    时间: 2014-1-16 08:46
做一个选择查询，应该可以满足你的要求
作者: tzh1600    时间: 2014-1-16 15:15
按name汇总查询,找出合计等于5000的name,查询他们的记录
作者: tzh1600    时间: 2014-1-16 15:31
本帖最后由 tzh1600 于 2014-1-16 15:34 编辑

晕,你这个属于勾兑的做法,勾兑前不清楚是谁的帐单,虽然不是那么简单,但是这个是有意义的,期待高手
不过你既然要列出可能的单子,那么最后估计还是要手工勾兑,那么仅仅列出可能的客户及帐单,或许会简单许多
作者: 玉树TMD临风    时间: 2014-1-16 19:02
这个有意思，研究一下。
作者: laomms    时间: 2014-1-16 19:16
本帖最后由 laomms 于 2014-1-16 21:33 编辑

假如不是三单的合计，而是某一客户的全部合计等于5000，然后列出构成这5000的所有这个客户的单号，这样应该简单点吧。
现实中确实碰到了这样的问题，由于客户比较多，不可能一个一个打电话询问，只看到银行账号里有客户汇了5000，也没通知我们，单个单号金额等于5000的客户都咨询了，不是他们，所以这5000应该是至少两单以上的合计，按照惯例这几个单子应该是连续的单号，也有可能是某一客户全部的合计。只要列出可能的清单，就缩小很大一个范围。

现在能筛选的条件1是客户的单号大于2，条件2，客户的任何一单金额都要小于5000，条件3，是某一个的连续单号（单号是由小到大递增的。）
cnn.Open "Provider=Microsoft.Jet.Oledb.4.0;data Source=" & ThisWorkbook.Path & "\data.MDB "
sql1 = "select BILL, strName,count(strName) as 个数, sum(amount) as 总计 from [Costmer] group by strName,BILL "
sql = "select A.BILL,A.strName, A.个数, A.总计, b.amount from (" & sql1 & ") as A left join [Costmer] AS b on A.strName=b.strName where A.个数 > 2 and b.amount < 5000 order by a.strName  a.strName "
作者: 玉树TMD临风    时间: 2014-1-16 19:32
laomms 发表于 2014-1-16 19:16
假如不是三单的合计，而是某一客户的全部合计等于5000，然后列出构成这5000的所有这个客户的单号，这样应该 ...

假如出来３个符合条件的结果，你们怎么办，打电话确认吗？
作者: laomms    时间: 2014-1-16 19:34
本帖最后由 laomms 于 2014-1-16 21:37 编辑

是的，实际数据远远多于附件中的数据，用人工算将花费大量精力。
作者: 玉树TMD临风    时间: 2014-1-16 20:09
laomms 发表于 2014-1-16 19:34
是的，实际数据远远多于附件中的数据，用人工算将花费大量精力。

如果你确认是三单合成的,问题现在倒能解决了,不知道你这个有没有2个单子加起来=5000的可能性?
作者: 玉树TMD临风    时间: 2014-1-16 20:20
用了个笨办法,把2单和3单的全部列举出来:
select * from
(SELECT a.name, a.amount+b.amount+c.amount as jg FROM 表2 AS a, 表2 AS b, 表2 AS c
where a.name & a.amount<>b.name & b.amount and a.name & a.amount<>c.name & c.amount and b.name & b.amount<>c.name & c.amount
and a.name=b.name and a.name=c.name and b.name=c.name
union
SELECT a.name, a.amount+b.amount as jg FROM 表2 AS a, 表2 AS b
where a.name & a.amount<>b.name & b.amount and a.name=b.name) as z
where z.jg=5000
作者: 玉树TMD临风    时间: 2014-1-16 20:22
玉树TMD临风 发表于 2014-1-16 20:20
用了个笨办法,把2单和3单的全部列举出来:
select * from
(SELECT a.name, a.amount+b.amount+c.amount a ...

不知道你同一个人有没有相同的amount,我是把同一个人相同的价格单给去掉了
作者: lynnwang    时间: 2014-1-16 20:45
用一个SELECT 语句的GROUP BY 子句进行分组查询就可以，没这么复杂
作者: lynnwang    时间: 2014-1-16 20:46
然后利用HAVING对结果进行条件筛选
SELECT Sum(Costmer.AMOUNT) AS 小计, Costmer.strName
FROM Costmer
GROUP BY Costmer.strName
HAVING (((Sum(Costmer.AMOUNT))>9000));

把这个复制到ACC中查询运行就好，9000替换成你需要的值
作者: 玉树TMD临风    时间: 2014-1-16 21:06
lynnwang 发表于 2014-1-16 20:46
然后利用HAVING对结果进行条件筛选
SELECT Sum(Costmer.AMOUNT) AS 小计, Costmer.strName
FROM Costmer
...

你这个是把某人所有的价格全部加上后的值,楼主要的不一定是一个人的所有订单,有可能是2笔,有可能是3笔
作者: aslxt    时间: 2014-1-16 23:27
本帖最后由 aslxt 于 2014-1-16 23:32 编辑

这种问题，一般是列举出欠账总金额大于或等于收款金额的客户就行了，如果没有其他辅助信息的话，没有肯定的结果的.比如某人三个单子欠款750、3500、4800，一共9050元，目前只有5000元可以支付欠款，就全部给你汇过来了，如果欠款大于等于5000元的人很多，你只能等待！！！
甚至有可能是预付款、或部分预付款。
除非贵单位与客户约定：要么不付款，要么付（一个或多个）订单的全款，
作者: laomms    时间: 2014-1-17 08:51
不是预付款或部分付款，确实是几单的合成，而且是最小的几单的合成，有些客户会打电话通知明确是哪几单。
一般是两单或三单。
所以想先统计所有客户最小的两个单号金额合计为5000的名单列表。
再统计所有客户最小的三个单号金额合计为5000的名单列表。应该涉及到排列组合的问题了。
作者: laomms    时间: 2014-1-17 09:11
本帖最后由 laomms 于 2014-1-17 11:25 编辑
玉树TMD临风 发表于 2014-1-16 22:22
不知道你同一个人有没有相同的amount,我是把同一个人相同的价格单给去掉了

真的很感谢 “玉树TMD临风”，等我仔细慢慢研究你的代码，最好能在附件中改下。不过同一个人不同的单号有相同的金额的。
作者: tzh1600    时间: 2014-1-17 10:11
总觉得楼主的表结构有问题,相同的BILL号为什么有多条记录,不同的金额?
作者: laomms    时间: 2014-1-17 10:43
本帖最后由 laomms 于 2014-1-18 15:26 编辑
tzh1600 发表于 2014-1-17 12:11
总觉得楼主的表结构有问题,相同的BILL号为什么有多条记录,不同的金额?

确实有这种情况，相同的单号，但是不同的金额，这其实是同一单但是不同产品，因为产品不只是一个，所以，写入数据库时是还按产品的不同分类。

作者: tzh1600    时间: 2014-1-17 10:49
所以说表结构有问题,正常情况应该是拆分成汇总表与明细表,用汇总表ID关联
作者: laomms    时间: 2014-1-17 10:54
本帖最后由 laomms 于 2014-1-18 15:27 编辑

目前只能用列举所有单的方法，但是统计所有客户的欠款列单数据很长。


作者: leonshi    时间: 2014-1-17 10:55
同意tzh1600的建议，其实建议先理理逻辑，然后再去设计表，否则想到一点再去修改就麻烦了
作者: tzh1600    时间: 2014-1-17 10:57
受玉树临风启发,做了个三数组合查询,也是笨办法
SELECT Costmer.strName, Costmer.AMOUNT, Costmer_1.AMOUNT, Costmer_2.AMOUNT, [costmer].[amount]+[costmer_1].[amount]+[costmer_2].[amount] AS hj, Costmer.ID, Costmer.BILL, Costmer_1.ID, Costmer_1.BILL, Costmer_2.ID, Costmer_2.BILL
FROM (Costmer INNER JOIN Costmer AS Costmer_1 ON Costmer.strName = Costmer_1.strName) INNER JOIN Costmer AS Costmer_2 ON Costmer_1.strName = Costmer_2.strName
WHERE ((([costmer].[amount]+[costmer_1].[amount]+[costmer_2].[amount])=5000) AND ((Costmer_1.ID)>[costmer].[id]) AND ((Costmer_2.ID)>[costmer_1].[id]))
ORDER BY Costmer.strName, Costmer.ID;
作者: laomms    时间: 2014-1-17 10:59
tzh1600 发表于 2014-1-17 12:49
所以说表结构有问题,正常情况应该是拆分成汇总表与明细表,用汇总表ID关联

这个简单的，主要套用一次select就可以了
sql1 = "select BILL, strName, sum(amount) as 金额,IFPAID from [Costmer] where IFPAID like 'NotPaid' group by strName,BILL"
sql2 = "select BILL, strName,总计 from (" & sql1 & ") group by strName,BILL "

sql2中得到的就是汇总后的客户单号表
作者: laomms    时间: 2014-1-17 11:31
tzh1600 发表于 2014-1-17 12:57
受玉树临风启发,做了个三数组合查询,也是笨办法
SELECT Costmer.strName, Costmer.AMOUNT, Costmer_1.AMOU ...

这个已经很好了，基本上就是这样了，只是少个单号顺序，客户会按单号顺序结算，而且不会跳单结算，比如说第一单和第三单结算了，第二单却没结算，不会出现这样的例子。
作者: tzh1600    时间: 2014-1-17 12:11
本帖最后由 tzh1600 于 2014-1-17 12:12 编辑
laomms 发表于 2014-1-17 11:31
这个已经很好了，基本上就是这样了，只是少个单号顺序，客户会按单号顺序结算，而且不会跳单结算，比如说 ...


那就存在一个问题,你是基于BILL还是基于ID的合计了,我看你示范表中ID与BILL的顺序是乱的,那就不能用ID来合计判断,应该用BILL,则基础数据应该是group by strName,BILL 的
总之不论ID还是BILL,用逐个> 可以列出各种组合
作者: laomms    时间: 2014-1-17 12:32
tzh1600 发表于 2014-1-17 14:11
那就存在一个问题,你是基于BILL还是基于ID的合计了,我看你示范表中ID与BILL的顺序是乱的,那就不能用ID ...

谢谢，基本上可以了。
[attach]53019[/attach]

作者: laomms    时间: 2014-1-17 15:22
本帖最后由 laomms 于 2014-1-17 17:24 编辑

确实出现同一单号没有合并金额的毛病，这样得出的结构是错误的，不是每单总金额的合计，而是每一单子项的合计是5000，是不是得先生成一次临时表。

[attach]53020[/attach]

作者: tzh1600    时间: 2014-1-17 16:51
laomms 发表于 2014-1-17 15:22
确实出现同一单号没有合并金额的毛病，这样得出的结构是错误的，不是每单总金额的合计，而是每一单子项的合 ...

不用,建立一个汇总查询做为数据源即可,原来的ID改用BILL
作者: 玉树TMD临风    时间: 2014-1-17 17:40
我做了一个查询，能列出所有两单或三单的组合，包括ID号，有个小问题，就是有些组合是重复的，暂时不知道怎么去掉，比如ID12,18,29合计5000远，29,12,18也是5000，结果都显示出来了
作者: laomms    时间: 2014-1-17 17:57
tzh1600 发表于 2014-1-17 18:51
不用,建立一个汇总查询做为数据源即可,原来的ID改用BILL

做了汇总查询后(比如：sql2)，里面的子项都是已经是按单号排序的汇总后的数据了，但是具体如何引进汇总后的数据，我直接用sql2替代原始的costmer表出错。
作者: 玉树TMD临风    时间: 2014-1-17 19:33
这是查询语句代码，有些重复的不知道怎么去掉，你试试看
select * from (SELECT a.ID & "+" & b.ID & "+" & c.ID as id,a.strname,a.amount+b.amount+c.amount as amount
FROM Costmer a,costmer b,costmer c
where (a.id<>b.id and a.id<>c.id and b.id<>c.id) and (a.strname=b.strname and a.strname=c.strname and b.strname=c.strname)) z
where amount=5000


我只选取了３个单子，如果有２个单子你可以用union按这个思路再加一行代码
作者: laomms    时间: 2014-1-17 20:13
本帖最后由 laomms 于 2014-1-17 22:25 编辑

[attach]53021[/attach]

数据库中的144这两单其实是一单，金额是按单号分的，所以144的这单的金额实际是1200. 而且只能做为三单中的其中一单，所以统计前得先按单号汇总一下金额才能做为初步的数据。
作者: laomms    时间: 2014-1-18 09:19
本帖最后由 laomms 于 2014-1-18 12:15 编辑

汇总的问题还是解决不了！
目前用新建表然后删除的方法：
sql1 = "select BILL, strName, sum(amount) as 金额, IFPAID from [Costmer] where IFPAID like 'NotPaid' group by strName,BILL"
sql = " SELECT BILL,strName,金额,  IFPAID  INTO Costmer_bake FROM (" & sql1 & ")"
cnn.Execute (sql)
然后从新表Costmer_bake中选择数据
...


用完后删除
    sql = "DROP TABLE " & "Costmer_bake"
    cnn.Execute (sql)


但是数据库大小都会增的很大，每次得压缩一次。



作者: tzh1600    时间: 2014-1-21 10:38
本帖最后由 tzh1600 于 2014-1-21 10:39 编辑
laomms 发表于 2014-1-18 09:19
汇总的问题还是解决不了！
目前用新建表然后删除的方法：
sql1 = "select BILL, strName, sum(amount) as ...


看双数连续查询和三数连续查询,ACCESS建立查询就可以,不要建新表
作者: laomms    时间: 2014-1-22 09:52
tzh1600 发表于 2014-1-21 12:38
看双数连续查询和三数连续查询,ACCESS建立查询就可以,不要建新表

非常感谢tzh1600的帮助，但是我是想要在那个合计查询之前先汇总一下，然后再列出所有的三单记录。
我现在用新建表，然后删除的方法，不知道有没有其他办法。

  1. Private Sub CommandButton1_Click()
  2. Sheet1.Range("A1:L100").ClearContents
  3. Dim sql1, sql2, sql
  4. Dim sum, sName
  5. cnn.Open "Provider=Microsoft.Jet.Oledb.4.0;data Source=" & ThisWorkbook.Path & "\data.MDB "
  6. sql1 = "select 销售清单号,公司名称,sum(销售金额) as 金额, 付款情况 from [XSD] where 付款情况 like 'NotPaid' group by 公司名称,销售清单号,付款情况"
  7. sql = " SELECT 销售清单号,公司名称,金额,付款情况 INTO XSD_bake FROM (" & sql1 & ")"
  8. cnn.Execute (sql)
  9. sql = "SELECT XSD_bake.公司名称, XSD_bake.金额, A.金额, B.金额, C.金额, XSD_bake.金额 + A.金额+ B.金额+ C.金额 AS hj, XSD_bake.销售清单号, A.销售清单号, B.销售清单号, C.销售清单号, XSD_bake.付款情况 FROM ((XSD_bake INNER JOIN XSD_bake AS A ON XSD_bake.公司名称 = A.公司名称) INNER JOIN XSD_bake AS B ON A.公司名称 = B.公司名称) INNER JOIN XSD_bake AS C ON B.公司名称 = C.公司名称 WHERE (XSD_bake.金额 + A.金额 + B.金额+ C.金额 = 6000) And (A.销售清单号 > XSD_bake.销售清单号) And (B.销售清单号 > A.销售清单号) And (C.销售清单号 > B.销售清单号)  ORDER BY XSD_bake.公司名称, XSD_bake.销售清单号;"
  10. rst.Open sql, cnn, adOpenKeyset, adLockOptimistic
  11.     If rst.EOF = False Then
  12.         For i = 1 To rst.RecordCount
  13.               Sheet1.Cells(i, 1) = rst.Fields(0).Value
  14.               Sheet1.Cells(i, 2) = rst.Fields(1).Value
  15.               Sheet1.Cells(i, 3) = rst.Fields(2).Value
  16.               Sheet1.Cells(i, 4) = rst.Fields(3).Value
  17.               Sheet1.Cells(i, 5) = rst.Fields(4).Value
  18.               Sheet1.Cells(i, 6) = rst.Fields(5).Value
  19.               Sheet1.Cells(i, 7) = rst.Fields(6).Value
  20.               Sheet1.Cells(i, 8) = rst.Fields(7).Value
  21.               Sheet1.Cells(i, 9) = rst.Fields(8).Value
  22.               Sheet1.Cells(i, 10) = rst.Fields(9).Value
  23.               Sheet1.Cells(i, 11) = rst.Fields(10).Value
  24.           rst.MoveNext
  25.       Next i
  26.   End If
  27.   rst.Close: Set rst = Nothing
  28.   sql = "DROP TABLE " & "XSD_bake"
  29.   cnn.Execute (sql)
  30.   cnn.Close: Set cnn = Nothing
  31. End Sub
复制代码
[attach]53026[/attach]

作者: tzh1600    时间: 2014-1-22 15:04
laomms 发表于 2014-1-22 09:52
非常感谢tzh1600的帮助，但是我是想要在那个合计查询之前先汇总一下，然后再列出所有的三单记录。
我现 ...


肯定是要先汇总的,我的例子里的SumCostom查询就是
作者: laomms    时间: 2014-1-23 09:13
如果可以的话把37楼附件里的代码改下，改成不用在access中新建表的方法。
作者: tzh1600    时间: 2014-1-26 11:15
其实我的方法就是在ACCESS里建立汇总查询,然后以汇总后的查询为数据源,找出总额符合要求且连续的单子(也是通过建立查询的方式实现),实现的方法在我36楼的附件里都体现了



欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/) Powered by Discuz! X3.3