Search code examples
sqlms-accessboolean-logic

MS Access query producing an unexpected result (illustrated)

(Disclaimer: No real names in this database. Database is from a tutorial offered free online by GCF [Goodwill Community Foundation, a respected organization] Database source)

Below is a subset of the original database which should be sufficient for the purposes of the problem here:

First Name Last Name City
Alex Hinton Cary
Amaya Gibson Raleigh
Danny Haverford Raleigh
Dick Whitman Raleigh
Denver Ferguson Raleigh
Don Hamm Cary
Alix Mars Raleigh
Dwyane James Cary
Dwight Parker Raleigh
Andrew Bedinger Raleigh
Dane Raught Raleigh
Andrzej Wujek Raleigh
Alyssa Ullman Raleigh
David Barrett Raleigh
Anisa Naser Raleigh
Avery Kelly Raleigh
Derek MacDonald Chapel Hill
Alex Yuen Cary
David Barrett Raleigh

I'm getting an unexpected result in a database query.

I know I'm doing something wrong. I need your advice on what I'm doing wrong.

Part 1: this works

The query below works as expected.

(update) in Plain English: "give me firstname, last name, city where ((first name starts with "a" and city is "raleigh") OR (where first name starts with "d" and city is not "chapel hill"))"

(update) the SQL from the SQL view is:

Query:

enter image description here

Result:

enter image description here

Part 2: this doesn't work

(update) in Plain English: "give me first name, last name, city where ((first name starts with "a" and city is "raleigh") OR (where first name starts with "d" and city is not "chapel hill") OR (where first name starts with "d" and city is not "cary")"

In the query below, I'm still seeing results from the cities "Chapel Hill" and "Cary". So it is not working as expected.

Query:

SELECT Customers.[First Name], Customers.[Last Name], Customers.City
FROM Customers
WHERE (((Customers.[First Name]) Like "a*") AND ((Customers.City)="raleigh")) OR (((Customers.[First Name]) Like "d*") AND (Not (Customers.City)="raleigh")) OR (((Customers.[First Name]) Like "d*") AND (Not (Customers.City)="cary"));

enter image description here

Result:

enter image description here

What am I doing wrong in Part 2?

Thanks in advance.

Update: I've understood the problem. The "NOT" is unintuitive. The query is working fine. It's just that I assumed that it won't show "Chapel Hill" or "Cary" in the result. But the reason these are showing up in the result is because the Not "chapel hill" is causing Cary to show, and the Not "cary" is causing "Chapel Hill" to show

Solution

  • I've understood the problem. The "NOT" was unintuitive to me. The query is working fine. It's just that I assumed that it won't show "Chapel Hill" or "Cary" in the result. But the reason these are showing up in the result is because the Not "chapel hill" is causing Cary to show, and the Not "cary" is causing "Chapel Hill" to show.