CODEWITHSUNDEEP
ms-accessjoin
M. Russ
M. Russ

Reputation: 17

How do I show blank fields in ms access queries?

I'm trying to make a query in MS Access. I have two tables that are related to each other, in which some items have a direct relationship and are listed in both tables, as well as, some items that only appear in one table.

I'm trying to make a Query to easily see the items that are not listed in the other table. (For example, I have 2 tables, a Student List, and a Book borrowing list and I want to show the student(s) that may have borrowed and have never borrowed in one query) For the students that have borrowed, it shows up fine, but for the students that have never borrowed, it removes the entire row, leaving me with a the students that have borrowed something in the past. I want to show the entire list of students, even if they have borrowed something or not, and just leave the borrow fields as an empty field in the query if they have never borrowed something.

I've tried using Is Null as one of the conditions, but that does not seem to work.

Please help. Thanks in advance.

Upvotes: 0

Views: 1584

Answers (1)

peterm
peterm

Reputation: 92785

What you need is called an OUTER JOIN. Please read A Visual Explanation of SQL Joins

If you were to list all students and the total number of books they borrowed regardless of whether they actually borrowed any you could use a query like this

SELECT s.id, s.name, COUNT(b.book_id) borrowed_count
  FROM students s LEFT JOIN borrowed b
    ON s.id = b.student_id
 GROUP BY s.id

Output:

| id | name | borrowed_count |
|----|------|----------------|
|  2 | Jane |              0 |
|  1 | John |              2 |

See how Jane never borrowed a book but is still listed

See SQLFiddle demo

Upvotes: 1

Related Questions