Access - if a row has a same value, how to count those values

[pooh]

Hello,
I am fairly new at this and I have a question. I wanted know how i can take same values within a row and return a count of those values
for example:
Col1 Col2
1 A,B,B,B,C
2 A,A,B,C

I would like it so that its
Col1 Col2
1 A, 2(B), C
2 2(A), B, C

Here is the code I have used
Public Function FixTable() As Boolean
On Error Resume Next

Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
Dim strColumn1 As String, strColumn2 As String

Set db = CurrentDb()
Call RecreateTables(db)

'Stop
sSQL = "SELECT Column1, Column2 FROM tblOriginal " _
& "ORDER BY Column1, Column2 ASC"
Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
strColumn1 = rst!Column1
strColumn2 = rst!Column2

rst.MoveNext
Do Until rst.EOF
If strColumn1 = rst!Column1 Then
strColumn2 = strColumn2 & ", " & rst!Column2
Else
sSQL = "INSERT INTO tblCopy (Column1, Column2) " _
& "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
db.Execute sSQL
strColumn1 = rst!Column1
strColumn2 = rst!Column2
End If
rst.MoveNext
Loop

' Insert Last Record
sSQL = "INSERT INTO tblCopy (Column1, Column2) " _
& "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
db.Execute sSQL
End If

Set rst = Nothing
Set db = Nothing

DoCmd.OpenForm "frmOutput"

End Function

Private Function RecreateTables(ByRef dbs As DAO.Database)
On Error Resume Next
Dim sSQL As String

' Delete Table, if exists
If DCount("*", "MsysObjects", "[Name]='tblOriginal'") = 1 Then
DoCmd.DeleteObject acTable, "tblOriginal"
End If

sSQL = "CREATE TABLE tblOriginal (Column1 Text(10), Column2 Text(10))"
dbs.Execute sSQL

sSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('A','1')"
dbs.Execute sSQL

sSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('A','2')"
dbs.Execute sSQL

sSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('B','1')"
dbs.Execute sSQL

sSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('B','2')"
dbs.Execute sSQL

sSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('B','3')"
dbs.Execute sSQL

sSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('C','1')"
dbs.Execute sSQL

' Delete Table, if exists
If DCount("*", "MsysObjects", "[Name]='tblCopy'") = 1 Then
DoCmd.DeleteObject acTable, "tblCopy"
End If

' Create Temp Table
sSQL = "SELECT Column1, Column2 INTO tblCopy " _
& "FROM tblOriginal WHERE 1 = 0;"
dbs.Execute sSQL

End Function


PLEASE HELP ME AS I AM FAIRLY NEW AT THIS !!!!
Thanks!

[Rabbit]

Here's the gist.

1) Split the string into a one-dimensional array using the Split() function.
2) Create a two-dimensional array. The first column holds the value, the second column holds the count.
3) Step through array 1 and check it against the values in the second array. Incrementing the count where necessary and creating a new value if it doesn't exist.
4) Rebuild the string.