How can I Create a Multi-Value CrossTab Query in Access 2013?

Question

My Issue

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.

For reference purposes if someone is searching online with similar issues - the resulting error if you try to add multiple 'Value' fields in Access:

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

Troubleshooting

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

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")

My Goal

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.

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

Example Data

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 "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.

My SQL

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 "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;

TMY TMY · Accepted Answer · 2016-01-14T14:38:44

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!

Variables

[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)

The SQL View Query

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];

How can I Create a Multi-Value CrossTab Query in Access 2013?

2 Answers