Access expression: Counting unique values in multiple columns

Question

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.

Answer 1

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.

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)