Compare Two Tables / Return a List of What is Missing

[MichaelA]

I think I have this just about done. Thank you! I have one new function I'd like to create in my grade book. I have tables and queries which show student's completion of course assignments , a percentage of the course complete. What I would like to do now is a query that will compare the list of course assignments (table: assignments) to the students grades (table: Grades) which show which assignments have been completed for each course and their score for each assignment. What I would like to see, is a list of assignments which have to been completed, assignments for which there is no grade. Some of the assignments can be accomplished out of order, so I need to see what is and isn't done. I'm attaching a zip file of the database in case you need to see what I'm working with.



Thank you,
Michael

[June7]

If you want to know what is not yet accomplished then you a dataset of all possible combinations of students/assignments. This dataset can be generated with a Cartesian type query, like:

SELECT Assignments.ID AS AssignID, Students.ID AS StuID FROM Assignments, Students;

Without a JOIN clause, every record of each table will associate with each record of other table resulting in all possible combinations.

Compare that dataset with Grades dataset. Build FindUnmatched query with compound join on both ID fields.

[MichaelA]

Thank you! I got the unmatched query to bring up courses and assignments the way I expected but I can't seem to get the students name associated with the missing assignments.

[June7]

Post the attempted queries.

Did you first build Cartesian query I suggested?

You can include student name fields in that query.

[MichaelA]

Yes, I did create it and it includes StudentID and works fine but when I compare that dataset with Grades, if any student did a particular assignment, it makes it appear as if everyone did.

[June7]

Finally got around to downloading db. Opening immediately errors on:

Private Sub Form_Load()
   DoCmd.GoToRecord , , acNewRec
End Sub

Use Open event instead. Or use the form DataEntry property set to Yes.

Why don't you save Assignment ID in Grades instead of the descriptive text?

SELECT Query1.Students.ID, Query1.FirstName, Query1.LastName, Grades.Grade, Query1.Assignments.ID, Query1.Assignments
FROM Grades RIGHT JOIN Query1 ON (Grades.Assignment = Query1.Assignments) AND (Grades.[StudentID] = Query1.[Students].[ID])
WHERE (((Grades.Grade) Is Null));

[MichaelA]

Ah, yes! This finally worked. I had to tweak a few things but my database is now everything I wanted it to be. (for now) I think that my biggest problems all along have been the test for assignments instead of ID in Grades. Thank you!