Count occurrances of certain value (Access 2000)

Topic

New Reply

I’m sure its possible to do through an update query, I just don’t know the expression. For a little background, I deal a lot with personnel. When people go places, earn special awards, etc, they are submitted on a list of names. I keep track of the lists they were on in Column A, simply displayed as 1, 2, 4, 7, 17, etc., with each number representing the list number they were on. I want column B to count the number of lists the person was on. I assume this is easily achievable through an update query, counting the number of commas (+1). I don’t know how to write the expression. I would have the update query run upon closing of the table.

Thanks for your help!

Jeremy

Reply | Quote

Replies

The data design is inconvenient. You should use a table with two fields: PersonnelID and ListID (plus extra fields if you want to store comments etc.)
If person 37 is on lists 1, 5 and 17, there would be three records:

37	1
37	5
37	17

You could then use standard Totals queries to count how many lists each person is on, or how many persons are on each list.

If you want to keep the table design as is, you still don’t need an update query. Since the number of lists is derived information, you can calculate it in a select query, there is no need to store it in a table. You can create a custom function in a standard module:

Function CountItems(var As Variant) As Integer
If Not IsNull(var) Then
CountItems = UBound(Split(var, “,”)) + 1
End If
End Function

and use this in your query:

NumberOfLists: CountItems([FieldName])

where FieldName is the name of the field (“Column A”)

Reply | Quote