Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    16

    CrossTab Query - Blank Values

    When I create my Crosstab query, the default is that any field without the row/column combination shows up as a blank value. Is it possible to have those fields show up as zero?

    Thanks.

  2. #2
    Guus2005's Avatar
    Guus2005 is offline Carbon based thingy
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Location
    the netherlands
    Posts
    42
    You have to handle those values before you turn it into a crosstab query. Usually you do this with the Nz function.

    HTH

  3. #3
    Join Date
    Aug 2006
    Posts
    16
    I understand how the NZ function would work if I had blank values in my first table, but I'm not sure how it will work with the crosstab.

    For example, if this is my table:
    Section Product Sales
    East Footballs 10
    East Baseballs 6
    West Footballs 5
    South Baseballs 4

    My crosstab query returns this:
    Product East South West
    Baseballs 6 4
    Footballs 10 5

    The intersection of South and Footballs is null because that combination didn't exist in the table. Without seeding the table with all possible section/product combinations, how can I get that to appear as zero on my crosstab query?

    Hope this makes my question more clear. Thanks again for your help.

  4. #4
    Join Date
    Aug 2006
    Posts
    16
    Sorry, it doesn't appear my texted displayed well enough to explain my thoughts. I've enclosed an attachment to make it more clear.
    Attached Files Attached Files

  5. #5
    Guus2005's Avatar
    Guus2005 is offline Carbon based thingy
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Location
    the netherlands
    Posts
    42
    Without seeding the table you can fake that with a query using empty values which you can UNION with your query before you turn it into a crosstab query.

    HTH

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Crosstab Query Values Export as Text
    By Paul H in forum Queries
    Replies: 0
    Last Post: 01-07-2013, 11:18 AM
  2. count blank colums in crosstab query
    By survivo01 in forum Queries
    Replies: 6
    Last Post: 04-13-2012, 01:37 PM
  3. Replies: 1
    Last Post: 12-15-2011, 04:44 AM
  4. Displaying multiple text values in crosstab query
    By MFlood7356 in forum Queries
    Replies: 6
    Last Post: 06-28-2011, 11:30 AM
  5. How to sum values in Crosstab query?
    By Buakaw in forum Queries
    Replies: 3
    Last Post: 03-22-2011, 01:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums