Query returning records it shuold not be returning

I have a query with a table as its record source.  The are two fields in the query that I am setting a criteria for.  The are "Email" (text field), and "AssociationID" (numeric).

I don't want the query to return the records that have no number in "AssociationID" and have no text in "Email".  

But the query IS only returning the record that have a number in the "AssociationID" field but IS ALSO returning the records if the "Email" field is blank and it should not be.

Here is the SQL of the query:

SELECT tblAssociations.AssocName, [LastName] & ", " & [FirstName] AS Name, tblIndividuals.Email, tblIndividuals.AssociationID, tblIndividuals.FirstName, tblIndividuals.LastName
FROM tblIndividuals RIGHT JOIN tblAssociations ON tblIndividuals.AssociationID = tblAssociations.AssociationID
WHERE (((tblIndividuals.Email) Is Not Null) AND ((tblIndividuals.AssociationID) Is Not Null))
ORDER BY tblAssociations.AssocName, [LastName] & ", " & [FirstName];
SteveL13Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
try this.
SELECT tblAssociations.AssocName, [LastName] & ", " & [FirstName] AS Name, 
       tblIndividuals.Email, 
	   tblIndividuals.AssociationID, 
	   tblIndividuals.FirstName, 
	   tblIndividuals.LastName
  FROM tblIndividuals 
 RIGHT JOIN tblAssociations 
    ON tblIndividuals.AssociationID = tblAssociations.AssociationID
 WHERE (((tblIndividuals.Email) Is Not Null)
   AND trim(tblIndividuals.Email) <> '' 
   AND ((tblIndividuals.AssociationID) Is Not Null))
 ORDER BY tblAssociations.AssocName, [LastName] & ", " & [FirstName];

Open in new window

0
 
mbizupConnect With a Mentor Commented:
Or this:

SELECT a.AssocName, [LastName] & ", " & [FirstName] AS Name, i.Email, i.AssociationID, i.FirstName, i.LastName
FROM tblAssociations a LEFT JOIN tblIndividuals i ON a.AssociationID = i.AssociationID
WHERE NZ(i.Email,"") <> ""  AND  NZ(i.AssociationID ,"") <> "" 
ORDER BY a.AssocName, [LastName] & ", " & [FirstName];

Open in new window

0
 
mbizupCommented:
Also, if you're not allowing NULLs in associationID (ie: un-matched records), you can use an INNER JOIN without the associationID criteria:

SELECT a.AssocName, [LastName] & ", " & [FirstName] AS Name, i.Email, i.AssociationID, i.FirstName, i.LastName
FROM tblAssociations a INNER JOIN tblIndividuals i ON a.AssociationID = i.AssociationID
WHERE NZ(i.Email,"") <> "" 
ORDER BY a.AssocName, [LastName] & ", " & [FirstName];

Open in new window

0
 
mbizupCommented:
Steve,

These all should give you the results you're looking for, but I'd recommend actually using the INNER JOIN in my last post (http:#a39653400).  That right join from what I can tell is overkill, and might (possibly) be problematic if you are using this as a recordsource for a form whose data you want to update.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.