Query - Partial matches with email addresses

[uptime244 via AccessMonster.com]

Need some advise:

I am doing a select query and the table is several thousand email addresses.
We have another table of emails (exceptions) that need to be checked against
the first list to make sure they do not exist in both lists


Table A - emails
Table B - do not send to these emails

This is normally done and quite simple with EXACT matches. I have simply tied
the tables together with a relationship on the email field and I would get
the e-mails that match in both tables. That way we would get a list of a
dozen or so e-mails to NOT send to.

Now I have a suppression list that does not contain e-mails, but it has
domains(several thousand). They are listed as "@domain.com" So any e-mail
addresses that belong to any of the domains need to be filtered from table A.
So this is where I am having an issue. In this example I would want to have
the results of the query say (e-mail address removed), (e-mail address removed), etc, etc.

I have been setting up a select query as I did in the past and using the
"like" command under the "criteria" section. e.g. Like "* &[table with
Domains].[domains field]& *" .

This is not working and I am just getting a empty result. Any help is
appreciated.

 

[tina]

first create a query of your "email addresses" table. in the query design
view, include the address field, and add the following calculated field, as

DomainOnly: Right([eAddress],Len([eAddress])-(InStr(1,[eAddress],"@")-1))

substitute the correct name of the address field, in place of "eAddress".

now write your Select query, based on the above query and the "domains"
table. link the domain field in the table with the DomainOnly field in the
query, to pull the email addresses with matching domains in the domains
table.

hth