Split and Count text in a field

[Simon]

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

[pietlinden]

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.OpenR ecordset("tblDe normalized",
dbOpenTable, dbOpenForwardOn ly)
Set rsWrite = CurrentDb.OpenR ecordset("tblNo rmalized", dbOpenTable)

Do Until rsRead.EOF
varValues = Split(rsRead.Fi elds("FieldToPa rse"), ",")
For intCounter = 0 To UBound(varValue s)
rsWrite.AddNew
rsWrite.Fields( "RecordID") = rsRead.Fields(" RecordID")
rsWrite.Fields( "NormalizedFiel d") =
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.N ormalizedField, Count(tblNormal ized.RecordID) AS
CountOfRecordID
FROM tblNormalized
GROUP BY tblNormalized.N ormalizedField
ORDER BY tblNormalized.N ormalizedField;

and that's it.

[Simon]

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
pi********@hotm ail.com wrote:

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.OpenR ecordset("tblDe normalized",
dbOpenTable, dbOpenForwardOn ly)
Set rsWrite = CurrentDb.OpenR ecordset("tblNo rmalized", dbOpenTable)

Do Until rsRead.EOF
varValues = Split(rsRead.Fi elds("FieldToPa rse"), ",")
For intCounter = 0 To UBound(varValue s)
rsWrite.AddNew
rsWrite.Fields( "RecordID") = rsRead.Fields(" RecordID")
rsWrite.Fields( "NormalizedFiel d") =
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.N ormalizedField, Count(tblNormal ized.RecordID) AS
CountOfRecordID
FROM tblNormalized
GROUP BY tblNormalized.N ormalizedField
ORDER BY tblNormalized.N ormalizedField;

and that's it.

[pietlinden]

Simon wrote:

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

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