对已分组的查询使用"totals“函数

Question

在查询中使用像group by这样的特性时，我在使用聚合函数时遇到了问题。我的聚合函数不是应用于整个查询记录集，而是仅应用于由查询的性质确定的选择组。例如：

  Person      Date      Able
 -----------------------------
    A       21/05/13     0
    B       21/05/13    -1
    C       21/05/13    -1
    D       21/05/13     0

(grouped by Person, Date, Able)

应用聚合函数时：

 Person      Date      Able    Max(Able)   Min(Date)
----------------------------------------------------
   A       21/05/13     0          0       21/05/13  
   B       22/05/13    -1         -1       22/05/13
   C       23/05/13    -1         -1       23/05/13
   D       24/05/13     0          0       24/05/13

除非数据完全解组，否则聚合函数是完全冗余的。到目前为止，我一直在使用以下方法来解决这个问题：

1)使用另一个查询引用初始查询并确定真正的聚合值。2)让表单使用d函数(dlookup、dcount等)调用第二个查询

在我的特定场景中，我有一个列表(非常类似于上面)，它需要以特定的顺序(根据ID排名)来显示。但是，我在查询中使用了一个表达式来定义不同类型的排名。这个想法是显示(使用条件格式)这个新排名中的第一条记录。下图所示

 Person      Date      ID   CalculatedRank    
--------------------------------------------
   A       21/05/13     1        4
   B       21/05/13     2        2
   C       21/05/13     3        3
   D       21/05/13     4        1

理想情况下，我希望有另一个列来确定哪一个是第一个，可以很容易地通过以下方式获得：

first: [CalculatedRank] = Min( [CalculatedRank] )

但是如上所述，Min()没有给我1，它是以每行为单位给我的(最小值并不总是1，所以我不能随意设置它)。

现在，我使用一个单独的查询来引用第一个查询，并根据计算出的排名对其进行排序。然后，条件格式可以使用dlookup来确定它是否是第二个查询中的第一个查询。但是，每次表单刷新或调用requery时，每一行的条件格式都会触发另一个dlookup，然后该dlookup引用第一个查询，重新计算每一行的新排名！

正如您可以想象的那样，延迟是显而易见的，导致游标空闲超过5秒。我不太确定访问的内部机制，但是使用内置调试器，对4行记录集进行重新查询会导致我的CalculateRank()函数被调用12次，这完全是通过调用第二个查询的条件格式实现的。

总之，我已经将范围缩小到需要一个单独的查询(因此需要dlookup)才能正确使用聚合函数。如果我能够将所有内容都保存在一个查询中，条件格式就不需要在另一个查询上使用dlookup来确定其状态。

我确信我不是唯一一个在这方面遇到问题的人，我想知道是否有任何解决方案可以避免所有堆叠查询。

一如既往，任何帮助都是非常感谢的！

Answer 1

哇，我明白你的意思了！对于我的表Table1

Person  Date        ID
------  ----------  --
A       2013-05-21   1
B       2013-05-21   2
C       2013-05-21   3
D       2013-05-21   4

和我的查询qryTable1Ranked

SELECT Table1.*, CalculateRank([ID]) AS CalculatedRank
FROM Table1;

它在标准VBA模块中使用以下函数

Public Function CalculateRank(ID As Long) As Long
Dim r As Long
Select Case ID
    Case 1
        r = 4
    Case 4
        r = 1
    Case Else
        r = ID
End Select
CalculateRank = r
Debug.Print "x"
End Function

并返回

Person  Date        ID  CalculatedRank
------  ----------  --  --------------
A       2013-05-21   1               4
B       2013-05-21   2               2
C       2013-05-21   3               3
D       2013-05-21   4               1

当我双击查询以在数据表视图中打开它时，我的排名函数被调用了4次，每一行调用一次。

如果我基于该查询创建一个连续表单，并打开该表单，我的函数将被调用4次。然后，如果我使用Value = DMin("CalculatedRank", "qryTable1Ranked")在CalculatedRank文本框中添加条件格式，那么我的函数将被调用32次！

我发现，如果我添加一个名为txtMinCalculatedRank的不可见的未绑定文本框，可以将其减少一半(到16倍)，在表单后面使用以下代码……

Option Compare Database
Option Explicit

Private Sub Form_Load()
UpdateMinCalculatedRank
End Sub

Private Sub UpdateMinCalculatedRank()
Me.txtMinCalculatedRank.Value = DMin("CalculatedRank", "qryTable1Ranked")
End Sub

...and将条件格式设置规则更改为Value = [txtMinCalculatedRank]。

我发现，如果我将表单的Record Source从qryTable1Ranked更改为Table1 (基表)，并将CalculatedRank文本框的Control Source更改为=CalculateRank([ID]) (仍然使用前一个调整中的技巧)，则可以将其再减少一半(到8倍)。

我认为在不创建临时表或在基表中持久化CalculatedRank (也许还有一个"IsMin“标志)的情况下，这可能就是最好的结果。