Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Location
    Antwerp, Belgium
    Posts
    9

    Question Unanswered: avoid empty cell in crosstab query

    Quick question:
    how do you prevent empty cells in a crosstab query?

    If you have following table:
    Field1 | Field2 | Field3
    ---------------------------------------------------------------
    1 | a | x
    3 | a | y
    2 | b | x
    3 | b | x

    And you perform the following query:
    TRANSFORM Sum(Table1.Field1) AS SumOfField1
    SELECT Table1.Field2
    FROM Table1
    GROUP BY Table1.Field2
    PIVOT Table1.Field3;

    You get the following result:
    Field2 | x | y
    ---------------------------------------------------------------
    a | 1 | 3
    b | 5 |

    ==> an empty cell for Y/b...

    Is there a way to prevent this? I am drawing a graph based on this, and the line stops for these empty cells, instead of indicating 0...

  2. #2
    Join Date
    Apr 2003
    Location
    Antwerp, Belgium
    Posts
    9

    Unhappy Re: avoid empty cell in crosstab query

    found it... sorry for wasting your time...

    TRANSFORM Val(Nz(Sum([Table1.Field1]),0)) AS SumOfField1
    SELECT Table1.Field2
    FROM Table1
    GROUP BY Table1.Field2
    PIVOT Table1.Field3;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •