[AccessD] CrossTab

Martin,

    While making a crosstab query, automatic creation of a column with heading  <>  signifies that it contains a mixture of nameless  columns corresponding to Null values in the field designated to provide the column headings.

    As a simple illustration, let table T_Test have three fields with values as follows -
    F_1 (Text)        F_2(Text)        F_3(Number)
        A                        L                        3
        A                                                   4
        B                                                   6
        B                        R                        2
        C                        L                        5
        C                        R                        7

    If a crosstab query is created in a routine manner, F_1(group by) as row, F_2(group by) as column and F_3(Sum) as value, it will look like -
    F_1                <>                L                R
        A                  4                 3                
        B                  6                                    2
        C                                     5                 7

    Normally, this can be left as it is - while taking care to interpret the column headed <> as follows -
    Item A has 4 numbers where field F_2 has Null value.
    Item B has 6 numbers where field F_2 has Null value.

    However, if due to some reason, you are very keen to have more descriptive headings (in lieu of  <>) without interfering with basic data, calculated field F_2M can be used instead of  field F_2 -
    F_2M: IIf(Len([F_2])>0, [F_2], [F_1] & "_NULL")

    The crosstab query will now give the following display -
        F_1        A_NULL        B_NULL        L               R 
        A                  4                                        3                
        B                                          6                                   2
        C                                                             5                7

    It will be nice if you could kindly try it out and let me know.

Regards,
A.D.Tejpal
--------------
  ----- Original Message ----- 
  From: Porter, Mark 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, October 08, 2003 23:38
  Subject: RE: [AccessD] CrossTab 


  You can also investigate the 'Switch' command, switching a <NULL> value for
  an actual text string like "Null".

  > -----Original Message-----
  > From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
  > Sent: Wednesday, October 08, 2003 7:56 AM
  > To: Access Developers discussion and problem solving
  > Subject: RE: [AccessD] CrossTab 
  > 
  > 
  > Is the crosstab using the values as the column headings?  If so, you
  > could use a calculated field to return an IsNull expression.
  > 
  > Charlotte Foust
  > 
  > -----Original Message-----
  > From: Martin Reid [mailto:mwp.reid at qub.ac.uk] 
  > Sent: Wednesday, October 08, 2003 7:47 AM
  > To: Access Developers discussion and problem solving
  > Subject: Re: [AccessD] CrossTab 
  > 
  > 
  > I need to keep the nulls as they are valid responses.
  > 
  > Martin
  > 
  > 
  > ----- Original Message ----- 
  > From: "Susan Harkins" <ssharkins at bellsouth.net>
  > To: "Access Developers discussion and problem solving"
  > <accessd at databaseadvisors.com>
  > Sent: Wednesday, October 08, 2003 4:38 PM
  > Subject: Re: [AccessD] CrossTab
  > 
  > 
  > > Can't you handle the Null in the underlying query?
  > >
  > > Susan H.
  > >
  > >
  > > > Running a major cross tab report
  > > >
  > > > A lot of the values are null
  > > >
  > > > Problem
  > > >
  > > > The Crosstab creats a new column called <> (I dont create this)
  > > >
  > > > when it comes across a null row it assigns it a value of 1
  > > >
  > > > how can I get it to stop creating the 1 value as it is 
  > tossing out 
  > > > my calculations.
  > > >
  > > >
  > > >
  > > > Martin

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20031010/94d1c218/attachment-0001.html>