How do I show the data that isn't there?

Now isn't that an interesting question. It's not such an unusual request: which students haven't submitted a paper? which departments haven't sold any coats today? Or the summary report flavour: when I run my monthly sales some departments don't show up- I want to see a zero where there have been no sales.

To get the answer to a question like this in Access usually involves two steps: find out what "everything" is, and then find out what part of "everything" you don't have.

Let's tackle this one with a students and papers example. Let's say we have three tables:

tblStudents -StudentID -StudentName -etc...

tblPapers -PaperID -PaperName -etc...

tblStudentPapers -StudentID -PaperID -etc...

To find all the papers I should have, I need all the possible student/paper combinations. You do this with an unusual query. Here's the SQL:

SELECT StudentID, PaperID FROM tblStudents, tblPapers;

The query has no join specified- that's why it gives you every combination. I think this is called a Cartesian Product.
For our example let's create the query above and call it qryAllStudentPapers.

Now you just need to use that query and tblStudentPapers and do an "unmatched query". The wizard can create simple unmatched queries, but this one joins on two fields so I don't think it can do it. Here's the SQL. (You can type the SQL into the SQL window and then see how it looks in the design view, then next time create it in the design view from scratch.)

SELECT qryAllStudentpapers.StudentID, qryAllStudentpapers.PaperID
FROM qryAllStudentpapers LEFT JOIN tblStudentPapers ON 
  (qryAllStudentpapers.PaperID = tblStudentPapers.PaperID) AND   
  (qryAllStudentpapers.StudentID = tblStudentPapers.StudentID)
WHERE tblStudentPapers.StudentID Is Null;

I won't stretch this blog out with explanations of LEFT JOINs and how unmatched queries work- you can read up on those on your own. The key here is the two step process- a cartesian product to find what "everything" is, and then an unmatched query to find what part of everything is missing in your data.   #