[AccessD] Crosstab question

Steve,

    Apparently, you wish to display 3 columns per month for the twelve months on the report. This translates to 36 columns in all.

    You can consider a simple crosstab query based upon your normal source table,  delivering 36 columns (3 columns per month). On the report, labels depicting month names can be placed in such a manner that each such label spans three columns pertaining to the given month.

    Sample crosstab query given below, demonstrates this approach. T_Data is the source table having fields RYear, RMonth, Category and Result (all number type). For each month in a given year, there will be three records pertaining to the three categories respectively (i.e. 1, 2, 3)

Best wishes,
A.D. Tejpal
------------

Sample crosstab query
=========================================
TRANSFORM First(T_Data.Result) AS FirstOfResult
SELECT T_Data.RYear
FROM T_Data
GROUP BY T_Data.RYear
PIVOT "M_" & Format([RMonth],"00") & "_" & [Category];
=========================================

  ----- Original Message ----- 
  From: Steve Erbach 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, July 23, 2009 01:48
  Subject: [AccessD] Crosstab question


  Dear Group,

  I need to create a report in the form of a crosstab with 12 monthly
  columns.  The Crosstab query capability only allows one numeric value to be
  summarized per column...but the client would like to see three values
  underneath each month's heading.

  I thought that I'd construct a table for use by the report with
  de-normalized columns in clusters of three for Month1Quantity,
  Month1Calculation1, Month1Calculation2...Month2Quantity, Month2Calculation1,
  Month2Calculation2, etc.

  I can make a crosstab query easily enough to, say, show the Quantity for
  each month...but the SQL TRANSFORM statement will not allow me to create a
  table directly with a SELECT INTO clause.  I've made two other crosstabs
  containing the Calculation1 and Calculation2 results by month and I want to
  combine them all into one table for this "triple-value" crosstab report.

  I can, of course, create an empty table with the structure I want; then
  write a function or sub procedure that runs each of the three TRANSFORM
  queries and loops through the recordset and writes the values into rows in
  the all-in-one table.  That's the approach I've taken so far.

  If I were using Microsoft SQL Server the queries would be more flexible,
  since I can use a subquery in a SQL FROM clause, which I can't do in
  Access.  I'm just curious about the methodology in Access.  My client needs
  rolling 12-month reports with the values broken out like a spreadsheet.
  That means a CROSSTAB query...but I've run up against the limitations of
  Crosstab queries and I'm trying to work around them.

  So, am I missing an easy way to convert the results of a Crosstab query to a
  table?

  Regards,

  Steve Erbach
  Neenah, WI