Verify imported field based on list in table

[apryor]

Hi, I'm not sure if this is possible, but I've heard you guys really know your stuff.

I have a client who would like to import a list of e-mail addresses, but would like to verify the domain name used in the provided address against a list of domain names pre-populated in a table. Do I need to use Dlookup? Here are some additional details. Thanks in advance!

MAIN_IMPORT_TBL
ID
FNAME
LNAME
E-MAIL
PHONE

DOMAIN_TBL
DOMAIN_ID
DOMAIN_NAME

AP

 

[neileg]

Use the unmatched query wizard to compare the two tables.

 

[apryor]

Sounds like a good idea, but how should I structure the query if I'm only comparing partial info? For example: I have several e-mail addresses that are imported into the staging ground, and I only want the addresses that end in .net or .edu (which would be listed in the DOMAIN_TBL) to be appended to the MAIN_IMPORT_TBL once I run the query.

The MAIN_IMPORT_TBL would contain the whole address James@blue.net, and would need to be compared to the list of valid domains in the DOMAIN_TBL (.net, .edu, etc...)

 

[Bodisathva]

I only want the addresses that end in .net or .edu

WHERE Right(DOMAIN_NAME,4) IN (".net", ".edu") 

or 

WHERE DOMAIN_NAME LIKE "***.net" OR DOMAN_NAME LIKE "***.edu"

 

[apryor]

The list of domain names I'm using actually includes several dozen items (.ca, .gov, .edu, .net, .co, .jp, etc). Is there a way to do the comparrison based on these items if they are listed in a table? What I'm trying to do, is verify that the e-mail addresses that are imported are valid (based on the list of domains). Sorry to keep going back and forth with this.

 

[apryor]

Here's a copy of the db.

 

[Bodisathva]

assuming that you store the list of valid domains as the only field in a table called Domains, you would change the SQL to:

...WHERE Right(DOMAIN_NAME,4) IN (SELECT * FROM Domains)

 

[apryor]

I pasted that into the SQL and I'm getting the following error. Did I put it in the wrong place?

Error:
You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field.

Code:
SELECT IMPORT_TBL.Email, IMPORT_TBL.Fname, IMPORT_TBL.Lname, IMPORT_TBL.Address1, IMPORT_TBL.Address2, IMPORT_TBL.City, IMPORT_TBL.State, IMPORT_TBL.Country, IMPORT_TBL.Zip, IMPORT_TBL.Tel, IMPORT_TBL.Gender, IMPORT_TBL.[Birth Year], IMPORT_TBL.[Birth Month], IMPORT_TBL.Birthday, IMPORT_TBL.DOB, IMPORT_TBL.[Source URL], IMPORT_TBL.[IP Address], IMPORT_TBL.[Date/time stamp]
FROM IMPORT_TBL
WHERE (((Right([Domain_Name],4)) In (SELECT * FROM DOMAIN_TBL)));

 

[Bodisathva]

looks like the field names are the same in both tables ([DOMAIN_NAME]) in which case, Access cannot distinguish between the two without using the syntax table.field. You may also want to replace the * with the field name you used in DOMAIN_TBL. try:

WHERE (((Right(IMPORT_TBL.Domain_Name,4)) In (SELECT DOMAIN_NAME FROM DOMAIN_TBL)));

a personal preference...using upper case field and table names makes distinguishing them from the SQL reserved words more difficult...and the underscore character is evil