[AccessD] Crosstab question

Steve,

    Cartesian join, the way it has been used in my sample SQL, is a particularly interesting  means of normalizing the source data, without using any code.

    For example, let us say that apart from the primary key, table T_MyTable has 5 columns, namely RYear, RMonth, Result_1, Result_2 and Result_3. For a given year, there are 12 records (one per month), holding three values (Result_1 to 3) per month.

    With a Cartesian join between T_MyTable and T_Ref (a table having single field named RefNum, having just three records populated with numbers 1, 2 and 3), the three value columns (Result_1 to 3) in table T_MyTable get converted to a single value column, thereby transforming 12 demoralized records (one per month) for the given year into 36 normalized ones (three records per month). 

    Each normalized record stands identified by month number (1 to 12 via RMonth) and Result number (1 to 3 via RefNum). For example, M_01_1, M_01_2 and M_01_3 identify the first month, continuing onwards till M_12_1, M_12_2 and M_12_3 for the last month. With this input, it becomes feasible for the crosstab query to display three value columns per month, i.e. overall 36 columns. 

    Hopefully, this would enable you to go ahead with implementation of suggested solution. Please do let me know (with reasonable promptness) - the outcome of your efforts and whether any further help is needed.

    Note:
    (a) In the crosstab query, format function is used while concatenating month numbers in column names so as to ensure display in proper sequence.
    (b) If your source data is already in normalized state, it can straightaway be used in crosstab query (without needing any Cartesian join), as per sample SQL provided in my very first post.

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

  ----- Original Message ----- 
  From: Steve Erbach 
  To: Access Developers discussion and problem solving 
  Sent: Monday, July 27, 2009 04:40
  Subject: Re: [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];
  > =========================================