How to count multiple text boxes in a Report - Page 2

Brian62 · #16 (**permalink**) 11-15-10, 16:16

This is in the table. I need to total all the yeses, No's and N/A's for all the columns. But right now I am trying to get the yeses to add up first then work on the rest.

Doc #2 Doc #25 Doc #36
Yes Yes Yes
Yes No Yes
Yes Yes No
Yes N/A Yes
No Yes Yes
Yes Yes No
Yes Yes Yes
Yes Yes Yes
Yes Yes Yes
Yes Yes Yes

I hope this answeres your question.

HiTechCoach · #17 (**permalink**) 11-15-10, 16:19

Quote:

Originally Posted by Brian62

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

So you want a grand total of all the YES's.

This can be done by getting the total number of YES' in a record and then summing this total.

If the dataabse was properly design (normalized) this would be a very simply summing query.

Bascially you will need to count the YES's for each field (column) separately then add the individual totals together.

HiTechCoach · #18 (**permalink**) 11-15-10, 16:26

Quote:

Originally Posted by Brian62

This is in the table. I need to total all the yeses, No's and N/A's for all the columns. But right now I am trying to get the yeses to add up first then work on the rest.

Doc #2 Doc #25 Doc #36
Yes Yes Yes
Yes No Yes
Yes Yes No
Yes N/A Yes
No Yes Yes
Yes Yes No
Yes Yes Yes
Yes Yes Yes
Yes Yes Yes
Yes Yes Yes

I hope this answeres your question.

I did already understand that this was your table design. I just did not understand what it was you wanted to count.

Unfortunately with a table design that has repeating fields (not properly normalized ) will not be easy.

Because of so many repeating fields you can NOT get a single query to get what you want.

Brian62 · #19 (**permalink**) 11-15-10, 16:32

How would I go about and add each column to show total in one cell.

When I try this I get a total of the 9 cells with all the yeses.

SELECT Sum(RDStudyAuditSectionA.[Doc #2]) AS Total
FROM RDStudyAuditSectionA
HAVING (((Sum(RDStudyAuditSectionA.[Doc #2]))="Yes"));

HiTechCoach · #20 (**permalink**) 11-15-10, 16:57

It will not be easy based on your current table design to get the grand total into a single field.

Depending in the number of fields you have you might still be able to use a UNION ALL query to convert your data to a properly normalized design. Then you could use a single query to get your counts.

If there are too many field then you could append the total for each field into a record in a table. You could then use a query to get the grand total counts.

Sinndho · #21 (**permalink**) 11-16-10, 14:58

With your naming conventions (not very good ones, but you were already told that), it would have been (here for counting "Yes"):

Code:

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 = 'Yes';

HiTechCoach · #22 (**permalink**) 11-16-10, 15:42

Quote:

Originally Posted by Sinndho

With your naming conventions (not very good ones, but you were already told that), it would have been (here for counting "Yes"):

Code:

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 = 'Yes';

By Defauklt the UNION revonme dupicate records. I would recomend using UNION ALL. Otherwise you will probably get only one record resulting in a count of 1

I would use this:

Code:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech