MS Access 2003 SQL Not Exists question

#1

Hi,

Why is this returning records that exist in the first table
(tblRoomAssignmentTemp) that are not in the second table
(tblMoneyReceived)? From a SQL book that I have and from what I have
found on the Internet, this is the proper syntax and therefore
shouldn't be happening. Maybe I messed something up:

Select tblRoomAssignmentTemp.reservationid From tblRoomAssignmentTemp
Where NOT EXISTS (SELECT tblMoneyReceived.ReservationID,
tblMoneyReceived.PaymentCategory
FROM tblMoneyReceived Where tblMoneyReceived.ReservationId =
tblRoomAssignmentTemp.reservationid)

I've tried Not IsNull(tblMoneyReceived.reservationid) but that makes
no difference.

However, I managed to build a query in the Query Builder that works (I
can not entirely grasp Joins!):

SELECT tblRoomAssignmentTemp.ReservationID,
tblMoneyReceived.PaymentCategory
FROM tblRoomAssignmentTemp INNER JOIN tblMoneyReceived ON
tblRoomAssignmentTemp.ReservationID = tblMoneyReceived.ReservationID
WHERE (((tblRoomAssignmentTemp.ReservationID)>0) AND
((tblMoneyReceived.PaymentCategory)="Deposit"))
ORDER BY tblRoomAssignmentTemp.ReservationID;

#2

I'm glad you discovered on your own that Access' "flavor" of SQL, unless you
specify otherwise, differs from the standard in some respects, and that
researching in "an SQL book" can lead to frustration. Researching in an
Access book that covers SQL, or searching online for Access SQL, will be
much more productive.

Is there some particular reason that you feel it is necessary to write your
own SQL from scratch, rather than use the very helpful Query Builder that
Access provides? It's really easy to use, and, if you need to later
manipulate the SQL with VBA code, you can switch to SQL View to see what it
generates. Sometimes it generates a few extra parentheses pairs, and there
are some things it won't handle (UNION queries, for example, and
subqueries). There's a good deal of information available in the newsgroup
archives and at the Microsoft site regarding subqueries in Access.

Anyway, glad you found something that works. Best of luck with using
Access.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"PW" <emailaddyinsig (AT) ifIremember (DOT) com> wrote

Quote:

Hi,

Why is this returning records that exist in the first table
(tblRoomAssignmentTemp) that are not in the second table
(tblMoneyReceived)? From a SQL book that I have and from what I have
found on the Internet, this is the proper syntax and therefore
shouldn't be happening. Maybe I messed something up:

Select tblRoomAssignmentTemp.reservationid From tblRoomAssignmentTemp
Where NOT EXISTS (SELECT tblMoneyReceived.ReservationID,
tblMoneyReceived.PaymentCategory
FROM tblMoneyReceived Where tblMoneyReceived.ReservationId =
tblRoomAssignmentTemp.reservationid)

I've tried Not IsNull(tblMoneyReceived.reservationid) but that makes
no difference.

However, I managed to build a query in the Query Builder that works (I
can not entirely grasp Joins!):

SELECT tblRoomAssignmentTemp.ReservationID,
tblMoneyReceived.PaymentCategory
FROM tblRoomAssignmentTemp INNER JOIN tblMoneyReceived ON
tblRoomAssignmentTemp.ReservationID = tblMoneyReceived.ReservationID
WHERE (((tblRoomAssignmentTemp.ReservationID)>0) AND
((tblMoneyReceived.PaymentCategory)="Deposit"))
ORDER BY tblRoomAssignmentTemp.ReservationID;

#3

On Wed, 15 Jun 2011 13:11:52 -0500, "Access Developer"
<accdevel (AT) gmail (DOT) com> wrote:

Quote:

I'm glad you discovered on your own that Access' "flavor" of SQL, unless you
specify otherwise, differs from the standard in some respects, and that
researching in "an SQL book" can lead to frustration. Researching in an
Access book that covers SQL, or searching online for Access SQL, will be
much more productive.

I saw this book at Amazon but I didn't like the reviews:
http://www.amazon.com/Microsoft-Acce...8161901&sr=1-1

Quote:

Is there some particular reason that you feel it is necessary to write your
own SQL from scratch, rather than use the very helpful Query Builder that
Access provides?

Just being cool I guess ;-). I guess I am being old fashioned.

Quote:

It's really easy to use, and, if you need to later
manipulate the SQL with VBA code, you can switch to SQL View to see what it
generates. Sometimes it generates a few extra parentheses pairs, and there
are some things it won't handle (UNION queries, for example, and
subqueries). There's a good deal of information available in the newsgroup
archives and at the Microsoft site regarding subqueries in Access.

Yes, it is an amazing tool.

Quote:

Anyway, glad you found something that works. Best of luck with using
Access.

Okay <g>

Not enough coffee today Larry? <g>

-paul

#4

I'z wrong! The query is NOT listing just records from the first table
(tlbRoomAssignmentTemp) that don't exist in the second
(tblMoneyReceived). Rats! I only want assigned rooms/reservations
that haven't made a deposit payment.

Hmmm...I don't know what to do about this since it looks like I can't
use Where Not Exists or Not In.

-paulw

On Wed, 15 Jun 2011 10:55:01 -0600, PW
<emailaddyinsig (AT) ifIremember (DOT) com> wrote:

Quote:

Hi,

Why is this returning records that exist in the first table
(tblRoomAssignmentTemp) that are not in the second table
(tblMoneyReceived)? From a SQL book that I have and from what I have
found on the Internet, this is the proper syntax and therefore
shouldn't be happening. Maybe I messed something up:

Select tblRoomAssignmentTemp.reservationid From tblRoomAssignmentTemp
Where NOT EXISTS (SELECT tblMoneyReceived.ReservationID,
tblMoneyReceived.PaymentCategory
FROM tblMoneyReceived Where tblMoneyReceived.ReservationId =
tblRoomAssignmentTemp.reservationid)

I've tried Not IsNull(tblMoneyReceived.reservationid) but that makes
no difference.

However, I managed to build a query in the Query Builder that works (I
can not entirely grasp Joins!):

SELECT tblRoomAssignmentTemp.ReservationID,
tblMoneyReceived.PaymentCategory
FROM tblRoomAssignmentTemp INNER JOIN tblMoneyReceived ON
tblRoomAssignmentTemp.ReservationID = tblMoneyReceived.ReservationID
WHERE (((tblRoomAssignmentTemp.ReservationID)>0) AND
((tblMoneyReceived.PaymentCategory)="Deposit"))
ORDER BY tblRoomAssignmentTemp.ReservationID;

#5

Quote:

I'z wrong! The query is NOT listing just records from the first table
(tlbRoomAssignmentTemp) that don't exist in the second
(tblMoneyReceived)

..I was wondering about that. The query you said was working used an INNER
JOIN which is the wrong type of join to exclude records. You need to change
it to a LEFT OUTER JOIN and add a criterion to exclude records where a key
field from the right side of the join is null:

SELECT tblRoomAssignmentTemp.ReservationID,
tblMoneyReceived.PaymentCategory
FROM tblRoomAssignmentTemp LEFT JOIN tblMoneyReceived ON
tblRoomAssignmentTemp.ReservationID = tblMoneyReceived.ReservationID
WHERE (((tblRoomAssignmentTemp.ReservationID)>0) AND
((tblMoneyReceived.ReservationID) is null))
ORDER BY tblRoomAssignmentTemp.ReservationID;

Having said that, I am very puzzled that the WHERE NOT EXISTS construct did
not work. It should have looked like this:

Select tblRoomAssignmentTemp.reservationid From tblRoomAssignmentTemp
Where NOT EXISTS (SELECT *
FROM tblMoneyReceived Where tblMoneyReceived.ReservationId =
tblRoomAssignmentTemp.reservationid)

Are you telling me that this query returns records where
tblMoneyReceived.ReservationId =
tblRoomAssignmentTemp.reservationid?

Rats! I only want assigned rooms/reservations

Quote:

that haven't made a deposit payment.

Oh wait - could that be the problem? You initially failed to specify that
deposit payment criterion. It would look like this:

Select tblRoomAssignmentTemp.reservationid From tblRoomAssignmentTemp
Where NOT EXISTS (SELECT *
FROM tblMoneyReceived Where tblMoneyReceived.ReservationId =
tblRoomAssignmentTemp.reservationid
and tblMoneyReceived.PaymentCategory="Deposit")

Alternatively it would look like this:

Select tblRoomAssignmentTemp.reservationid From tblRoomAssignmentTemp
Where reservationid NOT IN (SELECT ReservationId
FROM tblMoneyReceived Where
and tblMoneyReceived.PaymentCategory="Deposit")

I used to believe the EXISTS version was more efficient than the IN version,
but I've had some doubts raised about this in the past couple years, so test
both and use whichever performs better. The problem with using the exclusion
join is that if there can be multiple records with the same ReservationId in
tblMoneyReceived, then you will get multiple records returned even if only a
single record exists in tblRoomAssignmentTemp. The subquery solutions avoid
that trap.

#6

PW <emailaddyinsig (AT) ifIremember (DOT) com> wrote in
news:5kohv65ou6umq6bkiq014njqu1jcpt7d48 (AT) 4ax (DOT) com:

Quote:

However, I managed to build a query in the Query Builder that
works (I can not entirely grasp Joins!):

SELECT tblRoomAssignmentTemp.ReservationID,
tblMoneyReceived.PaymentCategory
FROM tblRoomAssignmentTemp INNER JOIN tblMoneyReceived ON
tblRoomAssignmentTemp.ReservationID =
tblMoneyReceived.ReservationID WHERE
(((tblRoomAssignmentTemp.ReservationID)>0) AND
((tblMoneyReceived.PaymentCategory)="Deposit")) ORDER BY
tblRoomAssignmentTemp.ReservationID;

Using a JOIN is going to be more reliably efficient than NOT EXISTS
with a subquery because NOT EXISTS (and NOT IN) are not reliably
optimized by the Jet/ACE query optimizer to use the indexes on both
sides of the comparison. A JOIN will always use the indexes on both
sides, so if you can write the query using a JOIN, that's going to
be preferable.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#7

"Bob Barrows" <reb01501 (AT) NOyahooSPAM (DOT) com> wrote in
news:itb5md$n0u$1 (AT) dont-email (DOT) me:

Quote:

I used to believe the EXISTS version was more efficient than the
IN version, but I've had some doubts raised about this in the past
couple years, so test both and use whichever performs better. The
problem with using the exclusion join is that if there can be
multiple records with the same ReservationId in tblMoneyReceived,
then you will get multiple records returned even if only a single
record exists in tblRoomAssignmentTemp. The subquery solutions
avoid that trap.

Both IN and EXISTS are pretty reliably optimized indexed-wise, but
when you add the NOT keyword, it is often the case that indexes are
not used on both sides of the comparison.

Personally, I've never quite understood what EXISTS works or what it
really means, so I don't ever use it. But I do know about the
indexing problem from troubleshooting performance problems in SQL
written by others.

I don't think the multiple records returned issue is a problem
except if you need an editable resultset -- just use SELECT
DISTINCT.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#8

On Wed, 15 Jun 2011 16:46:00 -0400, "Bob Barrows"
<reb01501 (AT) NOyahooSPAM (DOT) com> wrote:

Quote:

I'z wrong! The query is NOT listing just records from the first table
(tlbRoomAssignmentTemp) that don't exist in the second
(tblMoneyReceived)

.I was wondering about that. The query you said was working used an INNER
JOIN which is the wrong type of join to exclude records. You need to change
it to a LEFT OUTER JOIN and add a criterion to exclude records where a key
field from the right side of the join is null:

SELECT tblRoomAssignmentTemp.ReservationID,
tblMoneyReceived.PaymentCategory
FROM tblRoomAssignmentTemp LEFT JOIN tblMoneyReceived ON
tblRoomAssignmentTemp.ReservationID = tblMoneyReceived.ReservationID
WHERE (((tblRoomAssignmentTemp.ReservationID)>0) AND
((tblMoneyReceived.ReservationID) is null))
ORDER BY tblRoomAssignmentTemp.ReservationID;

Having said that, I am very puzzled that the WHERE NOT EXISTS construct did
not work. It should have looked like this:

Select tblRoomAssignmentTemp.reservationid From tblRoomAssignmentTemp
Where NOT EXISTS (SELECT *
FROM tblMoneyReceived Where tblMoneyReceived.ReservationId =
tblRoomAssignmentTemp.reservationid)

Are you telling me that this query returns records where
tblMoneyReceived.ReservationId =
tblRoomAssignmentTemp.reservationid?

Your version seems to be working! And I think different than what I
did.

Quote:

Rats! I only want assigned rooms/reservations
that haven't made a deposit payment.

Oh wait - could that be the problem? You initially failed to specify that
deposit payment criterion. It would look like this:

Oh boy. My bad.

Quote:

Select tblRoomAssignmentTemp.reservationid From tblRoomAssignmentTemp
Where NOT EXISTS (SELECT *
FROM tblMoneyReceived Where tblMoneyReceived.ReservationId =
tblRoomAssignmentTemp.reservationid
and tblMoneyReceived.PaymentCategory="Deposit")

Alternatively it would look like this:

Select tblRoomAssignmentTemp.reservationid From tblRoomAssignmentTemp
Where reservationid NOT IN (SELECT ReservationId
FROM tblMoneyReceived Where
and tblMoneyReceived.PaymentCategory="Deposit")

I replaced tblMR with a query that includes only deposit records.

Quote:

I used to believe the EXISTS version was more efficient than the IN version,

The SQL book I have says the same thing, and what I googled agrees
(but both are not MS Access query topics)

Quote:

but I've had some doubts raised about this in the past couple years, so test
both and use whichever performs better. The problem with using the exclusion
join is that if there can be multiple records with the same ReservationId in
tblMoneyReceived, then you will get multiple records returned even if only a
single record exists in tblRoomAssignmentTemp
. The subquery solutions avoid
that trap.

There will be because that table includes all money received for each
reservation. Deposits, store purchases, final balances, ...

I will check just to make sure. But filtering the table by deposit
records only should also prevent that if the users don't mess up.
Oops. I'd better doubley check that! I don't remember if I have a
check for that.

Thanks so much.

-paul

#9

On 15 Jun 2011 20:51:33 GMT, "David-W-Fenton"
<NoEmail (AT) SeeSignature (DOT) invalid> wrote:

Quote:

PW <emailaddyinsig (AT) ifIremember (DOT) com> wrote in
news:5kohv65ou6umq6bkiq014njqu1jcpt7d48 (AT) 4ax (DOT) com:

However, I managed to build a query in the Query Builder that
works (I can not entirely grasp Joins!):

SELECT tblRoomAssignmentTemp.ReservationID,
tblMoneyReceived.PaymentCategory
FROM tblRoomAssignmentTemp INNER JOIN tblMoneyReceived ON
tblRoomAssignmentTemp.ReservationID =
tblMoneyReceived.ReservationID WHERE
(((tblRoomAssignmentTemp.ReservationID)>0) AND
((tblMoneyReceived.PaymentCategory)="Deposit")) ORDER BY
tblRoomAssignmentTemp.ReservationID;

Using a JOIN is going to be more reliably efficient than NOT EXISTS
with a subquery because NOT EXISTS (and NOT IN) are not reliably
optimized by the Jet/ACE query optimizer to use the indexes on both
sides of the comparison. A JOIN will always use the indexes on both
sides, so if you can write the query using a JOIN, that's going to
be preferable.

I'll give it a shot David. Thanks.

-paul

#10

If you want records from tblRoomAssignmentTemp that do NOT have a Deposit
payment then you will need to use a subquery in the FROM clause or Not Exists
subquery in the where clause.

This should work, although it may be slower.
SELECT ReservationID
FROM tblRoomAssignmentTemp
WHERE Not Exists (SELECT *
FROM tblMoneyRecieved
WHERE ReservationID = tblRoomAssignmentTemp.ReservationID
AND PaymentCategory ="Deposit")

This should be faster, but you can only construct this query in SQL view.
SELECT ReservationID
FROM tblRoomAssignmentTemp LEFT JOIN
(SELECT ReservationID
FROM tblMoneyRecieved
WHERE PaymentCategory ="Deposit") as qDeposit
ON tblRoomAssignmentTemp.ReservationID = qDeposit.ReservationID
WHERE qDeposit.ReservationID is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 6/15/2011 4:51 PM, David-W-Fenton wrote:

Quote:

SELECT tblRoomAssignmentTemp.ReservationID,
tblMoneyReceived.PaymentCategory
FROM tblRoomAssignmentTemp INNER JOIN tblMoneyReceived ON
tblRoomAssignmentTemp.ReservationID =
tblMoneyReceived.ReservationID WHERE
(((tblRoomAssignmentTemp.ReservationID)>0) AND
((tblMoneyReceived.PaymentCategory)="Deposit")) ORDER BY
tblRoomAssignmentTemp.ReservationID;