[AccessD] Crosstab question

A. D.,
I'll have to peruse this a bit.  I confess to a bit of confusion about the
need for the Cartesian join.

Steve Erbach
Neenah, WI

On Thu, Jul 23, 2009 at 8:14 AM, A.D.Tejpal <adtp at airtelmail.in> wrote:

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