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.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.

[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********@hotmail.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.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.

[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 "tblNormalized"
Then you can do summaries on that table.