[AccessD] Counting multiple instances of a specific string

Jeff,

    Based upon comma separated string values in field named Clocks, you wish to get the count of occurrence of each distinct element of such strings in the whole table. Let the table name be T_Clocks.

    Sample query named Q_TotCountPerClock as given below, should get you the desired count per clock, irrespective of varying number or nature of characters in different elements. It also takes care of any unwanted extra spaces within the comma separated strings.

    The above query makes use of query Q_SplitToMultiRowsPerClock (also given below), which converts each element in Clocks field into a separate row. For this, Cartesian join between tables T_Clocks and T_Ref is used. T_Ref has a single field named RefNum (number type), populated with sequential numbers from 0 to 20. If the number of elements in field Clocks in any single record is likely to exceed 21,  you can expand the contents of T_Ref suitably.

    The above query uses two functions, namely Fn_Element() and Fn_ElementCount() as given below. These functions are to be placed in a general module.

Best wishes,
A.D. Tejpal
------------

Q_TotCountPerClock
====================================
SELECT Q_SplitToMultiRowsPerClock.Clock, Count(Q_SplitToMultiRowsPerClock.Clock) AS TotCount  
FROM Q_SplitToMultiRowsPerClock  
GROUP BY Q_SplitToMultiRowsPerClock.Clock;
====================================

Q_SplitToMultiRowsPerClock
====================================
SELECT Fn_Element([Clocks],[RefNum],",") AS Clock  
FROM T_Ref, T_Clocks  
WHERE (((T_Ref.RefNum)<Fn_ElementCount([Clocks],",")));
====================================

' Code in general module
====================================
Function Fn_ElementCount(InputString As Variant, _
                Optional strDelimiter As String = ",") As Long
    Fn_ElementCount = UBound(Split(Nz(InputString, ""), strDelimiter)) + 1
End Function
'------------------------------------------------------

Function Fn_Element(InputString As Variant, _
                Position As Long, _
                Optional strDelimiter As String = ",") As String
    Fn_Element = Trim(Split(Nz(InputString, ""), strDelimiter)(Position))
End Function
====================================

  ----- Original Message ----- 
  From: Jeff Barrows 
  To: accessd 
  Sent: Friday, August 21, 2009 01:25
  Subject: [AccessD] Counting multiple instances of a specific string


  I have a field, clocks, that can contain either three or four character
  employee clock numbers.

  The field can contain numbers (123 or 1234) OR text (ENG).

  The field can contain one clock number or multiple clock numbers (seperated
  by a comma and a space).

  The users claim that it HAS to be this way or they could be typing the same
  data multiple times with the only difference being the clock number.

  Now, here is the problem.

  They (Management) have decided that they need to be able to get a count of
  how many times the individual clock number appears in the table.

  How do I check for multiple entries for a clock number when it could be
  null, one clock number, or many clock numbers in one field?

  Here is a sample of what the data looks like in the field:

  *clocks***

  2214, 2410, 2349, 2373, 2514, 2431

  1537, 2296, 2202, 2212

  2244

  2411

  2415, 2214, 2393

  2431

  2431, 1542, 2514, 1922

  2431, 2214, 2516

  2431, 2510, 2335

  2410, 2202, 1170, 2212, 2431

  2477

  2410

  2514

  2516, 2397, 2422, 2296, ENG

  2522, 2337, 1661, 2250, 1180
  -- 
  Jeff Barrows