|
How to Turn 1 into 10, 100, 1000, or 1,000,000: The Power of Cartesian Queries
On the original Star Trek program (the best one), there was an episode about a furry little creature called a Tribble. These Tribbles were soft, cuddly, and cute. At first, there were only a couple but, bit by bit, they multiplied. A couple of Tribbles here, a dozen Tribbles there; Tribbles on the chairs; Tribbles on the transporter; Tribbles falling from the ceiling; Captain Kirk buried under a mountain of Tribbles. The episode was called "The Trouble with Tribbles." Although they were soft, cuddly and cute, the trouble with Tribbles was that they multiplied and multiplied and multiplied until the point that they had virtually taken over the Enterprise.
In Access, you usually want one of something, but sometimes it's helpful to have a bunch. For example, let's say you're trying to make return labels for envelopes (these quaint items were used in an ancient technology called snail-mail where you put your address in the upper left-hand corner of an envelope that was physically moved around the planet).
Or let's say you had a need to make a bunch of nametags that has the name of your company, followed by the words "Hi, My Name is..."
Certainly, you can use a word processing program to produce these, but sometimes it's advantageous to do this in Access, because within Access you can make changes easily and you have a quite a bit of power to produce reams and reams of these things with very little work.
This month we'll take a look at how to do duplicates with Cartesian queries.
| The Basics |
 |
|
If you've been using Access for a while, you have probably created a query with two tables and, when you ran the query, you got back many more rows than you expected, including many duplicates. More than likely, you got these duplicates because you forgot to join your tables. When you have two tables in a query and don't join them or connect them, you end up with something you probably didn't expect. Although this might not have been wanted, the result is a feature.
Let me show you an example. Let's say that your name is Justin Case and the picture below is a copy of part of your address table (where you have a row for yourself) and let's suppose that want to print out several pages of labels with your return address.
Of course, your first step would be to go ahead and create a query such as the one below.
When you run this query, you get the following:
Now onto the magic: Below I've added a second table. The table I've selected is not important other than it has a bunch of rows. Notice that there are no links between the two tables.
When we run this query, we get the following:
We get row after row after row of a single name and address. The number of rows returned is not random; I'll come back to that item in just a bit.
|
| Behind the Scenes |
|
|
Behind the scenes, Access is applying the criteria of your query. Since there is no join, it takes each row from the first table that meets your criteria and then takes each row from second table that meets your criteria. Since there are no criteria at all for the second table, all rows from the second table are selected.
As it happens, the second table has 201 records. So if the first table had one record that met the criteria and the second table had 201 records that met the criteria (in our case, there was no criteria for the second table), it produces 201 records.
If they were two records in the first table that met the criteria, we would get a total of 2 times 201 or 402 records. If we want to really want to go overboard, our second table could just have a whole lot of rows. Another way to do it would be to add the second table multiple times, making sure there are no joins. Without the joins, the query basically will match up every record in every table that meets the criteria against every row for every table in the query. So if there are 2 copies of the second table in our query (with no joins between them) and our criteria returned 1 record from our first address table, we would get 1 x 201 x 201 or 40,401 records just by adding another copy of the second table. If we add a third copy (again with no joins), we reach over 8 million records with almost no work!
So we can use this technique to produce the same thing over and over and over again. We could create blank nametags simply by having a record that has the text that we want, bring it to a query, and adding a second table with loads ofrecords. We would get reams and reams of our blank nametags.
|
| Conclusion and a Bow to the Mathematicians |
|
|
This type of query is called a Cartesian query, named after the French mathematician René Descartes. If you've ever studied set theory or discrete mathematics, you will have encountered the concept of a Cartesian product in the Power Set unit.
So here you have a technique where we can create hundreds, thousands, or millions of things almost out of thin air with very little effort - a nifty way to go forth and multiply.
|
|
Tip of the Month - Restricting the Number of Records in a Query |
|
|
|
In this month, we learned how to create many records quite easily. But let's say we were interested in restricting the number of records to 150. The process to do this is quite simple. Open your query in design view, right click in the grey area in the top half of the query, select properties, and next to top values put in the number 150 or whatever number gives you the most happiness.
You can also restrict your output so that you get a percent of the records back rather than a specific number. This type of approach comes in handy when you want to look at a sample of your records, so you can investigate particular patterns that might interest you.
|
|
