MS Access WHERE NOT IN not working

Question

I have two databases, one for holding book records and one for holding loan records.

The SQL below only doesn't work properly. It gives me the results where books have been returned, but not books that haven't been part of a loan.

SELECT Book.BookID, Book.ISBN, Book.Title, Book.AuthorID, Book.SubjectID, Loans.[Returned?]
FROM Book INNER JOIN Loans ON Book.BookID = Loans.BookID
WHERE ((Book.BookID) Not In (SELECT DISTINCT BookID FROM [Loans] WHERE BookID IS NOT NULL)) OR (((Loans.[Returned?])=True))
ORDER BY Book.Title;

What have I done wrong?

EDIT: I want the query to collect books that are currently available, for a report I want to produce. Available books includes books that have been returned and books that haven't been borrowed.

Answer 1

If I understand right, you want all books not in the Loans table and books in the Loans tabled marked as returned. Try this:

SELECT 
  Book.BookID, 
  Book.ISBN,
  Book.Title, 
  Book.AuthorID, 
  Book.SubjectID, 
  Loans.[Returned?]
FROM Book 
LEFT JOIN Loans ON Book.BookID = Loans.BookID
WHERE 
  (((Loans.BookID) is NULL)) 
OR 
  (((Loans.[Returned?])=True))
ORDER BY Book.Title;

Answer 2

It sounds like you need a simple left-join. Starting with your master "Book" table of all possible books, do a LEFT JOIN to a subquery. The subquery is by book, getting a count of how many are STILL OUTSTANDING (ie: not returned). If they are returned, they are available to be re-loaned out. If still out, it will have it's count max() set to 1. The group by / having will only keep those books STILL OUT ON LOAN.

So by doing the left-join by matching book ID, you will only get those from the master book list that DO NOT HAVE a record remaining in the StillLoanedOut subquery via the StillLoanedOut.BookID IS NULL.

SELECT 
      B.BookID, 
      B.ISBN, 
      B.Title, 
      B.AuthorID, 
      B.SubjectID
   FROM 
      Book B
         LEFT JOIN 
         ( select L.BookID
             from Loans L
             group by L.BookID
             having MAX( case when L.Returned then 0 else 1 end ) = 1 ) as StillLoanedOut
         ON B.BookID = StillLoanedOut.BookID
   WHERE 
      StillLoanedOut.BookID IS NULL
   ORDER BY 
      B.Title;

Since I don't know how your loan table is structured, this approach should work in case there are multiple records showing a loan history of any particular book. By doing a max(), if a book is loaned out 8 times, at most only 1 (the most recent) time is not returned... the previous 7 instances must have been returned to be loaned out again.