Split and Count text in a field

I have a field as follows:

aa_comp
03, 04, 05
03, 07, 05, 20
03, 06, 07

It is imported from xml (along with other fields), and I am aware of
multiple values in a field is bad normalisation.

I need to count of each text occurrence within the whole field column.
The results would look like:

03: 3
04: 1
05: 2
06: 1
07: 2
20: 1

The possible values range from 01 to 99.
The field could contain 0 values or anything up to 99 values
The delimiter is with a comma.

Please can someone offer some help?
I assume I would use split() and count(), but I am not sure how to go
about
it.

Thanks for any help,
Simon

re: Split and Count text in a field

Normalize

Create a second table. Something like:

CREATE TABLE tblNormalized(
RecordID Autonumber,
NormalizedField Text(255)
);

Normalize your data... copy this into a module and run it...

Option Compare Database

Public Sub NormalizeTable()
Dim rsRead As DAO.Recordset
Dim rsWrite As DAO.Recordset
Dim varValues As Variant
Dim intCounter As Integer

Set rsRead = CurrentDb.OpenRecordset("tblDenormalized",
dbOpenTable, dbOpenForwardOnly)
Set rsWrite = CurrentDb.OpenRecordset("tblNormalized", dbOpenTable)

Do Until rsRead.EOF
varValues = Split(rsRead.Fields("FieldToParse"), ",")
For intCounter = 0 To UBound(varValues)
rsWrite.AddNew
rsWrite.Fields("RecordID") = rsRead.Fields("RecordID")
rsWrite.Fields("NormalizedField") =
Trim$(varValues(intCounter))
rsWrite.Update
Next intCounter
rsRead.MoveNext
Loop

rsRead.Close
rsWrite.Close
Set rsRead = Nothing
Set rsWrite = Nothing
MsgBox "done"
End Sub

then create a query like this:

SELECT tblNormalized.NormalizedField, Count(tblNormalized.RecordID) AS
CountOfRecordID
FROM tblNormalized
GROUP BY tblNormalized.NormalizedField
ORDER BY tblNormalized.NormalizedField;

and that's it.

re: Split and Count text in a field

Thanks for you help.
After running the code, my table looks like:

RecordID NormalizedField
1 03 04 05
2 03 07 20 05
3 03 06 07

Is this correct?
Should it have broken each value out into a new field?

Thanks for the help

Simon


pietlinden@hotmail.com wrote:[color=blue]
> Normalize
>
> Create a second table. Something like:
>
> CREATE TABLE tblNormalized(
> RecordID Autonumber,
> NormalizedField Text(255)
> );
>
> Normalize your data... copy this into a module and run it...
>
> Option Compare Database
>
> Public Sub NormalizeTable()
> Dim rsRead As DAO.Recordset
> Dim rsWrite As DAO.Recordset
> Dim varValues As Variant
> Dim intCounter As Integer
>
> Set rsRead = CurrentDb.OpenRecordset("tblDenormalized",
> dbOpenTable, dbOpenForwardOnly)
> Set rsWrite = CurrentDb.OpenRecordset("tblNormalized", dbOpenTable)
>
> Do Until rsRead.EOF
> varValues = Split(rsRead.Fields("FieldToParse"), ",")
> For intCounter = 0 To UBound(varValues)
> rsWrite.AddNew
> rsWrite.Fields("RecordID") = rsRead.Fields("RecordID")
> rsWrite.Fields("NormalizedField") =
> Trim$(varValues(intCounter))
> rsWrite.Update
> Next intCounter
> rsRead.MoveNext
> Loop
>
> rsRead.Close
> rsWrite.Close
> Set rsRead = Nothing
> Set rsWrite = Nothing
> MsgBox "done"
> End Sub
>
> then create a query like this:
>
> SELECT tblNormalized.NormalizedField, Count(tblNormalized.RecordID) AS
> CountOfRecordID
> FROM tblNormalized
> GROUP BY tblNormalized.NormalizedField
> ORDER BY tblNormalized.NormalizedField;
>
> and that's it.[/color]

re: Split and Count text in a field

Simon wrote:[color=blue]
> Thanks for you help.
> After running the code, my table looks like:
>
> RecordID NormalizedField
> 1 03 04 05
> 2 03 07 20 05
> 3 03 06 07
>
> Is this correct?
> Should it have broken each value out into a new field?
>
> Thanks for the help
>
> Simon[/color]

it should write the normalized child records to "tblNormalized"
Then you can do summaries on that table.