Data Doubling in Query 1

[work4livinsean]

Hi everyone, I am having a serious problem and cannot figure out how to fix it. I have a new query that has one query and a table. The query and table are not joined but I need the table there to exclude information from the original query. Here is the SQL Statement:

SELECT queAPTNoSubmit.Group, queAPTNoSubmit.Name, queAPTNoSubmit.Empl_ID, queAPTNoSubmit.APEX_ID, queAPTNoSubmit.Department
FROM tblExcludePerson, queAPTNoSubmit
WHERE (((queAPTNoSubmit.APEX_ID)<>[tblExcludePerson]![ApexID]));

The statement actually works when I only have one data entry for the tblExcludePerson. However, as soon as I add someone new to the table my new query doubles all the data. So, I am left with twice the amount of data I should have but it still excludes one set of the data that should be excluded. Can anyone help me or explain to me why it doubles the data? I would really appreciate it!

Thanks in advance.

 

[abaldwin]

The problem is because of no join.

I am assuming that you are entering the ApexID of all the persons you want excluded into the tblExcludePerson Table.

Take the tblExcludePerson out of the From Clause

Change the Where to WHERE Apex_ID NOT IN (SELECT ApexID from tblExcludePerson



That should work.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[work4livinsean]

Thanks, you were right. That is strange b/c if you don't have the tblExcludePerson it is an error. But when you do have it w/out a join it creates a mess... Oh well, I understand now, Thank you.

 

[PHV]

Another way:
SELECT queAPTNoSubmit.Group, queAPTNoSubmit.Name, queAPTNoSubmit.Empl_ID, queAPTNoSubmit.APEX_ID, queAPTNoSubmit.Department
FROM tblExcludePerson RIGHT JOIN queAPTNoSubmit ON tblExcludePerson.ApexID = queAPTNoSubmit.APEX_ID
WHERE tblExcludePerson.ApexID Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886