Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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];
0
SteveL13
Asked:
SteveL13
  • 3
2 Solutions
 
SharathData 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
 
mbizupCommented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now