i have a table it has 41 fields some for them are numbered 1 to 36 in each of these fields there is a number from 1 to 6 i want to count the number of 6s in all the fields for each record. in a form or query.
Yup, then it is a repeating groups problem. This is a one-to-many relationship and there should be two tables, with the round number as values in a single column, not 41 columns in one table. Refer to the link for more information about proper database design.
I would redesign your table. If you are not prepared to do this then learn to love Ctrl + C and Ctrl + V.
SELECT SUM(iif(col1 = 6, 1, 0) + iif(col2 = 6, 1, 0) +......) AS SumOfMyDenormalsedDatabase
Note that not spending time getting your design right in the first place will mean you will have to write more and more hacky code like this to fulfil the simplest of requirements.