count

[orajat]

hi,

i want to count the number of 1's in the columns of each row in 'total' field.

attached is the way my table is.

thanx

[Stewart Ross]

Orajat, it helps us to help you if you could provide the necessary information in your post itself, not in an unexplained attachment, an attachment which provides no more detail than simply pasting your table contents into your post and using the code tags to delimit them would have done.

Your table is either some form of pivot table or it is non-normalised data. Either way, if your question relates to Microsoft Access there is no inbuilt SQL function which can count or sum across the fields automatically where the field is equal to 1. You will need to sum the fields concerned in a query like this:

OneCount: IIF(Nov-1 = 1, 1, 0)  + IIF(Nov-2 = 1, 1, 0) + IIF(Nov-3 = 1, 1, 0) + IIF(Nov-4 = 1, 1, 0)

Once you have this computed field you can then sum its value in another query.

If your data is not the result of a pivot table it is clearly not normalised and I would strongly advise you to resolve this before you develop matters further.

-Stewart

[orajat]

stewart...this just works fine for me...thnx