title: How can I Create a Multi-Value CrossTab Query in Access 2013?
body: <p><strong>My Issue</strong></p>
  
  <p>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. </p>
  
  <p><em>For reference purposes if someone is searching online with similar issues - the resulting error if you try to add multiple 'Value' fields in Access:</em></p>
  
  <blockquote>
    <p>To create a crosstab query, you must specify one or more Row
    Heading(s) options, one Column Heading option, and one Value option</p>
  </blockquote>
  
  <p><strong>Troubleshooting</strong></p>
  
  <p>I've read numerous articles about how to do this, including <a href="https://stackoverflow.com/questions/5781788/how-to-create-an-access-crosstab-query-with-totals-for-the-columns-and-the-rows">UNIONs</a>, <a href="https://support.microsoft.com/en-us/kb/109939" rel="nofollow noreferrer">JOINs</a>, and <a href="https://stackoverflow.com/questions/32878601/ms-access-crosstab-query-with-multiple-columns">conditional aggregates</a> -- yet none have worked.</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>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")
  </code></pre>
  
  <p><strong>My Goal</strong></p>
  
  <p>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. </p>
  
  <p>Any help getting SUM and COUNT pivot values in a single query table would be greatly appreciated!</p>
  
  <p><strong>Example Data</strong></p>
  
  <p>CrossTab "Amount" Query Result</p>
  
  <pre><code>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  
  </code></pre>
  
  <p>CrossTab "Quantity" Query Result</p>
  
  <pre><code>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        
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>My SQL</strong></p>
  
  <p>CrossTab "Amt" Query</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>CrossTab "Qty" Query</p>
  
  <pre><code>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;
  </code></pre>