By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,680 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Split and Count text in a field

P: n/a
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

Jun 15 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.

Jun 15 '06 #2

P: n/a
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.


Jun 16 '06 #3

P: n/a

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.

Jun 16 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.