A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query, I will now try to describe in detail one example of a practical use of a Cartesian Query.

As luck would have it, while I was preparing the materials for this blog, an UtterAccess member (Kamulegeya) posted a question in the forum that's a perfect fit for this topic. So, we will use his situation as the example for this blog.

In a nutshell, he wanted to create a report that displays the number of days a room was reserved or "booked" for a given month.

For instance, let's say we have the following data:

Room Reservations Table



And we want to have a query that looks like the following:

Dates rooms were occupied



Basically, we want to produce a record for each day that the room was reserved or occupied so we can easily count them. So, how do we do that? Enter, the Cartesian Query.

As we have learned before, a Cartesian Query involves more than one table. So, we'll need another table to join with "tblBookings" to produce the in-between dates for each room reservation.

For this technique, we'll create a table with the following structure:

Table of numbers



Note: Some developers refer to this table as a "Tally" table.

And we just need to populate it with sequential numbers starting with zero up to the largest number required for the result of the query, which in this case is the longest time a room could be reserved, as shown below:

Populate tblNumbers with sequential numbers



Now that we have our two tables, we just need to construct the Cartesian Query. The SQL statement we need looks like this:

SELECT tblBookings.BookingID, tblBookings.RoomID,
          DateAdd("d",[TheNumber],[CheckInDate]) AS DateOccupied
FROM tblBookings, tblNumbers
WHERE (((DateAdd("d",[TheNumber],[CheckInDate]))<[CheckOutDate]))
ORDER BY DateAdd("d",[TheNumber],[CheckInDate]);

If you run that query, you will get the result shown in the second image above. You can now use this query to produce the number of days/nights that the room was reserved for each  month either by using a Totals query or a Crosstab query.

Note: We can also use a table of dates and a Non-Equi Join to produce the same result but it's not nearly as flexible as using a Cartesian Query. In fact, you can re-use tblNumbers in other Cartesian Queries to suit various situations.

To see the actual discussion at UtterAccess on this topic, click here.





Original post date: February 3, 2013