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!
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
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!
|
|