Confusing query results

[Steven Sutton]

The problem I am having is with an SQL statement I wrote in VBA but the
examples I am giving actually come from a Query where I could let Access
write the SQL statement for me.

I have a table that has 1143 records in it. If I run the following Query it
returns 11 records, which is correct:

SELECT tblJobTickets.JobNumber, tblJobs.intJobID, tblJobs.txtJobName,
tblJobs.Status, tblJobs.dtEntered, tblJobs.dtModified, tblJobs.txtUsername,
tblJobs.intStatusSort
FROM tblJobTickets RIGHT JOIN tblJobs ON tblJobTickets.intJobID =
tblJobs.intJobID
WHERE ((tblJobs.Status)<>'Completed');

If I run this Query, it returns 1142 records - also correct (at this time,
there is only one record with the Status of 'Canceled':

SELECT tblJobTickets.JobNumber, tblJobs.intJobID, tblJobs.txtJobName,
tblJobs.Status, tblJobs.dtEntered, tblJobs.dtModified, tblJobs.txtUsername,
tblJobs.intStatusSort
FROM tblJobTickets RIGHT JOIN tblJobs ON tblJobTickets.intJobID =
tblJobs.intJobID
WHERE ((tblJobs.Status)<>'Canceled');

But if I run this Query, I get all 1143 records and it seems to me that I
should be getting 10 - the 11 that aren't 'Completed' minus the one that is
'Canceled':

SELECT tblJobTickets.JobNumber, tblJobs.intJobID, tblJobs.txtJobName,
tblJobs.Status, tblJobs.dtEntered, tblJobs.dtModified, tblJobs.txtUsername,
tblJobs.intStatusSort
FROM tblJobTickets RIGHT JOIN tblJobs ON tblJobTickets.intJobID =
tblJobs.intJobID
WHERE (((tblJobs.Status)<>'Canceled')) OR (((tblJobs.Status)<>'Completed'));

Does anyone see why I am not getting the results I am expecting? If I
haven't made it clear, what I am wanting is to exclude all records whose
Status is either 'Completed' or 'Canceled' but instead I am getting all
records. Any suggestions? Thanks again to all who help.

 

[Allen Browne]

Not sure I followed you, Steven.

Your final query asks for the records WHERE
((tblJobs.Status <> 'Canceled') OR (tblJobs.Status <>'Completed'))

Only one part of an OR has to match to return the record.
The records where Status is Canceled match the first part.
The records where Status is Completed match the second part.
Hence all the Canceled and all the Completed get returned.
Is that what you intended?

On a different topic, you have tblJobs is on the outside of the join?
The criteria you applied have the effect of eliminiating nulls.
Hence the query will behave like an inner join.

 

[John Spencer]

Try replacing the OR with AND.

Boolean logic can be confusing. You want records where Status is not
canceled and where status is not Completed.

WHERE (((tblJobs.Status)<>'Canceled')) AND ((tblJobs.Status)<>'Completed'))

Or you can use
WHERE tblJobs.Status Not IN('Canceled','Completed')

Or you can use

WHERE Not (tblJobs.Status ='Canceled' OR tblJobs.Status='Completed')


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Steven Sutton]

Thank you John. I tried changing the OR to AND not 5 minutes after I posted
this question and sure enough I got what I was wanting regards the results. I
tried posting back that I had found the answer I needed but I had all kinds
of trouble getting logged in to this site and was unable to add a new post
that I had found the answer. Thanks for your answer and I certainlt didn't
know that there were so many different ways to phrase the WHERE clause!

Allen - I just wish I knew enough about Access to be able to answer your
questions! To be perfectly honest, I tried different Joins until I finally
got the results I wanted without really knowing the differences from one Join
Type to the next. Someday I hope to understand what I have done!