Find missing records

[PeterWieland]

Hi,

I have a db for managing school reports. A table stores the reports, with the following fields: StudentName, Subject, Teacher, ReportText.

Every student should have 14 subject reports, and I need a query that will find who has missing subjects. I have set up a one field table with the 14 subjects in it, and tried to use the 'find unmatched' wizard, but that didn't return anything.

Any ideas?

Thanks
Pete

 

[Howlsta]

Do a totals query add the table to your query and include Studentname. Add another field to your query you could call it Number of subjects.

So in the field box put:

Number of Subjects: Studentname
select count for the total field
In the criteria put <14

The query will show all the students with less than 14 subjects

Alternatively the SQL will be like this:

SELECT YourTable.Studentname, Count(YourTable.Studentname) AS [Number of Subjects]
FROM YourTable
GROUP BY YourTable.Studentname
HAVING (((Count(YourTable.Studentname))<14));

HTH

Rich