Match Fields with Query with special characters

[Freefall69]

I am using Access 2007 and have 3 tables to match on common field (email address) but it is not populated the same in each table

Table #1 joe.smith@abc.com
Table #2 joe.smith or could be joesmith
Table #3 joe.smith@abc.com

How do I assemble query to just match everything before the @ symbol and account for the joe.smith vs the joesmith? The data entry done by others is not always formatted the same.

 

[MajP]

You could do something like this

Public Function CleanEmail(email As Variant) As String
  If Not IsNull(email) Then
    CleanEmail = Split(email, "@")(0)
    CleanEmail = Replace(CleanEmail, ".", "")
  End If
End Function

And use it like this

SELECT 
 tblEmail.address, 
 tblEmail_1.address
FROM 
 tblEmail, 
 tblEmail_1
WHERE 
 cleanEmail([tblEmail].[address])=cleanEmail([tblEmail_1].[address])

 

[Andrzejek]

But the two are completely different e-mail addresses:
joe.smith@abc.com
joesmith@abc.com

I don’t know if I could – with a clear conscience – remove the period and assume the two are the same.

Have fun.

---- Andy

 

[MajP]

Maybe I do not understand the question.

joe.smith or could be joesmith

You said it could be joe.smith or could be joesmith, but now you are saying it cannot be and that they are different.

 

[Andrzejek]

MayP,

That's not Freefall69 talking, that's just me voicing my concern...

Have fun.

---- Andy

 

[randy700]

Andrzejek is right.
There is no way to know if [ignore]joesmith@abc.com[/ignore] and [ignore]joe.smith@abc.com[/ignore] are the same person.

Actually, I would design my code to assume they are NOT the same.


Randy

 

[MajP]

There is no way to know if joesmith@abc.com and joe.smith@abc.com are the same person.
Actually, I would design my code to assume they are NOT the same.

Based on that logic, there is no code that you could write. You would have to have some data integrity rules on the table. Or the OP will have to provide more exclusion rules.

Because if in table 2, the name can be written with a period or without a period
like:
peterambo

Without additional information, there is no way to know for certain if it matches any of these
peter.ambo@abc.com
pete.rambo@abc.com
peterambo@abc.com

You would have to verify manually.
The only ones you could match with certainty would be those of the form (first.last) in both tables.

1)So build a query to match (first.last formatted names). Use a where clause to eliminate those without a period. Then use this function

Public Function CleanEmail(email As Variant) As String
  If Not IsNull(email) Then
    CleanEmail = Split(email, "@")(0)
  End If
End Function

This returns all records with "first.last" in both tables

2)Create another query using the same function but with a where clause that only includes records without a period
This returns all matches of the form "firstlast" in both tables
As pointed out this is still uncertain.
3)Create a union query of 1 and 2. These should be pretty good matches

4) Use the original function provided and a where clause to include only records where the left table is of the form (first.last) and the right table is of the form (firstlast).
Verify all records manually, and apply additional logic to determine if a match.

5)Do the same as 4 but left is of the form (firstlast) and right of the form (first.last)
verify all records manually.

 

[randy700]

Seems to me this is a perfect example of why you should never store the same data in multiple locations.
In this instance, if Mr Smith changes his email address (say to @gmail.com), you need to update it in each table.

Perhaps the email address should be stored in 1 location. Either a separate table or the same table that Mr Smith's ID is created (tblEmployee, tblCustomer, tblVendor, etc).



Randy