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

  • 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

    Wednesday, June 15, 2016 10:34 PM

Answers

  • 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)

    • Marked as answer by ryguy72 Saturday, June 18, 2016 3:07 AM
    Thursday, June 16, 2016 5:18 AM
  • 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.

    • Marked as answer by ryguy72 Saturday, June 18, 2016 3:07 AM
    Thursday, June 16, 2016 5:52 AM
  • 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


    • Edited by mbizup MVP Thursday, June 16, 2016 8:58 AM
    • Marked as answer by ryguy72 Saturday, June 18, 2016 3:07 AM
    Thursday, June 16, 2016 8:57 AM
  • 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.

    • Marked as answer by ryguy72 Saturday, June 18, 2016 3:06 AM
    Thursday, June 16, 2016 11:56 PM
    Moderator

All replies

  • 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)

    • Marked as answer by ryguy72 Saturday, June 18, 2016 3:07 AM
    Thursday, June 16, 2016 5:18 AM
  • 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.

    • Marked as answer by ryguy72 Saturday, June 18, 2016 3:07 AM
    Thursday, June 16, 2016 5:52 AM
  • 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


    • Edited by mbizup MVP Thursday, June 16, 2016 8:58 AM
    • Marked as answer by ryguy72 Saturday, June 18, 2016 3:07 AM
    Thursday, June 16, 2016 8:57 AM
  • 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.

    • Marked as answer by ryguy72 Saturday, June 18, 2016 3:06 AM
    Thursday, June 16, 2016 11:56 PM
    Moderator
  • 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

    Saturday, June 18, 2016 3:06 AM