How to join one value to a list of values

[teriw]

Hello,

An affiliate sent us a table of email addresses, one per record. We need to find which ones already exist in our master table. Our master table contains an email field but it may contain MULTIPLE email addresses separated by semicolons. How do we create a query (or queries) which tell us which email addresses already exist somewhere in our master table?

Thank you,
Teri

 

[David R]

Do you want to do it fast or right?

 

[Mihail]

Do you want to do it fast or right?

Interesting question. I'll wait also for your solution.

 

[JHB]

Hello,

An affiliate sent us a table of email addresses, one per record. We need to find which ones already exist in our master table. Our master table contains an email field but it may contain MULTIPLE email addresses separated by semicolons. How do we create a query (or queries) which tell us which email addresses already exist somewhere in our master table?

As you now realize storing multiple values ​​in a field are not any good, save the values ​​in a separate table, (one record per value), that is linked to the master table.
There are some function/features in MS-Access that can help you out of the mess you are in right now like InStr and Split, the last one need some UDF.
The last one can also be use to create the separate table.

 

[teriw]

You are seeing an ugly slice of what is otherwise a well normalized database. The email addresses are no doubt being handled poorly. Ideally they should be in a separate table. It may come to that but it's not yet clear whether this data, which is part of a standalone application we inherited, will be need to be migrated to our live system or discarded.

At the moment I suppose we're looking for a (somewhat) quick solution to achieve what I asked about. Could we hear more specifics on using the functions InStr, Split, UDFs, etc. to accomplish this? Can anyone share specific examples of this or some other approach?

Thank you for your responses.

Teri

 

[jdraw]

I'd make a separate table based on the data you have.
I think a recordset with identification info and your email;email; field using a Split on ;
then add to the new table with the id field.
Once new table is completed, use a query (wizard for unmatched) to see what new emails you currently don't have.

 

[David R]

I agree with jdraw, it will be worth splitting that data into a temporary table even if you don't end up importing it permanently.

 

[jdraw]

Here's a skeleton to show concept

Sub PutEmailsInTable()
      Dim db As DAO.Database
      Dim rs As DAO.Recordset
      Dim sampl As String
      Dim myId As String
      Dim i As Integer

      'test data to show split  recordID in positions 1 and 2  followed by emails separated by ;
10    sampl = "25jack@west.com;bob@gmail.com;sam@west.ca;lea@diligens.com"

      Dim varSplit  As Variant
20       On Error GoTo PutEmailsInTable_Error

30    Set db = CurrentDb
      'Set rs = db.OpenRecordset("yourIDandEmailFieldquery")

      'Do While Not rs.EOF
40    myId = Left(sampl, 2)
50    varSplit = Split(Mid(sampl, 3), ";") 'only scan/split the email info
60      For i = 0 To UBound(varSplit)
        
        'simulate record creation to immediate window
        
70           Debug.Print myId; " "; varSplit(i)
80      Next i


      'rs.MoveNext
      'Loop

90       On Error GoTo 0
100      Exit Sub

PutEmailsInTable_Error:

110       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure PutEmailsInTable of Module AWF_Related"
End Sub