对分组报告进行排序 [英] Sorting a grouped report

问题描述

我的架构看起来像这样:

SEQ  GROUP1  GROUP2  GROUP3  VALUE

SEQ大致等于行号-实际上是外部表的排序顺序-GROUPx字段进一步描绘了数据.因此，例如:

SEQ  GROUP1  GROUP2  GROUP3  VALUE
---- ------- ------- ------- ------
1    A       A       A       123
2    A       A       A       456
3    A       B       C       foo
5    X       Y       Z       bar
4    A       B       D       baz

我希望此数据按此层次结构分组，但按SEQ排序.因此，以上数据将生成如下报告:

A
  A
    A
      123
      456
  B
    C
      foo
    D
      baz
X
  Y
    Z
      bar

我一直在使用报表的分组和排序"选项，并且似乎可以将其排序但不分组，或者将分组但不排序！

例如，如果我指定:

• 按GROUP1分组
  • 按GROUP2分组
    • 按GROUP3分组
      • 按SEQ
      排序

分组级别字段中的值确定数据的输出顺序. (在这种情况下，无论如何，我选择的名称都是按字母顺序排列的，但是如果我将GROUP1中的X的名称更改为123，它将显示在报告的顶部.)

如果将按SEQ排序"移到最高优先级，则顺序是正确的，但是分组级别被破坏:Access基本上将SEQ字段视为分组级别，然后重复后续的层次结构该组中的每个值.

在没有每个GROUPx字段(而不是我当前的"global" SEQ)没有一个SEQx字段的情况下，有什么方法可以实现我想要的东西吗?

解决方案

我认为您将必须对此进行左连接.首先，仅使用SEQ创建查询，然后按SEQ对其进行排序.然后进行分组查询，并将其左联接到第一个查询，以便保持正确的顺序.有道理?

您查询的代码应大致为:

SELECT 
First(B.SEQ) AS FirstOfSEQ, tblTest.Group1, tblTest.Group2, tblTest.Group3, tblTest.Val AS Val2 FROM (SELECT tblTest.SEQ FROM tblTest ORDER BY tblTest.SEQ) AS B 
LEFT JOIN tblTest ON B.SEQ = tblTest.SEQ 
GROUP BY tblTest.Group1, tblTest.Group2, tblTest.Group3, tblTest.Val 
ORDER BY First(B.SEQ); 

在报告布局中，按SEQ对其进行排序，然后按其他值对其进行分组，您应该会得到所需的信息.

My schema looks a bit like this:

SEQ  GROUP1  GROUP2  GROUP3  VALUE

Where SEQ is roughly equivalent to the row number -- it's actually sort order, from an external table -- and the GROUPx fields delineate the data further. So, for example:

SEQ  GROUP1  GROUP2  GROUP3  VALUE
---- ------- ------- ------- ------
1    A       A       A       123
2    A       A       A       456
3    A       B       C       foo
5    X       Y       Z       bar
4    A       B       D       baz

I would like this data grouped in this hierarchy, but ordered by SEQ. So, the above data would produce a report like:

A
  A
    A
      123
      456
  B
    C
      foo
    D
      baz
X
  Y
    Z
      bar

I've been playing with the "grouping and sorting" options of the report and I seem to be able to have it ordered but not grouped, or grouped but not ordered!

For example, if I specify:

• Group by GROUP1
  • Group by GROUP2
    • Group by GROUP3
      • Order by SEQ

The values in the grouping level fields determine the order in which the data comes out. (In this case, the names I've chosen are in alphabetical order anyway, but if I changed the name of X in GROUP1 to 123, it would appear right at the top of the report.)

If I move the "Order by SEQ" to the highest priority, the order is correct, but the grouping levels are broken: Access basically treats the SEQ field as a grouping level and then the subsequent hierarchy is repeated for each value in that group.

Is there any way I can achieve what I'm looking for without having a SEQx field for each GROUPx field (rather than my current "global" SEQ)?

解决方案

I think you're going to have to do a Left Join on this. First, create a query using only SEQ, and order it by SEQ. Then take your group-by query and Left Join it to your first query, so that it keeps the proper order. Make sense?

Your query's code should more or less be:

SELECT 
First(B.SEQ) AS FirstOfSEQ, tblTest.Group1, tblTest.Group2, tblTest.Group3, tblTest.Val AS Val2 FROM (SELECT tblTest.SEQ FROM tblTest ORDER BY tblTest.SEQ) AS B 
LEFT JOIN tblTest ON B.SEQ = tblTest.SEQ 
GROUP BY tblTest.Group1, tblTest.Group2, tblTest.Group3, tblTest.Val 
ORDER BY First(B.SEQ); 

In a report layout, order it by SEQ, then group it by the other values and you should get what you need.

这篇关于对分组报告进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！