MS Access 2003 SQL Not Exists question

#11

On Thu, 16 Jun 2011 08:39:45 -0400, John Spencer
<JSPENCER (AT) Hilltop (DOT) umbc> wrote:

Quote:

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

Thanks John. I'll try that too.

-paul

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

MS Access 2003 SQL Not Exists question

comp.databases.ms-access comp.databases.ms-access

Re: MS Access 2003 SQL Not Exists question - 06-16-2011 , 12:13 PM