Finding "missing" records

[funderburgh]

I would like to craft a query that identifies the absence of records. Specifically I have a table with students linked to a table of grade levels. I need to audit for missing grade levels, i.e. students whose grade level for a specific year is absent. My knowledge of SQL is growing, but I can't sort out how to query something that isn't there. I'm grateful for any help

 

[Michael J Ross]

You need to join the tables using a left outer join, and under the criteria of student id in the grade level enter Is Null.

 

[funderburgh]

I really appreciate the reply, but I did not give a sufficient explaination of the tables. In the grade level table there are several records for one student, not one. In our school, we cannot calculate a grade level (i.e. 10th, 11th or 12th grade), it must be assigned. So a student may have a grade levels input correctly for several years, and one year accidentially skipped. The student table and the grade level table are related by the student ID. I want to query "Who is missing 2008?" - 2008 being data in a field in the grade level table record.

 

[georgedwilkinson]

select studentid 
from Students 
where studentid not in
(select studentid from Grades where Grades.year = '2008');

Is the general syntax. Of course, it all depends on your object names and types.

 

[funderburgh]

Thanks for you on-point reply, and the lesson. I had trouble making this query work until I realized that I actually did not have any missing records in the table. I must have a low expectation of my users.

 

[georgedwilkinson]

LOL. Glad you got it figured out.

 

[funderburgh]

The query is working well:

SELECT Students.StudentId, Students.[First Name], Students.[Last Name], [Forms]![Print Menu]![SchoolYear] AS SchoolYear
FROM Students
WHERE (((Students.StudentId) Not In (Select ID from [Grade Level] where [Grade Level].[School Year] =[Forms]![Print Menu]![SchoolYear])));

but I am having a confusing time with the report I have made based on the query. The report is generated through my Print Menu Form with this code:

DoCmd.OpenReport [ReportName], acViewPreview, , "[School Year] = '" & Me.SchoolYear & "'"

I have several reports based on school year working fine through this command.

When I run the query stand alone, or the report through the Print Menu, I am prompted for "School Year" - however, if I have the Print Menu open and the School Year field filled in, I can run the query and the report stand alone and not be prompted for the data. This is really confusing me because I know from the results that the query is getting the parameter correctly from the object in the form, except when I run it through the form! Any wisdom is appreciated.

 

[The_Doc_Man]

When Access prompts you for something that you thought was defined properly, the very first thing you look for is a spelling error. The next is a qualification error (i.e. you got the field name right but it is not in the higher level object you named, you spelled the container wrong.)

Case 1: You wanted MyForm.cboBox1 but used MyForm.cobBox1

Case 2: Same desire but used MyFrm.cboBox1

In either case, you misnamed the desired entity origin.

If neither of those, then there is an ambiguity somewhere and Access can't decide which of multiple possible choices to use. I.e. perhaps you have [MyData] in two tables that are joined and Access doesn't know whether to use [MyTable1] or [MyTable2]

 

[funderburgh]

Thanks for your help, I figured the problem out. In other reports I had to pass a parameter for the where condition, but in a report based on a query where the criteria parameter was pointed to an object on a form, that is not necessary. I was doing both.