MS Access Queries: The Cartesian Product Effect – Working With Non Table Joins in Access
If you ask an Access database developer what are the key strengths to this application and why don’t they use Microsoft Excel instead, you will probably hear at least two things. The first being MS Access queries and how flexible it is to ask questions of your data and the second would be how easy it is to take advantage of RDBMS (Relational Database Management System) or in other words, joining tables together.
In this post, I want to focus more on the latter and talk about table joins or to be more accurate non-table joins and why you may want to consider applying this infrequent technique.
The process of having two (or more) source files (normally being two tables) for a query that have no join between them is known as the Cartesian Product which simply takes each record from table A and assign it to each record in table B by multiplying the two recordsets (hence the ‘product‘ of the two).
Of course when creating queries with two or more tables, you normally (and in most cases) have them joined altogether leaving no stand-alone table which will cause the Cartesian Product effect. With the added benefit of setting primary and optionally foreign keys, the relationship becomes a solid foundation for your reports.
In addition to a join between two or more tables, you can also control the type too between the inner or outer (left or right) joins which will reveal unrelated recordsets.
MS Access Queries: The Cartesian Product Effect – How To Create It
Imagine you wanted to show a permutation of all record instances for date, month and year which have been stored in separate tables where the ‘Days‘ table contained 31 records (for each day of a maximum month), the ‘Months‘ table for each month of the year (12 records) and the ‘Years‘ tables containing as many years as needed for the database process (which in this example I’m using 6 records (2010 to 2015).
Now the total product of all records between the three tables should yield 2,232 assuming there are 31 days for each month (which of course that’s not true!) but keeping it simple, this total is the product of using the formula of 31 x 12 x 6 = 2,232.
The SQL statement for the above looks like:
SELECT tbl_Days.Day, tbl_Months.Month, tbl_Years.Year
FROM tbl_Days, tbl_Months, tbl_Years;
Notice there are no joins of any description and the more non-related tables there are the bigger the product effect.
In reality, this example is useless unless of course you add filters to exclude months that end with either 28, 29 or 30 days which will mean a way to suppress non valid dates. There are various ways to do this and ignoring leap years for now, you could add a calculated field with an expression as the below image shows:
The benefit of using the Cartesian Product effect versus have a single table with all valid dates for a six year period (as with this example) becomes clear in the sense that you end up with less physical data helping to improve the performance and reduces data maintenance when you wish to add a new year which in this case would mean adding one record to the ‘Years‘ table and not having to populate 365 records for valid date range instance.
MS Access queries are generally used with relational tables and this post shouldn’t be endorsed to not continue to do so but on some occasions they just may well be a reason to this infrequent approach.
To learn more about queries and joins, check out my eBooks that will cover the essentials in Access database design showing plenty of examples to help expand tour knowledge.