Help Using Unmatched Queries

[ssampier]

I have a Excel spreadsheet of email usernames. This file is emailed to me automatically by the system. I imported the usernames into a separate table, tblUsernames. I have another table that contains all customer records and their email addresses,tblCustomers. I created a query from all active customers who do have a email address on record, qryAllUsernames.

I'm trying to create a separate unmatched query comparing tblUsernames with qryAllUsernames. This query seems to work (it has 27 results). I encounters problems with the reverse, compare qryAllUsernames with tblUsernames (it has 4987 results).
I did define a relationship between qryAllUsernames.EmailAddress and
tblUsernames.Usernames. My concern is my qryAllUsernames contains multiple usernames fields,Email1, Email2, Email3, etc., to reflect customers having multiple email addresses. tblUsernames, in contrast, contains all email usernames in one field.

 

[M8KWR]

can you post the sql code, so i can see what is going on with you query, and hopefully ammend to get it to work for you.

 

[ssampier]

Sure. I appreciate your help.

SELECT [tblUsername Temp].ID, [tblUsername Temp].Email
FROM [tblUsername Temp] LEFT JOIN qryAllEmail ON [tblUsername Temp].Email = qryAllEmail.[email 1]
WHERE (((qryAllEmail.EmailAddress) Is Null));

I'm a SQL newbie. I can run simple SELECT queries, but I always get confused on the join procedures. Thanks again.

 

[M8KWR]

I think it may be worth your while doing a Union select query, so you can get all the columns email1, email2, and email3, into one column, along with the username.

Once you have got this, then you can do the query again, and it should work a bit better.

From your code above I would change it to

SELECT [tblUsername Temp].ID, [tblUsername Temp].Email
FROM [tblUsername Temp] RIGHT JOIN qryAllEmail ON [tblUsername Temp].Email = qryAllEmail.[email 1]
WHERE ((([tblUsername Temp].Email) Is Null));

 

[ssampier]

I wrote the SQL code for a Union Query which I named qryAllActiveUsernames. I believe I wrote the query correctly.

I was unable to get your changes to work, however. When I run the query, it is completely blank. I had to change the query references, qryAllUsernames, to qryAllActiveUsernames (the Union query).

Without the change the query still has better results. It has 87 and 53 results, respectively, which is high, but not impossible. I should note that I did recreate the queries in design view.