Join Comma Sep field items to another field

[JeepStone]

I have a users table which contains fields which hold marketing information in the form of a comma separated list. These are inputted from a web form and appear in the database fields as

i.e.
mkting_info1
1,2,5
mkting_info2
10,30,9

Each mkting_info field has a table with an ID and a description. I need to join to this table and return a comma separated list of the desciptions in each field

ie
mkting_info1 becomes
The Sun, The Observer, New York Times

I think I need to split my field and create a table and then join to the correct tables but I have no idea how to do this with the Access Query builder.

Any help is really appreciated.

TIA

JeepStone

http://www.eggsbox.co.uk

 

[RickSpr]

What you've got is a multi-valued column, and your intuition telling you to split it up is a good thing.

Well, maybe. If the only use you have for this field is to translate it into another list of values, you may as well leave it the way it is.

In any case, you aren't going to be able to do this with simple queries. Having multiple values in one column is a violation of "first normal form", the first rule in designing a relational database, so it shouldn't surprise you that there are no SQL functions to help you break it up--you weren't supposed to combine them in the first place.

Note: I'm not criticizing your design, really. I realize that web forms aren't designed with relational purity in mind, and certainly management never wants to impose any rules on what salesmen do, for God's sake. (Can you tell this is a pet peeve of mine?)

So what I would do in your case is write a VBA function that takes a string (the value of this column), parses it into separate IDs, looks up the corresponding descriptions, and combines them with separating commas. This resulting string is returned as the function result.

Since you're talking about using the Query Builder, I assume you're something of a novice with Access, so I'll give you this function. I'll assume you'll look up descriptions in a table named "tblDescr", which has columns named "ID" and "Description". (ID would be one of the values in the multi-valued column; Description would be "The Sun", "The Observer", etc.)

    Public Function LookupDescr(strIDs As String) As String
        Dim i As Integer         ' used with InStr()
        Dim j As Integer         ' used with InStr()
        Dim strID As String      ' an ID from strIDs
        Dim varDescr As Variant  ' looked-up description
        Dim strResult As String  ' descriptions separated by commas

        Do While i < Len(strIDs)
            j = i + 1
            i = InStr(j, strIDs, &quot;,&quot;)
            If i = 0 Then i = Len(strIDs) + 1
            strID = Trim$(Mid$(strIDs, j, i - j))
            If Len(strID) > 0 Then
                If IsNumeric(strID) Then
                    varDescr = DLookup(&quot;Description&quot;, &quot;tblDescr&quot;, &quot;ID=&quot; & Val(strID))
                    If IsNull(varDescr) Then varDescr = &quot;<unknown>&quot;
                Else
                    varDescr = &quot;<invalid ID>&quot;
                End If
                strResult = strResult & &quot;, &quot; & varDescr
            End If
        Loop
        LookupDescr = Mid$(strResult, 3)
    End Function

Create a module and paste this code into it.

To use it in a query, set the Field: cell in a new column to "LookupDescr([columnname])", where "columnname" refers to the multi-valued column.

----------

If, on the other hand, you decide it's best to break up your multi-valued column into separate columns, you'll still have to do that with VBA code. Study LookupDescr to understand how it parses the column. You may be able to use some of the parsing code in your own Sub or Function procedure.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein