[AccessD] Counting multiple instances of a specific string

Kaup, Chester Chester_Kaup at kindermorgan.com
Thu Aug 20 15:30:20 CDT 2009


How about some like this to count the number of commas in each record and then subtract 1 for number of clock numbers?

Len([clock number])-Len(Replace([clock number],",",""))

Of course this will not help if the same clock number appears more than once in a record. 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jeff Barrows
Sent: Thursday, August 20, 2009 2:55 PM
To: accessd
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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list