Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Nov 2010
    Posts
    29

    Unanswered: How to count multiple text boxes in a Report

    have a report where I am trying to count all the text boxes which comprises of about 8o. I want to separate them by Yes, No and N/A. I tried a few different options and none have worked in Control Source.

    Example1: =Count(IIf([RDStudyAuditSectionA.Doc #12-5]+[RDStudyAuditSectionA.Email Corrospondence]+[RDStudyAuditSectionA.Doc #2]="Yes",0))

    Example2=Count(IIf([Doc #12-5]+[Email Corrospondence]+[Doc #2]="Yes",0))

    Example3=Count(IIf([Doc #12-5],[Email Corrospondence],[Doc #2]="Yes",0))

    The formula is set in the report footer. The information is coming from a query with 10 different tables linked to one query which the Report operates off of.

    I have tried it on the query side but I can't figure it out there as well.

    Attached is the DB. Look at the center column. That is the one I am trying to count for each category (Yes, No and N/A).

    Thanks!
    Attached Files Attached Files

  2. #2
    Join Date
    May 2010
    Posts
    601
    I don't understand what your examples are trying to do.

    Please explain in more detail what you are trying to count().
    Last edited by HiTechCoach; 11-12-10 at 12:24.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Nov 2010
    Posts
    29
    I am trying to count the middle column in the report. Each text box is different than the others and need to find a way add all of them. There is approximately 80 different cells. I hope that helps.

  4. #4
    Join Date
    May 2010
    Posts
    601
    Not really. You did not explain want any of the example are attempting to do. The logic in your example does not seam correct or make sense. Without a better understanding of exactly what you are trying to do, I can not offer any suggestions.

    Since we know nothing about your database or what you are doing you need to give enough details that we understand exactly what you are doing.

    It has been my experience in 30+ years of software development that if you can not explain the steps of what you are doing to another human then it will be difficult to translate the steps into code that will give you the desired results.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Nov 2010
    Posts
    29
    The database is attached in an earlier post. I don't know how to explain it better than I already have. If you look at the report then look at the center column (test box) which are all different names coming from a query with about 80 different column names. As the fields populate in the center column of the report then I want the report footer to give me a total Yeses, No's and N/A from all the different text boxes. Once I figure out the Yeses I can change the formula for the other two.

    I hope this helps but I'm not sure if I explained it any better.

  6. #6
    Join Date
    May 2010
    Posts
    601
    Unfortunately I am not at a place where I have a test machine that will provide a safe environment to look at you attachment.

    Note: Nothing personal toward you, but I am very cautious at looking at attachments from unknown sources. I am overly very cautious of emails and posts that are very brief and say just lookmy attachment for details. This is a common way to deloy malicious stuff.


    There seams to be several issues with you examples. I will pick one.

    Code:
    =Count(IIf([Doc #12-5]+[Email Corrospondence]+[Doc #2]="Yes",0))
    The IIF using has three parts.

    The first part needs to evaluate to true or false.

    What is the result of this calculation?
    Code:
    [Doc #12-5] + [Email Corrospondence]+ ([Doc #2]="Yes")
    What is the data type for each of these fields?
    [Doc #12-5]
    [Email Corrospondence]
    [Doc #2]

    I would think that [Doc #2] is a trying that can have the Values "Yes" or "No" or possible some other string. Is this correct?

    Does ([Doc #2]="Yes") evaluate to a true or false correctly?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    Join Date
    Nov 2010
    Posts
    29
    I understand your reluctance.

    Answer to question 1: If 2 out of the three give me 2 yeses than 2 would be the total I am looking for. Currently gives me an #error. How would I rewrite the formula to show true or false for the first part?

    Answer to question 2: The fields are set to text in the table. Yes the data type can be any other string but I only use Yes, No and N/A in a drop down box in a form.

    Is this the information you are looking for?

    Thanks!!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like Boyd, I'm not sure to understand your question clearly.

    If what you try to achieve is counting the number of occurences of a determined value in one or several fields, this can mean counting the same number of occurences in one or several columns in the datasource (table or query) of the report.

    This can be done in SQL:
    Code:
    SELECT COUNT (*) AS Total
    FROM <Data Source>
    WHERE <Column> = '<Value>';
    For counting in several columns this becomes:
    Code:
    SELECT COUNT (*) AS Total
    FROM <<Data Source>
    WHERE (<Column1> = '<Value>') 
         OR (<Column2> = '<Value>') 
         OR ...;
    You could also merge the different column using a UNION query, then use a COUNT and a condition on the resulting data set:
    Code:
    SELECT Count(*) AS Total
    FROM [SELECT <Column1> AS x FROM <Data Source>
    UNION
    SELECT <Column2> AS x FROM <Data Source>
    UNION
    SELECT <Column3> AS x FROM <Data Source>
    UNION
    SELECT <Column4> AS x  FROM <Data Source>]. AS Merged_Alias
    WHERE Merged_Alias.x = '<Value>';
    With 80 columns you would probably quickly reach the maximum level of complexity that Access can process in a SQL expression, so you would have to use several partial queries and sum them together but this can be done.
    Have a nice day!

  9. #9
    Join Date
    May 2010
    Posts
    601
    I did finally get to a test computer where I could look at your attachment.

    The first this that I notices what that the database really is not properly normalized. What you are wanting would be a whole lot easier to do if the tables were properly normalized.

    TIP: You should avoid using object names as data.

    If designed properly you should be able to add or remove new items without modifying the table design. For example: adding a new a Doc # should not require you to modify the design of the table. This would also be true for Audit Sections. You should be able to add a new Audit section without adding a new table.

    As Sinndho suggested, you could use a UNION query to properly normalize your data as a bandage. You might hit some limits so I would recommend running a bunch of queries to append the data into a properly normalized table that you can use as the record source for your report.

    Before attempting this report I would urge you to redesign your tables using the rules of data normalization. Are you allowed to redesign the tables?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  10. #10
    Join Date
    Nov 2010
    Posts
    29
    I took what you gave me and this is what I tried with no luck.

    SELECT Count(*) AS Total
    FROM [SELECT 'Doc #2' AS x FROM <RDStudyAuditSectionA]
    UNION
    SELECT ['Doc #25' AS x FROM <RDStudyAuditSectionA]
    UNION
    SELECT ['Doc #28-5' AS x FROM <RDStudyAuditSectionA]
    UNION
    SELECT ['Doc #30' = AS x FROM <RDStudyAuditSectionA] AS Merged_Alias
    WHERE Merged_Alias.x = '<Value>';

    I also tried this and got a total of 10 when it should be a total of 18 for the yeses.

    SELECT Count(*) AS Total
    FROM RDStudyAuditSectionA
    WHERE ((('Doc #25')='Value')) & ((('Doc #25')=Yes) & (('Doc #2')='Value')) & ((('Doc #2')=Yes));

    Is there something that I am missing?? I tried various scripts but couldn't get anything to work. Thanks!
    Last edited by Brian62; 11-15-10 at 16:09. Reason: More info.

  11. #11
    Join Date
    May 2010
    Posts
    601
    I would highly user you to NOT use spaces or non-alpha or numeric characters.

    For example the field name Doc #25 needs to be wrapped with [ and ] like this [Doc #25]

    Wrapping the field name with apostrophes (') ccreates a string. 'Doc #25' is a literal string NOT a field name.


    So you would need to use something like this:

    You also wooudl not use & between the conditions in the Where. Use And or OR

    Code:
    SELECT Count(*) AS Total
    FROM RDStudyAuditSectionA
    WHERE ((([Doc #25]='Value')) Or ((([Doc #25]='Yes') Or (([Doc #2]='Value'))or ((([Doc #2]='Yes'));
    Is 'Value' a valid option?


    If you wanted it were at least one is YES then I woudl use:

    Code:
    SELECT Count(*) AS Total
    FROM RDStudyAuditSectionA
    WHERE  (([Doc #25]='Yes') Or ([Doc #2]='Yes') );

    If you wanted it were BOTH are YES then I would use:

    Code:
    SELECT Count(*) AS Total
    FROM RDStudyAuditSectionA
    WHERE  (([Doc #25]='Yes') and ([Doc #2]='Yes') );
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  12. #12
    Join Date
    Nov 2010
    Posts
    29
    I tried this code you suggested (#3) with one additional doc # for a test.

    SELECT Count(*) AS Total
    FROM RDStudyAuditSectionA
    WHERE (([Doc #25]='Yes') and ([Doc #2]='Yes') and ([Doc #36]='Yes') );

    The total for yeses in those three should be 25 but it is showing 7. It's not counting correctly. The more columns I add the smaller the number gets.

    Value is not a valid option that I can get to work.

  13. #13
    Join Date
    May 2010
    Posts
    601
    For the following:

    Code:
    SELECT Count(*) AS Total
    FROM RDStudyAuditSectionA
    WHERE (([Doc #25]='Yes') and ([Doc #2]='Yes') and ([Doc #36]='Yes') );
    This only counts the number of record where ALL three fields ARE EQUAL to YES.

    Is that what you want?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  14. #14
    Join Date
    Nov 2010
    Posts
    29
    No. I need to have a total of all the yeses in all the columns. I will be adding more columns once the totals come out correctly.

    Doc #2: has 9 Yes, 1 No
    Doc #25: has 8 Yes, 1 NO, 1 N/A
    Doc #36: Has 8 Yes, 2 No

    Total should be 25. I hope this helps! Thanks!!

  15. #15
    Join Date
    May 2010
    Posts
    601
    I have been tryhing to figure out wehat you want.

    Previously you stated
    Answer to question 1: If 2 out of the three give me 2 yeses than 2 would be the total I am looking for.
    After some pondering on the above, I have a few questiosn for you.

    Are you wanting to total number of Yes's in a sinlge record?

    or

    Are you wanting the number of YES's for a single field in all records?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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