# Thread: Count occurrances of certain value (Access 2000)

1. ## Count occurrances of certain value (Access 2000)

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.

Jeremy

2. ## Re: Count occurrances of certain value (Access 2000)

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:

<table border=1><td align=right>37</td><td align=right>1</td><td align=right>37</td><td align=right>5</td><td align=right>37</td><td align=right>17</td></table>
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")

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•