Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Wilmington, North Carolina, USA
    Thanked 0 Times in 0 Posts

    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.

    Thanks for your help!

    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    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