Results 1 to 4 of 4

Thread: Crosstab Query

  1. #1
    Join Date
    Dec 2004
    Posts
    9

    Question Unanswered: Crosstab Query

    I have another issue with a cross tab query. There are instances were the data within the table have blanks. So, when a query is ran, the records are also blank. When trying to perform calculations on these fields, the blanks are preventing the calculation from performaning. For example, I have 5 fields I'm trying to sum. However, if one of those fields have a blank, the result or answer is a blank. The other fields all could have numbers but they don't compute. What can be done to handles this. I'm not sure if it is at the table level or query level. The data is coming out of a text file and is a very large data dump. There would be no easy way to make the crosstab have zeros instead of blanks where there is no crossover.

    Thanks for any help you can provide!
    Last edited by cequent; 12-08-04 at 13:29.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I'm assuming by blank, you mean Null. In that case you can use the NZ (null to zero function)

    In your query design, in the field called "The Value" it says something like
    SUM(yourvalue)

    change that to
    SUM(NZ(yourvalue))

    That will convert all the null into zeroes.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Dec 2004
    Posts
    9

    Did not work - any other suggestions?

    It does not seem to work with blank / empty cells. I am trying to sum fields where some of the records are either empty, have a zero, pos number or neg number. For some reason, if one record has a blank, the formula computes a blank.
    Attached Thumbnails Attached Thumbnails untitled2.gif  

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I'm having trouble duplicating it.

    I tested it on one of my crosstabs. The NZ function successfully did the conversion on rows in my base table that had a Null instead of a number, but in the columns where no data existed to begin with, I still got blanks, and not zero's. You can fix this with another query, but it doesn't reallys look like that's the problem you are having.

    In your example, if there are no records of inventory of 0000-89-F13 in 10 ATP, then that cell would be blank. However, if you had 1 record showing 3 in inventory, and another that is "empty" for some reason, using the NZ function would drop a 3 in that cell. Without the NZ function, you would get a blank in that cell.

    Can you post the sql statement that you're using?
    Maybe something will jump out when I see that.
    Inspiration Through Fermentation

Posting Permissions

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