[AccessD] Crosstab question

Steve,

    The solution suggested in my earlier post was meant for normalized data source. However, it appears from your post that you might wish to use de-normalized data source, having three separate columns holding different types of results for each month in question, there being one record per month. This would require transporting the contents of each such column as distinct value columns in the crosstab query, leading to 36 sub-columns for twelve months.

    Conventional formulation of crosstab query would permit only one value column per month. However, this limitation can be overcome by using a Cartesian join with table T_Ref having a single field named RefNum (number type), populated with numbers 1 to 3, thereby getting three value columns per month.

    Sample crosstab query demonstrating this approach, is given below. T_MyTable is the source table having fields RYear, RMonth, Result_1, Result_2 and Result_3.

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

Sample crosstab query - De-normalized data source
(Transporting three columns of source table as value columns)
=========================================
TRANSFORM First(IIf([RefNum]=1,[Result_1],IIf([RefNum]=2,[Result_2],[Result_3]))) AS Expr1  
SELECT T_MyTable.RYear  
FROM T_MyTable, T_Ref  
GROUP BY T_MyTable.RYear  
PIVOT "M_" & Format([RMonth],"00") & "_" & [RefNum];
=========================================

  ----- Original Message ----- 
  From: A.D.Tejpal 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, July 23, 2009 11:10
  Subject: Re: [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