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!

I don't understand what your examples are trying to do.

Please explain in more detail what you are trying to count().

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.

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.

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.

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.

The IIF using has three parts.

The first part needs to evaluate to true or false.

What is the result of this calculation?
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?

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!!

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:
For counting in several columns this becomes:
You could also merge the different column using a UNION query, then use a COUNT and a condition on the resulting data set:
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.

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?

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!

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

Is 'Value' a valid option?


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


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

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.

For the following:

This only counts the number of record where ALL three fields ARE EQUAL to YES.

Is that what you want?

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!!

I have been tryhing to figure out wehat you want.

Previously you stated
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?