Counting records

[seamusnboo]

Hi,

I wonder if somebody could help me with this issue. We have a survey database here in our company and we need to count certain records in order to create a report. For example, one of the questions (which is a text field in the database) has a YES or NO answer. I can sort the YES and NOs out with a query, but how can I count how many of the fields contain YES and how many contain NO? And also, how can I generate the percentage from it?

Thanks.

 

[ByteMyzer]

The following query will enumerate the table MyTable, with a text field MyField, which contains either a "YES" or a "NO" per record:

SELECT MyTable.[YES], MyTable.[NO], [YES]/([YES]+[NO]) AS [Yes Percentage], [NO]/([NO]+[YES]) AS [No Percentage]
FROM [TRANSFORM Count(MyTable.MyField) AS CountOfMyField
SELECT '' AS MyType
FROM MyTable
GROUP BY ''
PIVOT MyTable.MyField In ('YES','NO');]. AS MyTable;


So, if MyTable has three records with "YES" in MyField, and two records with "NO", here would be the returned results:

YES | NO | Yes Percentage | No Percentage
-----------------------------------------
  3 |  2 |            0.6 |           0.4