Working with Null Values

Sometimes, in a set of data, there are values that are missing. These missing values have a special name in databases. Fields that are empty, containing no value, are called null values. A null value is the database's way of saying "This field has no value." It does not mean 0 (zero) or "" (an empty string). It literally means nothing or no value.

Since null values are special to a database, we have special keywords to sort, filter, and find null values:

• Is Null - returns all records with a null value in the specified field.
• Is Not Null - returns all records with any value in the specified field.

Since we want to find faculty teaching in a particular semester/year (SU18) but also those who are not assigned to teach in any semester/year (null), we need to include null as a criteria in our query.

Step1. Return to Design view.

Step2. To include null values for SemesterYear, in the Criteria field for the field, after "SU18", type:

or is null Tab key

NOTE: The keyword is null is not case sensitive. Access will capitalize it to match the following image once we move our focus from that field.

The finished query should look like this:

NOTE: Your fields may be in a different order. As long as they are all present and have the appropriate criteria, your query is correct.

Step3. Save qryCoursesSectionsAndAllFacultySummer18 and view the results in datasheet view.

You should see 62 results and some rows will have null (empty fields in datasheet view) values for CourseCode, SectionNumber, and CourseTitle. Diana Thibodeaux should now appear in our result set.

Step4. Close qryCoursesSectionsAndAllFacultySummer18.

Building Relational Queries

Let's build a query on our own. Let's find the names of all of our departments and if the department is offering any courses, show the course codes and course titles.

This section has less guidance than the previous sections. If you would like a walk-through, watch the video Building Relational Queries.

Step1. Create a new relational query that finds all department names and if the department is offering any courses, show the course codes and course titles.

Step2. Save the query as "qryAllDepartmentsAndCourses".

You should see 100 records. The English department should appear without any courses. They are a new department coming on board and haven't had their courses data added to the database yet.

Step3. Close qryAllDepartmentsAndCourses.