Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2006
    Posts
    39

    Unanswered: Keeping queried columns with no results

    Hello All,

    In one of my queries, if there are no results the columns vanish. I read something about nz function and Value if Null, but cant's seem to get it to work right. All I am trying to do ensure that regardless of results, ALL columns stay in the query when ran. ( see screen print)

    Can anyone show me what to put in my criteria to make this happen.

    Thanks a million,

    B
    Attached Thumbnails Attached Thumbnails Aaaa.PNG  

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    In order to do this, my approach has always been to create a dummy table with one record with zeroes in all the columns. Use a UNION ALL query to add the record to your live data and then do the cross tab. Then if you need to, filter out the dummy data.

    The Nz function will give you zeroes for all your nulls so you can sum the data.

  3. #3
    Join Date
    Dec 2006
    Posts
    39

    Thanks!

    I'll give it a try.
    I never would have thought of that, but it does sound like it would work.

    Thanks.

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Will you always have the same columns in the crosstab query, unless one drops out? If so, another way to be sure a column does not drop out would be to add the column names into the Column Headings property of the crosstab query. with the query open in design view, right click anywhere in the upper portion of the query, but not on a table, then select properties. The third property down is the Column Headings. In your case you would add "In Progress", "Resarching", "Left Message", etc. Then, even if one of those columns does not contain any data, the column will be in the query output.

  5. #5
    Join Date
    Dec 2006
    Posts
    39

    Reply

    GolferGuy,

    Yes, the column headings will remain the same. I will try this method instead - it seems to be a cleaner solution.

    Thanks.

  6. #6
    Join Date
    Dec 2006
    Posts
    39

    Thanks!

    GolferGuy,

    Wow! It worked and was really simple. I followed your instructions and got the right results.

    Cheers!

Posting Permissions

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