1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access07 - Count records based on column text

Discussion in 'Business Applications' started by mstabinsky, Mar 4, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. mstabinsky

    mstabinsky Thread Starter

    Joined:
    Jan 11, 2010
    Messages:
    18
    In an existing report, I need to have a count of records that have a certain text in a particular column. How do I create this command?

    Example: Need a count of records in column B that have the word "yes" or "no". So I need a count of how many yes's and how many no's. I need a count for both.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,805
    Where do you intend putting the Counted Yeses and Nos?
    The reason that I ask is that there are different ways to achieve what you want to do.
    For instance, does the Report have "Grouping"?
    You could do this in the Query that supplies the report with it's data, or a separate Query, you could probably use an Expression in an UnBound Field as in "Totals" or you could use VBA.
    Are you sure that the values are really "Yes" and "No", as Yes/No Fields actually use -1 (or True) for Yes and 0 (or False) for No.
     
  3. mstabinsky

    mstabinsky Thread Starter

    Joined:
    Jan 11, 2010
    Messages:
    18
    The report has grouping, but not by this yes/no column.
    The values are text "yes" or "no", not yes/no checkboxes.
    I will try doing a separate query, but then how do I link the query to the report?
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,805
    You can create a separate Report and add it to the Main report as a Subreport, hence my question about where you wanted the data in the report.
    You can do it within the Groups in the report if you want using Unbound Fields with formulae in place of the Data Source, something like
    iif([fieldname] = "Yes", 1,0)
    in the Yes counting field and
    iif([fieldname] = "No", 1,0)
    in the No counting field
     
  5. mstabinsky

    mstabinsky Thread Starter

    Joined:
    Jan 11, 2010
    Messages:
    18
    I have the counts at the top of the report in the header.
     
  6. mstabinsky

    mstabinsky Thread Starter

    Joined:
    Jan 11, 2010
    Messages:
    18
    I am thoroughly confused! I'll explain in more detail.

    1. In my table I have column that has a combo list(drop down) that has the values "Therapeutic" and "NonTherapeutic" as your choice for each record.

    2. I have an existing report where I want to add a count in the Report Header for "Therapeutic" clients and "NonTherapeutic" clients, based on the values in this particular table column.

    3. How do I get the total count on the top of my report that counts the records in this field matching the "Therapeutic" or "NonTherapeutic" criteria?

    4. I thought I was pretty skilled in Access, but apparently I'm not, so I need a step-by-step instruction on how to get to achieve this goal. Thank you.
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,805
    Create a Query, with the Field holding the words Therapeutic and NonTherapeutic also have the keyfield from the table
    Add Totals to the Query (Main Menu>View>Totals) and leave the Therapeutic/NonTherapeutic field as "Grouped" but change the keyfield to "Count".
    When you run this query you should have 2 rows one with
    Therapeutic and a count
    and
    NonTherapeutic and a count
    If you get this, make a Report form that query, removing any Report and Page Headers and Footers and size it to fit your Current Report's Header where you want the data to appear.
    Now use the Report's SubForm/Report Wizard to add the new report to your Current report's header.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,805
    I have created a simple database to demostrate.
     

    Attached Files:

  9. mstabinsky

    mstabinsky Thread Starter

    Joined:
    Jan 11, 2010
    Messages:
    18
    I was able to do this. Thank you so much for your help and patience.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/907719

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice