在access中使用DCOUNT解决“不允许在交叉表查询中将子查询用作输出”

suisat

在做一个销售管理数据库时，有下列情况：
销售公司与销售终端签定协议，并按协议兑现每期（按月分）协议费用，兑现费用（多数时候不是以现金形式，而是用商品代替）时要填写收据，一个协议在一个费用期内可能会有多个费用单。
协议兑现用两个表记录：



现在要做一个协议兑现台帐:
生成每个协议每一期兑现的费用金额 如下结构的查询：
协议   费用期 收据单号 费用额

费用额很好汇总出来，但要求在费用单号把同一协议同一费用期的多个单号象多值字段那样列出来，这个就不好办。
我设想是分三步：
第一步：为每个协议的同一期费的各个收据编号按大小 生成一个RANK字段。

第二步：用transform 按RANK值进行维透视：

第三步：生成所要的收据单号：如111号协议201311期单号：3422、2221、234
在做时用字查询生成了第一步中的表：

1. SELECT a.收据编号, a.协议, a.费用期, (select iif(isnull(sum(1)),1,sum(1)+1) from 收据台头
   
2. where 收据编号 > a.收据编号 and 协议 = a.协议 and 费用期= a.费用期)  AS rank
   
3. FROM 收据台头
   
4. AS a;

复制代码

但用transform时：

1. TRANSFORM Max(c.收据编号) AS 收据编号之最大值 SELECT c.费用期, c.协议 FROM 收据维透视前 AS c GROUP BY
   
2. c.协议, c.费用期 PIVOT c.rank;

复制代码

始终报错  a.收据编号不能识别为有效字段，查“帮助”得知：不允许在交叉表查询中将子查询用作输出，弄了半天，在网上查到用DCOUNT代替字查询生成RANK，问题解决：

1. SELECT a.收据编号, a.协议, a.费用期, dcount("[收据编号]","收据台头","[收据编号] >" & a.收据编号
   
2. & "and [协议] = " & a.协议 & "and [费用期] = " & a.费用期)+1 AS
   
3. rank
   
4. FROM 收据台头 AS a;

复制代码

希望对有同样需求的人有所帮助，另外不知这样在查询中合并多条记录的文本字段是不是合适的方法，是否有更好方法？
这方案依然无法处理极端情况，如果一协议的一期费用的收据单很多时，很难办，我现在只能假设它不会超过6个。

wuxiang_123

交叉表可以使用子查询，但有限制。请上传附件，说明你要达到的效果。

suisat

wuxiang_123 发表于 2013-11-8 12:53
交叉表可以使用子查询，但有限制。请上传附件，说明你要达到的效果。

办事处终端资料Vb1 - 副本.rar (113.54 KB, 下载次数: 11)

2013-11-23 16:16 上传

点击文件名下载附件

wuxiang_123

suisat 发表于 2013-11-23 16:16

查询太多，看起来很混乱，请说明需要用到的表，和这些表之间的关系，并提供需要实现的效果表。

suisat

是啊，我还删了很多查询和表和记录，原来更多。

不过这里只用到了：协议明细 收据台头 收据明细 三个表

suisat

原理是这样的，我们办事处与下面的一些销售终端签了促销协议  ，每个月（即费用期）会返给他们费用，他们会要签收（即收据），可能会多批返，所以一期费用可能会有多张收据。
现在要做一个表，形如：协议 费用期 收据单号
收据单号得显示成多值字段那个样子，以便把一个协议的一期费用的多张收据的单号显示出来。

suisat

wuxiang_123 发表于 2013-11-23 17:12
查询太多，看起来很混乱，请说明需要用到的表，和这些表之间的关系，并提供需要实现的效果表。

不过，这个贴主要不是提问题，而是探讨下。

ljchb

wuxiang_123 发表于 2013-11-23 17:12
查询太多，看起来很混乱，请说明需要用到的表，和这些表之间的关系，并提供需要实现的效果表。

具体的限制是什么呢？