Count

[Gismo]

Hi All,

Please could you assist?
I have a table with 8 columns and multiple rows
Data only consists of 1's and 0's
How do I calculate the number of 1's per row instead of by column?

 

[Minty]

Badly stored data alert...
This is a typical problem associated with a spreadsheet style storage method. As you have now discovered it doesn't scale well and makes doing apparently easy things very difficult.

You'll have to literally add them up manually - PainfullResult: Field1 + Field2 + Field3 etc etc.

When you add column 9 you'll have to hardcode that back into your query, and if you have null values anywhere you have to encompass them to continue the sums to work.

 

[Mark_]

Just to make sure, these are "0" or "1", NOT True/False?

 

[llkhoutx]

Use the NZ function to conver Nulls to 0. if any.

 

[MajP]

Public Function AddColumns(ParamArray Columns() As Variant)
  Dim i As Integer
  For i = 0 To UBound(Columns)
    AddColumns = AddColumns + Nz(Columns(i),0)
  Next i
End Function

select AddColumns([field1],[field2].....[fieldN]) as Total from sometable