MS Access Query - count number of values in a comma separated column

0
votes

I want to count the number of occurrences of values in a comma separated field in a MS Access Table. Any suggestions please :

For example

Table

ID | Value

1 | 1,2,3
2 | 1,5,8,9,5
3 | 1,5,8,3

Desired Output

ID | # of value

1 | 3
2 | 5
3 | 4

2
databasems-access
Will need a custom VBA function that can be called in query or textbox. Develop code and when you encounter specific issue post it for analysis. - June7

2 Answers

1
votes

As @June7 correctly said, you need a custom function which can be called in your query.

Place this in a Standard Module:

Public Function CountValues(ByVal commaValues As Variant) As Long
    If Not IsNull(commaValues) Then CountValues = UBound(Split(commaValues, ",")) + 1
End Function

You can now call it in your query:

SELECT ID, CountValues(FieldName) AS [# of value]
FROM YourTableName;

Output:

ID | [# of value]

1 | 3
2 | 5
3 | 4
4 | 0

NULL values default to zero.

1
votes

you can also do this with built in functions, without the overhead of calling a custom function:

select value,  Len([value])-Len(Replace([value],",","")) as [# of value]