Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count No Data (2003)

    Hi,

    I have a query to count how many data of each cetria. There's no data at all for some cetria sometimes. So is any way I can shows 0 in the query if no data match one cetria.

    For example, I would like to see below if I run the query:

    Ceteria Count
    Yes 5
    No 4
    N/A 0

    Thanks

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count No Data (2003)

    You need to have a table or query that lists the unique values (in your example, "Yes", "No" and "N/A").
    Create a new query in design view.
    Add this table or query, and also the totals query that you created.
    Join the two on the appropriate field (the field with values such as "Yes" and "No").
    Double-click the join line.
    Select the option to include ALL records from the first table/query, then click OK.
    Add the field that lists the values from the first table/query to the query grid.
    Create a calculated column:

    MyCount: Nz([CountOfSomething],0)

    where CountOfSomething is the name of the count field from your totals query.
    Switch to datasheet view to see the result.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count No Data (2003)

    Thanks, Hans.

    I tried what you suggested, but still doesn't give to me what I want. Maybe I didn't explain correctly. So I attached the sample dBase.

    As you open the qry_CountStatus, I would like to see the result like the below:

    No 2
    Yes 3
    N/A 0

    Thank you so much for all of your help.

    Regards
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count No Data (2003)

    You need TWO queries: the one you originally created, plus another one, based on that query and on tbl_Status, as I tried to explain in my previous reply. See attached version.
    Attached Files Attached Files

Posting Permissions

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