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.
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.
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".
Step3. Close qryAllDepartmentsAndCourses.