How best to match comma seperated values in one record with one value in one record?

Question

This is a weird one.  Just a warning.  I have two tables that look like what you see below.  Let's say this is Table1 (above) and Table2.

I want to match the records in the first table ot the second.  I can't think of any slick way, or intuitive way to do this using native Access processes.  I have a VBA script that split the comma-delimited values in one record, to several different records.  You can see this in the image below.

Let's say this is Table3.

Is this the best way to do this kind of thing?  Run the VBA to split out all the values and then create a relationship between Table2 and Table3?

I'm just trying to think of the best practice here.

Thanks.

---

MY BOOK

Answer 1

For some purposes, you could use Like or InStr to match values in Data_Source to ZIP_ID, but in general, working with the "exploded" field is much more flexible, so I'd prefer that.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Is this the best way to do this kind of thing?  Run the VBA to split out all the values and then create a relationship between Table2 and Table3?

Hi ryguy,

"The best way" is very dependant on what you want.

"My" best way is not to use not to store csv-lists or that kind of things. In this way I managed to make all relations in my applications as bi-directional. That is for each Data_Center one can immediately see which Zip_ID are related, but also for each ZIP_ID which Data_Center are related. The surplus value of this is that it is completely automated in my applications.

Imb.

Answer 3

How did tblSystems come into being?  Was it designed directly in Access or was it the result of an import (such as TransferSpreadheet)?

If it is created from a spreadsheet or some other non-Access source, you could process the raw data into the format you described (splitting the list into multiple records) before using it within Access.

A common process for imported data is to use a 'staging table' for the raw data from the initial import to facilitate cleaning up/formatting the data into a separate table, which then gets used in forms, queries reports, etc.

---

Miriam Bizup Access MVP

Answer 4

Hi ryguy72,

I think that the way suggested by Imb-hb is the good option to handle the issue like this.

but here I would recommend you to view all the suggestions provided by the members and then decide which is best suitable for you or with which you can work comfortably and easily without having any other issue.

I would recommend you to mark that suggestion as an Answer which help you to solve your issue.

Regards

Deepak 

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 5

These are all great suggestions.  As I read trough the suggestions, I came to believe there is no 'best' was you handle this.  I love the idea of getting the data delivered in a normalized fashion.  If that's absolutely not possible, the staging table solution sounds good to me.  Exploding the data may actually be the best way to handle it, if nothing else works.  Below is the VBA to explode the data, in case someone needs it someday.

Sub ExplodeTable(SourceTable As String, _
        ExplodeTable As String, _
        SourceKey As String, _
        ExplodeKey As String, _
        SourceField As String, _
        ExplodeField As String, _
        Optional Delimiter As String = ",")

    Dim db                  As DAO.Database
    Dim rsSource            As DAO.Recordset
    Dim rsExplode           As DAO.Recordset
    Dim astrExplodeValues() As String
    Dim I                   As Long

    Set db = CurrentDb
    Set rsSource = db.OpenRecordset(SourceTable)
    Set rsExplode = db.OpenRecordset(ExplodeTable, dbOpenDynaset, dbAppendOnly)

    Do Until rsSource.EOF

        If Len(rsSource.Fields(SourceField) & vbNullString) > 0 Then

            astrExplodeValues = Split(rsSource.Fields(SourceField), Delimiter)

            For I = LBound(astrExplodeValues) To UBound(astrExplodeValues)

                With rsExplode
                    .AddNew
                    .Fields(ExplodeKey) = rsSource.Fields(SourceKey)
                    .Fields(ExplodeField) = astrExplodeValues(I)
                    .Update
                End With

            Next I

        End If

        rsSource.MoveNext
    Loop

    rsSource.Close
    rsExplode.Close

End Sub

Private Sub Command0_Click()
    ' Call ExplodeTable("table1", "table2", "field_in_table1", "field_in_table2", "field_in_table1", "field_in_table2")
    Call ExplodeTable("tblSystems", "tblExplodedAU", "Data_Center_LE_Code", "Source_Field", "Data_Source", "Exploded_Field")
End Sub

---

MY BOOK