Code for importing a field with multiple pieces of data

[kavya]

Hi,

I have a table called "Member Information" which has a field called "List". The relation is a member can belong to many list types.

For eg:
Individual ID list_type
2 Agriculture;Pharmaceutical;Bio

I need it split in this manner
Individual ID list_type
2 Agriculture
2 pharmaceutical
2 Bio

If any one can help me with the code in VB, I would be very grateful.

Basically I am very new to the programming area.

Thanks a lot

 

[dawnd3]

My way is probably the long way around but I will share it incase it helps you. First make sure you have a table with the various list_types and a unique ID for each. Then I would add the list_type ID field to a "junction table" that you will link up later. The junction table will also have the individual id that will link to the individual table. I would do a series of append queries. You are appending to the junction table (because many individuals can have many list types and visa versa)with the individual id and the list_type id where the list_type contains "*agricultural*" and so on. Make sure you put the wildcards* so that it picks up all records that have each list type regardless of the other entries in the field. I hope this helps, I may have missed a step. Let me know if I can explain further. Dawn

 

[kavya]

Hi Dawn,

Thanks for your reply, could you explain further. I am really stuck and brain dead. When I have so much of information in one row separated by semicolon in the list field, how can I first create a table with unique list type capturing the individual ID.
please can you explain me much more,

Thanks a lot and sorry for the trouble

 

[dawnd3]

No problem, I am glad to help. Lets just do this one step at a time. First create 2 more tables. One is a "list type" table, with ListTypeId as a autonumber (and primary key) and ListType as a text field. The other table is going to join your two tables, perhaps called tblMemberListTypes, this table will have the list type ID field and the individual ID field. Both of these fields will be a number field, and together they can both be set as a primary key. Actually, once we get to this point, perhaps you should email me a copy so I can be sure the next steps I am thinking are correct. My email address is dawnd3@yahoo.com

 

[JeffH]

If I understand everything correctly, I think this code might work for you. It's a little crude and it does not take everything into account, so you might have to scrub data before it's run. Basically it assumes a new table named tblListType with 2 fields, IndividualID and ListType. As always, test this first on a copy of your database and all that fun stuff. Let me know if you have questions on the code or anything else...(make sure you have a reference to Microsoft DAO, otherwise this will not work.)

Jeff

'*** Start Code ***
Dim rstSource As Recordset
Dim rstDestination As Recordset
Dim dbCurrent As database
Dim strSQLSource As String
Dim strSQLDestination As String
Dim intStringCounter As Integer 'Counts position in text
Dim strTempFieldHolder As String
Dim intStartValue As Integer
Dim fKeepGoin As Boolean
Dim strValue As String

'INit
fKeepGoin = True
intStartValue = 1
strSQLSource = "SELECT [Individual ID], List_Type FROM [Member Information];"
strSQLDestination = "SELECT IndividualID, ListType FROM tblListType;"
Set dbCurrent = CurrentDb
Set rstDestination = dbCurrent.OpenRecordset(strSQLDestination)
Set rstSource = dbCurrent.OpenRecordset(strSQLSource)

'Action!
While Not rstSource.EOF
strTempFieldHolder = Nz(rstSource!list_type)
intStringCounter = InStr(intStartValue, strTempFieldHolder, ";&quot
While fKeepGoin
If intStringCounter > 1 Then 'More than 1 value
strValue = Left(strTempFieldHolder, intStringCounter - 1)
strTempFieldHolder = Mid(strTempFieldHolder, intStringCounter + 1)
intStringCounter = InStr(intStartValue, strTempFieldHolder, ";&quot
'Update the destination
rstDestination.AddNew
rstDestination!IndividualID = rstSource![Individual ID]
rstDestination!ListType = strValue
rstDestination.Update
Else 'either only 1 value, or the end
If Len(strTempFieldHolder) > 0 Then
rstDestination.AddNew
rstDestination!IndividualID = rstSource![Individual ID]
rstDestination!ListType = strTempFieldHolder
rstDestination.Update
End If
fKeepGoin = False 'Jump out of loop
End If
Wend
'Reinit
inststartvalue = 1
fKeepGoin = True
rstSource.MoveNext
Wend
'Cut!
Set rstDestination = Nothing
Set rstSource = Nothing
Set dbCurrent = Nothing
MsgBox "DONE", vbInformation
End Sub