ms-accessexpressionbuilder

Access expression: Counting unique values in multiple columns

How can I use MS Access Expression to count unique values in multiple columns as shown below? I used Countif in Excel to get the "Yes" counts in the status column and now I want to use MS Access expression to get the same results.

Solution

  • use the function to make a row aggregate.

    chek this out

    Public Function count_sum(col1 As String, col2 As String, col3 As String) As Integer


Dim count_yes As Integer

count_yes = 0

If (col1 = "YES") Then
count_yes = count_yes + 1
End If

If (col2 = "YES") Then
count_yes = count_yes + 1
End If

If (col3 = "YES") Then
count_yes = count_yes + 1
End If

count_sum = count_yes

End Function

    call this function using the following query

    SELECT col1,col2,col3, count_sum([col1],[col2],[col3]) as Status
FROM Table1;

    you can also use this fuction in contionous form.

    enter image description here

    In status textbox add control source like this OR directly use the above query and select the control source as status.

    =Nz(count_sum([col1];[col2];[col3]);0)