splitting a field 1

[EnergyTed]

I have a data field which I need to seperate. Here is an example of the field.

Record 1 5
Record 2 5,5,5,5
Record 3 5,5,10,10,10,11
Record 4 2,2,2,2,2,2,2,2,2,10,10,10,10


I have been trying to achieve this using Left, Mid and Instrs, etc functions in my query, but limited success so far.

My objective is to be able to count the occurrances of the different numbers per record.

Record 1 One 5
Record 2 Four 5
Record 3 Two 5, Three Ten, One 11
etc.

Kind Regards

Ted

 

[Remou]

How about Split?

I think someone may say normalisation yesno?

 

[EnergyTed]

Could you expand on that please.

 

[DrSimon]

Yes Split() was what I was thinking of too. Here's some code that goes a long way towards what you want. It doesn't have any means of coping with bad data, but that shouldn't be a problem.

Function CountNums(Str As String) As String
Dim Ary
Dim CountAry(100) As Integer
Dim OutStr As String

Dim I As Integer
Ary = Split(Str, ",")
X = UBound(Ary)
For I = 0 To UBound(Ary)
    CountAry(Val(Ary(I))) = CountAry(Val(Ary(I))) + 1
Next I
For I = 1 To UBound(CountAry)
    If CountAry(I) > 0 Then OutStr = OutStr & CountAry(I) & "*" & I & ","
Next I

An example of the output is below.

String Count
5 1*5
5,5,5,5 4*5
5,5,10,10,10,11 2*5,3*10,1*11
2,2,2,2,2,2,2,2,2,10,10,10,10 9*2,4*10
5,4,3,5,2,1,5,5,2,3,4 1*1,2*2,2*3,2*4,4*5

 

[DrSimon]

.... but I agree with Remou that it would probably be better to normalise the data as my code just gets round the issue it doesn't really cure the underlying problem.

 

[EnergyTed]

I can only agree that the data should be normalised in the first place. Thanks again.