Cross Joins In Access
Have you ever wanted to display all combinations from two tables
regardless of the values? For instance, imagine that you want a report that
shows every combination of employee and training course even if they haven’t
attended anything! In SQL, this type of join is called a cross join. In
Access, it looks like no join at all
!
The image above shows a normal (INNER) join of the Employees and Courses
tables through the CompletedCourses table. This report will show all of the
employees who have completed courses, and which courses they completed. If
an employee hasn’t completed any courses, they simply won’t show up in
the report. We need to find those employees and get them to attend courses—this
type of a query won’t help us at all.
The joins below (called an OUTER joins) will show us either all employees
(including those who have not completed any courses) or all courses
(including those with no attendees) but not both!
So how do we get all employees AND all courses? Since we double-clicked
on the lines connecting tables in the query window to change our inner joins
to outer joins, you’ll be tempted to do the same to create the cross join.
That’s not the case.
In fact, you create a cross join to add the two tables you want every
combination from in design view in the query window and don’t join them at
all! It looks like the window below.
What those lines between tables tell the SQL compiler is what
combinations to leave out. In first case, leave out everything that doesn’t
have a match. In the other cases, leave out everything from the one table or
the other that doesn’t have a match. A cross join says “Don’t leave
out anything—give me it all.” This
sample database is available for
download so give it a try!