Most queries will involve working with multiple tables. To combine all values from one table with all values from another table, you can use a cross join: simply selecting the relevant fields from the two tables.

SELECT c.CourseID, c.Term, s.StudentID, s.Year
FROM Courses as c, Students as s
WHERE s.Year=2016;

Unless there are only a few values in the two tables, this is usually a terrible idea, if only because the result set could be huge. It’s usually safer to use join clauses.

Join Clauses

Join clauses are used to combine two tables based on a common field between them. INNER JOIN is the most common type, returns matching entries available in the two tables.

For instance, this query will return entries from the students and registrations tables that match based on the student ID:

SELECT *
FROM Students S INNER JOIN Registrations R
ON S.StudentID = R.StudentID

LEFT JOIN and RIGHT JOIN allow you to keep all the entries of one of the two joining tables. Entries with no match will have null values in the corresponding fields.

For instance this query will match students to the registered courses.  Students with no registered courses will have a null value in the field CourseID and will be returned in the result set.

SELECT S.StudentName
FROM Students S LEFT JOIN Registrations R
ON S.StudentID = R.StudentID
WHERE R.CourseID IS NULL

To cross more than two tables you need to use multiple joins in sequence. Using parentheses helps to avoid errors.

SELECT S.StudentName, R.CourseID, C.CourseName
FROM ((Students S INNER JOIN Registrations R
ON S.StudentID = R.StudentID)
INNER JOIN Courses C
ON R.CourseID = C.CourseID)

The FULL OUTER JOIN combines the result of a left and right joins. MS Access doesn’t has this clause available.

You can find other examples with JOIN clauses here and a discussion of the types available in MS Access here.