[AccessD] CrossTab

Great

Learn something on this list every day

Will try this in the morning and let you know how it goes

Martin
  ----- Original Message ----- 
  From: A.D.Tejpal 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, October 09, 2003 11:51 AM
  Subject: Re: [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



------------------------------------------------------------------------------


  _______________________________________________
  AccessD mailing list
  AccessD at databaseadvisors.com
  http://databaseadvisors.com/mailman/listinfo/accessd
  Website: http://www.databaseadvisors.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20031009/e20e3188/attachment-0001.html>