[b]Convert multiple value field in to columns[/b] 2

[yog2go]

Hello Everyone,

I have a table with a list of Business Locations
(BL) and for each BL, there may be multiple Account IDs (AI). For each unique BL, there is a row for AIs. It looks something like this:

BL AIs
12345 135790, 124680, 567321
54321 975315, 456123

What I need is a table that looks like this:

BL AI
12345 135790
12345 124680
12345 567321
54321 975315
54321 456123

How do I do this? Can you show me the step by step procedure? I am totally new to database stuff.

Thanks in advance.

 

[willir]

yog2go

Is this a one-time only conversion routine?
Is the account ID format on the non-normalized business location consistent? Is it always ", " (comma + space)
Is the Account ID always six characters long?

I would use a visual basic program...

Assumptions

OLD_tblBusinessLoc - original table
NEW_tblBusinessLoc - original table
BL - field name for business locations, numeric, long interger
AI - field name for AccountID. How this data type is stored will impact the logic in how to parse / strip off the account ID.
Your new table does not have an apparent primary key. Not important for this excercise, but something to consider.
I am going to use DAO for the read and write. You can also use ADO for the read/write or read-only and use SQL for the write.
I am going to go through the entire "old" table.

[COLOR=blue]Private Sub WalkBizLocations() [/color]
Dim dbs as DAO.Database, rstOld as DAO.RecordSet
Dim strAILong as String, lngBL as Long
Set dbs = CurrentDB()

set rstOld = dbs.Openrecordset("OLD_tblBusinessLoc")

'Very simple walk through the table
With rstOld
    .MoveFirst
    Do While Not .EOF
         WriteNewLocation !BL, !AI
         .MoveNext
    Loop
End With

rstOld.Close
dbs.Close
Set rstOld = Nothing
Set dbs = Nothing

End Sub


[COLOR=blue]
Private Sub WriteNewLocation(lngBL As Long, strAILong As String)[/color]
Dim dbs As DAO.Database, rstNew As DAO.Recordset
Dim strAI As String, strSearch As String, intX As Integer

Set dbs = CurrentDb()
Set rstNew = dbs.OpenRecordset("NEW_tblBusinessLoc")

'Logic how the accounts are parsed or stripped off
'will be impacted by the AI string
strSearch = ", "

Do While Len(Nz(strAILong, ""))

    intX = InStr(1, strAILong, strSearch)
    
    If intX > 0 Then
        strAI = Left(strAILong, intX - 1)
        strAILong = Right(strAILong, Len(strAILong) - intX - 1)
    Else
        strAI = strAILong
        strAILong = ""
    End If
    
    With rstNew
        .AddNew
        !bl = lngBL
        !ai = strAI
        .Update
    End With

Loop

rstNew.Close
dbs.Close
Set rstNew = Nothing
Set dbs = Nothing

End Sub

I broke the code into two subroutines - more flexible in that you can call the WriteNewLocation with just one record, or as in this case, while "walking through" a record set.

If the AI account was always six characters, the above Do While loop could be replaced with a more effecient For / Next loop.

I placed a ", " as the search string -- I am assuming your sample data is correct.

Richard

 

[yog2go]

Hello Richard,
Sorry for late reply. I was on travel.
Thanks a lot for your response.
Yes it is only one time conversion routine.
Sorry AI field value doesn't have space after ",".
All field value are defined as text data type and they vary in length.

Suppose i am adding another field name CodeNo with values given below.

BL AIs CodeNo
12345 135790,124680,567321 IDP,ADP,XZP
54321 975315,456123 IER,REF

I want the table as given below. How your code is going to change? I have access database on my C drive.

BL AI CodeNo
12345 135790 IDP
12345 124680 ADP
12345 567321 XZP
54321 975315 IER
54321 456123 REF

Thanks in advance Richard.

 

[PHV]

If ac2k or above, take a look at the Split and UBound functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Well...
"135790,124680,567321"
...is pretty easy to deal with.

I already accommodated unequal lengths in the string.

But the third column will have to wait until later to night.

I forgot to ask how you are going to identify the primary key for this table?

 

[yog2go]

My actual database was in Filemaker Pro6. But i needed to convert it in Access. The data in the above two field(AI & CodeNo) has a multiple value in it.

I've shown here only 3 fields. in fact this table has 10 fields in it. But only 3 field has a multiple value in it. The goal is to relate multiple values in fields.

Well, this table is related to another table with CodeNo Field. So that is going to be my primary key.

Also if i add 4th multivalue field(text data type)with the same scenario as above what is going to change in code?

Sorry Guys, I am not a database person. Please bear with me. I can wait until late to night Richard.

I really appreciate your help.

 

[willir]

yog2go

Okay, here is the tweaked code. Now only using one function...

Private Sub ConvertIT_Click()

Dim dbs As DAO.Database, rstOld As DAO.Recordset, rstNew As DAO.Recordset
Dim strAILong As String, lngBL As Long
Dim arrAILong() As String, arrCodeNo() As String
Dim strSearch As String, intX As Integer

Set dbs = CurrentDb()
Set rstOld = dbs.OpenRecordset("OLD_tblBusinessLoc")
Set rstNew = dbs.OpenRecordset("NEW_tblBusinessLoc")

strSearch = ","

With rstOld
    .MoveFirst
    Do While Not .EOF

        lngBL = !bl
        
        'propagate arrays using split function
        'assumption that there is a 1-to-1 sequential match b/n AI and CodeNo
        'no check for bad data
        arrAILong = Split(!ai, strSearch)
        arrCodeNo = Split(!CodeNo, strSearch)
        
        'insert data into new table using array
        For intX = 0 To UBound(arrAILong())
            With rstNew
                .AddNew
                !bl = lngBL
                !ai = arrAILong(intX)
                !CodeNo = arrCodeNo(intX)
                '
                'You state you have 10 fields to update, add them here
                '
                .Update
            End With
        
        Next intX
        
        .MoveNext
    Loop
End With

rstOld.Close
rstNew.Close
dbs.Close
Set rstOld = Nothing
Set rstNew = Nothing
Set dbs = Nothing

End Sub

OKAY, I used this with data provided it works.
I am assuming your data will not have integrity issues

BUT I am concerned about your "primary key". Per the theory of Normalization, the second normal form recommends the use of a primary key. The data you provided does not suggest you have a primary key within the new table, and your comment ...

this table is related to another table with CodeNo Field. So that is going to be my primary key

... suggests you will try to use a key external to the new table as the primary key. You may want to reconsider this approach.

If you have time, review...
Fundamentals of Relational Database Design by Paul Litwin DOC version
or
Fundamentals of Relational Database Design by Paul Litwin DOC version

Another star to PHV for suggesting to use the SPLIT function.

Richard