SQL help needed to shorten the query to iterate through columns

Question

I have a table of about 200 columns having primary key as the ID and almost 180 of them are y/n fed. But i have to calculate one column “percentage” through calculating total “y” present from overall y/n columns for each ID in the table.

Any help will be appreciated.

I’m bound to use access and I’m searching for a short query to do it in SQL. I can use column names to write the query but that will take lots of time.

Answer 1

Use VBA and DAO to loop the table:

Public Function CheckPercent()

    Const Sql           As String = "Select * From YourTable"
    
    Dim Records         As DAO.Recordset
    Dim Field           As DAO.Field
    
    Dim Total           As Integer
    Dim Selected        As Integer
    
    Set Records = CurrentDb.OpenRecordset(Sql)
    While Not Records.EOF
        ' Calculate.
        Total = 0
        Selected = 0
        For Each Field In Records.Fields
            If Field.Type = dbBoolean Then
                Total = Total + 1
                Selected = Selected + Abs(Field.Value)
            End If
        Next

        ' Print result.
        Debug.Print "Id " & Records(0).Value, "Total " & Total, "Selected " & Selected, Format(Selected / Total, "Percent")

        ' Save the percentage for this record in field PercentSelected 
        ' having the data type Currency.
        Records.Edit
        Records!PercentSelected.Value = Selected / Total
        Records.Update

        ' Move on.
        Records.MoveNext
    Wend
    Records.Close
    
End Function

Output example:

Id 1002       Total 8       Selected 5    62,50%
Id 1003       Total 8       Selected 6    75,00%