Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010

    Unanswered: Return 0 for query from table with multiple criteria

    Hi there, I'm having an issue with getting a query to do what I need and haven't found a viable solution yet.

    I have a table with performance results for a number of groups logged in a table by date and type of error. An exampe table:

    Week Ending Group Error Agent
    06/18/10 Group 1 Error 2 Agent A
    06/18/10 Group 1 Error 3 Agent B
    06/18/10 Group 1 Error 3 Agent B
    06/18/10 Group 2 Error 1 Agent A

    and so forth.

    The query is designed to gather a group's total of each type of error for a particular week, to be presented in chart to show trends week by week. The issue is that I need the chart to present a "0" value instead of a null result in the query. I attempted to overcome this issue by using a left join on a table that contains a listing of the four error types, but it did not produce the intended results.

    SELECT [Error Categories].[Category Name], Count([Agent Error Log].Error) AS Total
    FROM [Error Categories] LEFT JOIN [Agent Error Log] ON [Error Categories].[Category Name] = [Agent Error Log].Error
    GROUP BY [Error Categories].[Category Name], [Agent Error Log].[Week Ending], [Agent Error Log].[Group]
    HAVING ((([Agent Error Log].[Week Ending])=#6/18/2010#) AND (([Agent Error Log].[Group])="Group 1"));
    I also tried iif or nz statements to insert a 0 if the value was null, but I was only able to get returns for the records with values. Ideally what I'm looking for based on the above example would be a result of:

    Error Category Total
    Error 1 0
    Error 2 1
    Error 3 2
    Error 4 0

    Any suggestions?

    P.S. To get ahead of the statement, I know it's better practice to not have spaces in my field names. Moving on.

  2. #2
    Join Date
    May 2004
    New York State
    I have the same issue in a project I'm working on.

    The only reliable way I know to do that is to design the underlying table with Default Value in the appropriate fields to be 0. You probably didn't do that.

    What you can do now is to copy the Structure Only to a new temp table, modify the new structure as above, and use an append query to populate the new table from the old one. All non-populated fields that you defaulted to 0 will be 0, not null. Delete the old table and rename the new one. Now your query should work as intended.

    Good luck,


  3. #3
    Join Date
    Jul 2010
    The issue is that this log will be ongoing, maintaining records of all errors in the format shown above, so even if that were a possible solution it doesn't seem practical. But I can't see how having a default value of 0 would cause the query to return a value of 0 when it is counting the occurrences of a particular error by a particular group on a specific date. Even when using a NZ or IIF function to substitute a 0 for a null value, I'm pretty sure those results are being precluded by the selection criteria.

Posting Permissions

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