Counting the number of fields ticked "Yes"

[Poss]

Hi all

I have a database that lists 1000 schools (rows) and about 100 survey questions (columns). The data type for the cells in the columns is yes/no.

I also have a form that contains a listbox with the school name and another listbox that needs to display the total number of "yes" values that each school has. If a question is ticked it means "yes" if not ticked it means "no"

When i click a command button, i want it to search through my database, go to the school name and look at its record to see how many colums were ticked.

Thanks in advance

 

[Rabbie]

From your description it sounds as if you are using Access as if it was Excel.

It looks as if you are paying the price for not having your data normalised. Access tables short be long and thin not short and wide. I suspect that you only have 1 table in your DB. From what you have said you should have at least 3 - A school table , a Question table and a Answer Table. The answer table will have a record for each question answered by each school. This will make it very simple to calculate how many "Yes answers a school has.

 

[dreamdelerium]

use count sql

use the sql count function and run the sql statement with docmd.runsql

ex:

...
dim MySQLStr as string

MySqlStr = "SELECT Count(QuestionYourCounting) AS Quantity " _
& " FROM SchoolTable where School= '" & Forms("FormName").controls("ComboboxName").text & "' and QuestionYourCounting = 'yes' "

docmd.runsql MySqlStr

you ll have to edit the sql string to fit your table

 

[boblarson]

use the sql count function and run the sql statement with docmd.runsql

ex:


you ll have to edit the sql string to fit your table

Dreamdelerium:

You can't use docmd.runsql MySqlStr

as DoCmd.RunSQL ONLY works with Action queries (Append, Delete, Make Table, etc.) not with select queries.