Searching for null values over several fields.

[Robbyp2001]

Good morning folks.

I have a query that has to look at the contents of five fields in the same table to determine if any or all has a null value. I can underrstand how to return records that all five fields are null and all five fields are not null, however I need to know if any records have at least one or more of the five equal to null.

MTR Teacher Comments
MTR Homework Grade
MTR Behaviour Grade
MTR Current Attainment
MTR Target Level

In other words the query has to show [MTR Teacher Comments]=null and/or [MTR Homework Grade]= null and/or [MTR Behaviour Grade]=null and/or [MTR Current Attainment]=null and/or [MTR Target Level]=null

Is this possible to do in a query? If anyone could help with either suggestions how to query or how to write code that will do this, I would be extremely grateful, becaye I am stumped!

Thanks folks

Rob

 

[mahenkj2]

Please see attached example.

 

[Robbyp2001]

Dear mahenkj2

Many thanks for your prompt response. I have already tried to do the same thing as your sample query and unfortunately I found that it simply returns all of the values in the table no matter what the content of the field. I have deleted the columns in the query, leaving only the 5 that I need and tried the same method. Same results I'm afraid.

Rob

 

[mahenkj2]

should work, can you post SQL with desired sample output.

 

[Robbyp2001]

Hello mahenkj2

I don't know if this is what you asked for but it is the SQL for the query that is not returning the values expected. It should have returned 1 record (see below) because the [MTR Behaviour Grade] is null. It returned a load of records instead. I have attached the query for your consideration. I hope you can make some sense of what I've sent you. But as I look at it now, there's just confusion.

Rob

SELECT [Reports Table].[MTR Teacher Comments], [Reports Table].[MTR Homework Grade], [Reports Table].[MTR Behaviour Grade], [Reports Table].[MTR Current Attainment], [Reports Table].[MTR Target Level]
FROM [Reg Class Table] INNER JOIN ([Yeargroup Table] INNER JOIN ([Teacher Title Table] INNER JOIN ([Teacher Table] INNER JOIN ([Subject Table] INNER JOIN ([STUDENT LIST] INNER JOIN [Reports Table] ON [STUDENT LIST].ID = [Reports Table].[Student ID]) ON [Subject Table].[Subject ID] = [Reports Table].[Subject ID]) ON [Teacher Table].[Teacher ID] = [Reports Table].[MTR Teacher ID]) ON [Teacher Title Table].[Teacher Title ID] = [Teacher Table].[Teacher Title ID]) ON [Yeargroup Table].[Yeargroup ID] = [STUDENT LIST].[Year Group ID]) ON [Reg Class Table].[REG ID] = [STUDENT LIST].[Reg ID]
WHERE ((([Reports Table].[MTR Teacher Comments]) Is Null)) OR ((([Reports Table].[MTR Homework Grade]) Is Null)) OR ((([Reports Table].[MTR Behaviour Grade]) Is Null)) OR ((([Reports Table].[MTR Current Attainment]) Is Null)) OR ((([Reports Table].[MTR Target Level]) Is Null));
Reports TableYear Group IDMTR Teacher IDSubject IDStudent IDdate()Report IDReg IDMTR Effort Grade IDMTR Attitude to Learning GradeMTR Homework GradeMTR Behaviour GradeMTR Current AttainmentMTR Target LevelMTR Target GradeMTR Exam MarkMTR Exam GradeMTR Teacher CommentsReport Type IDReport Type ETRMT PE PraticalMT PE TheoryETR Effort Grade IDETR Attitude to Learning GradeETR Homework GradeETR Behaviour GradeETR Current AttainmentETR Target LevelETR Target GradeETR Exam GradeETR Exam MarkETR Teacher IDETR Teacher CommentsETR Report Type IDSession IDInter Teacher ID789453518/09/20128559491121
45


The Year 7Art & Design course teaches students understanding of the formal elements of art which are essentials to strengthening the student’s ability. Observational and drawing techniques develop confidence in the student’s work and it is this factors coupled with the [personal response to topics that allows student’s to develop their own individual outcomes. Student assessment is not based on final outcome only but equally on the developmental process from concept to planning to skills to final outcome.

Ali is a friendly member of the group who finds some of the new painting and drawing techniques challenging. He shows an interest in the subject and enjoys learning techniques with new media. With continual practice and understanding of skills taught Ali should improve the accuracy of his drawing, thus improving his level in the coming term. He is able to communicate about his work using appropriate art language. Ali should aim to produce his best work at all times.

Please note: Levels in Art have been amended to the new UK curriculum requirements and standards.12000







89
2189

 

[Robbyp2001]

Dear mahenkj2

Your method was correct. I now have this working, many thanks for your help. You are a star!

Rob

 

[mahenkj2]

Happy to help.