Parsing a field data and a bit more

[Alp Bekisoglu]

Hi Experts,

I have a field in table "co_main" where the data is entered as
"234,567,545,334,.." etc. All 3 digit numbers and are comma seperated. I
would like to populate a listbox for a report (or also a form) where each
set of triplets are matched to an explanation in a second table. This table
has "sc_code" and "sc_desc" as its fields where the above comma seperated
triplets match the "sc_code" field values.

Thanks in advance for pointing me in the right direction to accomplish the
above.

Alp

 

[Douglas J. Steele]

Excuse me if this seems rude, but you shouldn't have multiple values in a
single field: that violates database normalization rules. Instead, you
should have a second table, linked to your existing table, with each of the
values representing a separate row in the second table.

You might want to take a look at some of the resources related to database
normalization that Jeff Conrad has at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

 

[Ron Weiner]

I absolutely agree with Douglas, but in effort to help yourself dig an even
deeper hole than you are already in, feel free to use the shovel (code)
below to populate your list box.

strSql = "SELECT sc_code, sc_desc FROM SecondTable " & _
"WHERE sc_code In(" & me.ColumnInTableMain & ")" & _
"ORDER BY sc_desc"
me.lstBoxYouWantToPopulate.RowSource = strSql

This code assumes that sc_code column is numeric (Integer or long), and
should probably be placed in the OnCurrent event of the form with the list
box.

 

[Alp Bekisoglu]

No need to worry about what you've said Doug. Ofcourse you are absolutely
correct. But due to the pressure during data entry, they have opted to enter
all this way. Now, since all have been entered (and it IS time to think
properly) it is necessary to parse all that into proper method.
Would it be too much to ask help in parsing that data to a new table?
I know I'm not that good in writing code using For's and While's... but I'll
try anyway.
By the way, once I did find vb code on ParseData but just can't locate it
anymore.
Thanks for reminding me.

Alp

 

[Alp Bekisoglu]

Thanks for the code Ron. And yes I do agree with Doug and am in an effort to
correct it.

Alp

 

[Douglas J. Steele]

Difficult to give a complete answer without knowing your table and field
names, but generically:

Assuming co_main has co_id as its primary key, your second table would be,
say, co_sc. You have two options. If the order in which the sc_code is
linked doesn't matter, simply make the primary key of co_sc an autonumber,
and have a foreign key of co_id to point back to the corresponding row in
co_main. Alternatively, if you do care about the sequence, make the primary
key of co_sc the combination of co_id and sc_seq_number.

One approach to populating co_sc would be to create a routine that you can
pass co_id and your multi-valued field (let's call it sc_multi)

Something like the following untested air-code should work (note that I'm
using DAO. If you're using Access 2000 or 2002, you'll have to add a
reference to the Microsoft DAO 3.6 Object Library if you haven't already
done so):

Sub PopulateNewTable ( _
Id As Long, _
MultiValueField As String _
)

Dim intLoop As Integer
Dim strSQL As String
Dim varValues As Variant

varValues = Split(MultiValueField, ",")
If IsNull(varValues) = False Then
For intLoop = LBound(varValues) To UBound(varValues)
' This assumes you care about the sequence:
strSQL = "INSERT INTO co_sc " & _
"(co_id, sc_seq_number, sc_code) " & _
"VALUES (" & Id & ", " & intLoop & ", " & _
varValues(intLoop) & ")"
' Comment the previous statement and uncomment this
' if you don't care about the sequence:
' strSQL = "INSERT INTO co_sc " & _
' "(co_id, sc_code) " & _
' "VALUES (" & Id & ", " & _
' varValues(intLoop) & ")"
CurrentDb.Execute strSQL, dbFailOnError
Next intLoop
End If

End Sub


You'd use the routine above like:


Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT co_id, sc_multi FROM co_main"

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do While rsCurr.EOF = False
Call PopulateNewTable(rsCurr!co_id, rsCurr!sc_multi)
Loop

 

[Alp Bekisoglu]

Hi Doug,

Thanks for the patience. As I should have provided earlier; I'm using A2K.
Table info:
Table: co_main Fields: co_id(autonum, PK), co_urunler(text)
I have already created a new table to receive the parsed data as:
Table: prods Fields: prod_id(autonum, PK), prod_co_id(number - to store
co_id), prod_subcat_code(number - to receive parsed the triple digits)
I also located a code which gives me the correct count of triplets:
Function CountCSWords(ByVal s) As Integer
'Counts the words in a string that are separated by commas.
Dim WC As Integer, Pos As Integer
If VarType(s) <> 8 Or Len(s) = 0 Then
CountCSWords = 0
Exit Function
End If
WC = 1
Pos = InStr(s, ",")
Do While Pos > 0
WC = WC + 1
Pos = InStr(Pos + 1, s, ",")
Loop
CountCSWords = WC
End Function

It also has a second part which yields the nth in the string but I'm
fighting to get it to work properly.

I'll try your suggestions first then.

Thanks again.

Alp

 

[Douglas J. Steele]

The Split function is much cleaner than your CountCSWords function.

Function CountCSWords(ByVal s) As Integer
'Counts the words in a string that are separated by commas.
Dim varWords As Variant

varWords = Split(s, ",")
If IsNull(varWords) = True Then
CountCSWords = 0
Else
CountCSWords = UBound(varWords) + 1
End If

End Function

The nth substring would be

Split(s, ",")(n-1)

 

[Alp Bekisoglu]

I tried your suggested code but ended up with only the very first data
(which was 362) stored in all companies which kept on going... I cut it off
at 119054th record. I don't know where it went haywire.

Below is the modified version. I might have missed something.

Alp

Sub PopulateNewTable( _
Id As Long, _
MultiValueField As String _
)

Dim intLoop As Integer
Dim strSQL As String
Dim varValues As Variant

varValues = Split(MultiValueField, ",")
If IsNull(varValues) = False Then
For intLoop = LBound(varValues) To UBound(varValues)
' This assumes you care about the sequence:
' strSQL = "INSERT INTO prods " & _
' "(prod_co_id, sc_seq_number, prod_subcat_code) " & _
' "VALUES (" & Id & ", " & intLoop & ", " & _
' varValues(intLoop) & ")"
' Comment the previous statement and uncomment this
' if you don't care about the sequence:
strSQL = "INSERT INTO prods " & _
"(prod_co_id, prod_subcat_code) " & _
"VALUES (" & Id & ", " & _
varValues(intLoop) & ")"
CurrentDb.Execute strSQL, dbFailOnError
Next intLoop
End If

End Sub


Public Function UrunNaklet()
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT co_id, co_urunler FROM co_main"

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do While rsCurr.EOF = False
Call PopulateNewTable(rsCurr!co_id, rsCurr!co_urunler)
Loop

End Function

 

[Alp Bekisoglu]

Doug I found out that PopulateNewTable does not go beyond the first company
and goes into an infinite loop.
The very first record has only one entry; 362. Thus the co_id is always
entered as 1 and the parsed field value is always 362.

Alp

 

[Alp Bekisoglu]

I finally found what was wrong; I added a rsCurr.MoveNext before the Loop
and it works. But! Unfortunately I found out that there are some records
without any entry! So now the code comes to a halt when it encounters a Null
entry.

If I can get around that, then all will be solved.

I hope your patience is still holding.

Thanks,

Alp

 

[Douglas J. Steele]

Sorry about that. (I _did_ warn you it was untested air code! <g>)

 

[Alp Bekisoglu]

I know. Well, you made me use my brains again. for which I thank you.

Thank you for your help.

Alp