如何在 Access 2013 中创建多值交叉表查询? [英] How can I Create a Multi-Value CrossTab Query in Access 2013?

问题描述

我的问题

我正在使用 Access 2013.我有一组数据需要根据两个字段进行细分(最好使用交叉表查询).我的问题是，我需要显示每个值"([Amt Total] 字段)的 SUM 和 COUNT——不幸的是，Access 尚未允许多值交叉表查询.

I'm working in Access 2013. I have a set of data that needs broken down based on two fields (best done with a crosstab query). My issue is, I need to show the SUM and the COUNT of each 'Value' ([Amt Total] field) -- and unfortunately Access has yet to allow multi-value crosstab queries.

如果有人在网上搜索遇到类似问题，则仅供参考 - 如果您尝试在 Access 中添加多个值"字段，则会出现错误:

要创建交叉表查询，您必须指定一个或多个 Row标题选项、一个列标题选项和一个值选项

To create a crosstab query, you must specify one or more Row Heading(s) options, one Column Heading option, and one Value option

疑难解答

我已经阅读了很多关于如何做到这一点的文章，包括 联合, JOINs 和 条件聚合 -- 但没有一个工作.

I've read numerous articles about how to do this, including UNIONs, JOINs, and conditional aggregates -- yet none have worked.

此时我有两个 FUNCTIONAL 交叉表查询...一个返回 COUNT，按日期，按状态；一个按日期和状态返回 SUM.我还没有找到一种方法将计数和总和组合到一个表中.在这一点上，我不在乎它是通过查询还是报告完成 - 但我认为查询是最简单的方法.

At this point I have two FUNCTIONAL crosstab queries... one that returns the COUNT, by date, by status; and one that returns the SUM, by date, by status. I've yet to find a way to combine the count and the sum into a single table. At this point I don't care if it's done via a query or a report - but I assume a query is the easiest way.

我的状态比下面显示的要多得多，但为了了解我的数据，这里是条件聚合解决方案的一个片段:

I have quite a few more status' than those shown below, but for an idea of my data, here is a snip from the conditional aggregate solution:

SELECT Format([DOS],"yyyy-mm") AS [Month of Service],
       Count(IIF(myStatus='OPEN', myStatus, Null)) As [Open Accts],
       Sum(IIF(myStatus='OPEN', [Amt Total], Null)) As [Open Amt], 
       Count(IIF(myStatus='PAID', myStatus, Null)) As [Paid Accts],
       Sum(IIF(myStatus='PAID', [Amt Total], Null)) As [Paid Amt]
FROM [myTable]
GROUP BY Format([DOS],"yyyy-mm")

我的目标

获取单个结果表.第 1 列 = 服务月份(该月内所有原始记录的总和).第 2 列 = 总记录数，按月.第 3 列 = [Amt Total] 字段的总和，按月份.最后(交叉表数据透视功能出现的地方)，第 4-x 列 = [状态 X、状态 Y... 和状态 Z] 的记录计数，按月份；x-y 列 = [状态 X、状态 Y... 和状态 Z] 的记录总和，按月份.

Get a single results table. Column 1 = Month of Service (all raw records within that month combined). Column 2 = TOTAL Count of records, by Month. Column 3 = TOTAL Sum of [Amt Total] field, by Month. And finally (where the Crosstab pivot functionality comes in), column 4-x = Count of records [of Status X, Status Y... and Status Z], by Month; and columns x-y = Sum of records [of Status X, Status Y... and Status Z], by Month.

非常感谢在单个查询表中获取 SUM 和 COUNT 枢轴值的任何帮助！

Any help getting SUM and COUNT pivot values in a single query table would be greatly appreciated!

示例数据

CrossTab金额"查询结果

CrossTab "Amount" Query Result

Month of Service Gross Charges  DENIED     OPEN       PAID
2011-12          $1,613.20                 $1,613.20            
2012-02          $999.00          $999.00
2012-05          $14,296.00           $14,296.00        
2014-09          $37,776.00 $2,874.00   $8,925.50   $25,976.50  

CrossTab数量"查询结果

CrossTab "Quantity" Query Result

Month of Service    Quantity    DENIED  OPEN    PAID
2011-12                 1                 1         
2012-02                 1          1
2012-05                 1                 1 
2014-09                21          1      2       18        

我的目标是简单地合并这两个表...保持服务月份"作为最左边的列，然后只有数量"、拒绝数量"、开放数量"和支付数量"" 列中的总费用"、拒绝金额"、未结金额"、已支付金额"列均基于服务月份.

My goal is to simply merge the two tables... keeping 'Month of Service' as the far left column, and then just have the "Quantity", "Denied Qty", "Open Qty", and "Paid Qty" columns with the "Gross Charges", "Denied Amt", "Open Amt", "Paid Amt" columns all based on Month of Service.

我的 SQL

CrossTab "Amt" 查询

CrossTab "Amt" Query

TRANSFORM Sum([myTable].[Total]) AS [SumOfTotal]
SELECT Format([DOS],"yyyy-mm") AS [Month of Service], Sum([myTable].[Total]) AS [Gross Charges]
FROM [myTable]
GROUP BY Format([DOS],"yyyy-mm")
PIVOT [myTable].myStatus;

CrossTab数量"查询

CrossTab "Qty" Query

TRANSFORM Count([myTable].[Total]) AS [CountOfTotal]
SELECT Format([DOS],"yyyy-mm") AS [Month of Service], Count([myTable].[Total]) AS [Quantity]
FROM [myTable]
GROUP BY Format([DOS],"yyyy-mm")
PIVOT [myTable].myStatus;

推荐答案

答案是使用内连接(类似于我在其他地方读到的).但是，对于那些不了解 SQL 或无法弄清楚的人来说——我从头开始编写了自己的 SQL 查询，该查询有效.

The answer was to use an inner join (similar to what I've read elsewhere). However, for those of you that don't know SQL, or couldn't figure it out -- I wrote my own SQL query from scratch that worked.

这是一个通用查询，适用于所有遇到类似问题的人.只需复制下面的代码，对以下变量进行替换...您应该可以从那里扩展！

Here is a generic query for all of you that stumble upon this with a similar issue. Simply copy the code below, do a replace for the following varialbes... and you should be able to expand from there!

变量

[myCrossTabQueryCOUNT] = name of your COUNT crosstab query

[myCrossTabQuerySUM] = name of your SUM crosstab query

[Month of Service] = field name that is the primary ROW HEADING for each crosstab query

[mySum] = field name of your 'Value' field in the [myCrossTabQuerySUM] query (in my example it held a Sum value)

[myCount] = field name of your 'Value' field in the [myCrossTabQueryCOUNT] query (in my example it held a Count value) 

SQL 视图查询

SELECT [myCrossTabQueryCOUNT].[Month of Service], [myCrossTabQueryCOUNT].[myCount] AS [Count], [myCrossTabQuerySUM].[mySum] AS [Total Claim Charges]
FROM (([myCrossTabQueryCOUNT] 
INNER JOIN [myCrossTabQuerySUM] 
ON [myCrossTabQueryCOUNT].[Month of Service]=[myCrossTabQuerySUM].[Month of Service])
ORDER BY [myCrossTabQueryCOUNT].[Month of Service];

这篇关于如何在 Access 2013 中创建多值交叉表查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！