Counting across a row

[Patrick1977]

Hello,

If I have a table that looks like this:

Row#	F1	F2	F3
1	X	X	X
2	X
3		X	X

How can I count the number of Xs across each row, so that I get

Row#	Count
1	3
2	1
3	2

[ranman256]

1. bad design of a table

2. you can get around it in a query ,

if numeric fld"
Total: [f1]+[f2]+[f3]

or not numeric:
select ROW#, Total: iif(IsNull([f1]),0,1)+ iif(IsNull([f2]),0,1))+ iif(IsNull([f3]),0,1)) from table

[Perceptus]

Not an access answer. but I would Copy the data to Excel and transpose rows for columns. then you could perform a aggregate count function.

other wise you could

Public Function countoccurences(ByVal rs As Recordset)
Dim f As Field, i%: i% = 0
For Each f In rs.Fields
    If f.Value = "X" Then i% = i% + 1
Next f
End Function

or similar

[Patrick1977]

Thank you for your reply.
I don't deny that is isn't a great table design; I am just trying to get the information that I need from it.

I hoped to avoid the nested IIF() statements as the real table has > 100 fields.

1. bad design of a table

2. you can get around it in a query ,

if numeric fld"
Total: [f1]+[f2]+[f3]

or not numeric:
select ROW#, Total: iif(IsNull([f1]),0,1)+ iif(IsNull([f2]),0,1))+ iif(IsNull([f3]),0,1)) from table

[Patrick1977]

Thanks for the idea.
I may try to transpose by modifying the function found here:
https://www.fmsinc.com/microsoftacce...transpose.html
and then count rows as normal.

Not an access answer. but I would Copy the data to Excel and transpose rows for columns. then you could perform a aggregate count function.

other wise you could

Public Function countoccurences(ByVal rs As Recordset)
Dim f As Field, i%: i% = 0
For Each f In rs.Fields
    If f.Value = "X" Then i% = i% + 1
Next f
End Function

or similar