Placeholder Records

I have always recommended against "placehoder" records (with possible exceptions), but someone in the forums recently asked me "why?" So, to explain my reasoning, I have decided to write an article about it.

What is a "placehoder" record?

First, we'll need to define what I consider as placeholder records to help define why I oppose them. To do that, I will try to use some concrete examples of what I consider as placeholder records (other developers may have a different definition of what is a placeholder record).

My definition of placeholder records are records created in advance and stored in the table to represent future records. In other words, these records are created in anticipation of possible records that may or may not be needed in the future. For example, let's say you have a database to track class attendance. If a class will have a total of ten sessions, you might be tempted to create ten records, in advance (at the time of enrollment, for example), per student to make it easier to mark if a student attended each session. They are considered placeholder records because you are creating records to represent future class sessions.

Other examples of potential placeholder records may be found  in booking databases (room and equipment rentals or theater shows to track daily occupancy or usage) or property management databases (apartment or house rentals to track monthly payments).

So, why are they bad?

Well, I probably wouldn't say they are bad but more like unnecessary. Records should only be created when they are needed and not before. For example, if we take the example of the class attendance database, each attendance record should only be created when that particular class session occur. And even then, I would also recommend that you only create records for those in attendance. There's really no need to create records for the absent students, because we can always determine who they are by comparing the list of students from those who attended the class. You can also create a query to show both attendees and absent students as the record source for your form or report.

If you need to produce sequential records for use in a crosstab query or a daily status report, you have the option of using a cartesian product query as presented in an earlier blog article: A practical use of a Cartesian query. That article demonstrates the use of a Tally table, which I also prefer over creating a table of sequential dates.

If you like the convenience of having placeholder records in the table for your data entry forms, that can also be addressed by other methods such as using a temporary table or a multi-select listbox. The user can select all the records to be created from the temporary table or listbox, and then VBA code can be used to actually add those records into the table.

Conclusion

So, to summarize, placeholder records could take up space in the table when it is not actually needed. They don't necessarily violate "normalization" rules, but it's considered best practice to avoid using placeholder records. Hopefully, I have described how the common benefits of creating placeholder records can be easily addressed without actually creating placeholder records.
The next time you hear me tell someone to avoid creating placeholder records, you now know my reasoning behind it. I would like to hear other perspectives on this topic, so please leave a comment to share your thoughts.

Thanks for reading!

Original post date: October 27, 2014

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

Cartesian Query

When creating a query involving multiple tables, we can select from three types of table joins depending on the desired result of the query. Usually, we want the query to return only the matching records between the tables, in which case an “inner join” would be used.

Sometimes, the desired result is for the query to return all the records from one table with the matching records from the related table. In these cases, an “outer join” (left or right, Access does not support “full outer join”) would be the join of choice.
However, there will be times when none of these join types will return the desired result. In which case, the only thing left to do is to not use a join at all. That’s when you get a ”Cartesian Query.”

Basically, a Cartesian Query returns a set of data that include all the records from all the tables in the query with a slight difference, the number of records returned will be the cartesian product between the number of records from all the tables.
What exactly does that mean? To better illustrate, take a look at the following sample data.

Table 1

Table 2

Cartesian Query

As you can see, the query returned as many records in Table2 for each record in Table1 (3 x 3 = 9). Here’s the SQL statement used to create that query:

SELECT Table1.Field1, Table1.Field2, Table2.Field1, Table2.Field2
FROM Table1, Table2;

You will notice that there was no relationship join used between Table1 and Table2.

So, why would anyone want to use a Cartesian Query? Believe it or not, there are many valid reasons for using a Cartesian Query. For example, you can use a Cartesian Query to print a shipping label to indicate the box or pallet number within the number of boxes or pallets per shipment, or to display all possible network addresses within an IP range, or to calculate the fractional cost of a product over its life expectancy, or to generate a daily roster for student attendance or employee training or meeting.

One last note to remember when using a Cartesian Query: the resulting data set will not be updateable. So, the result of Cartesian Queries is usually used only as a data source for reports or other queries.

Hopefully, this post helped you become a little bit familiar with Cartesian Queries so that maybe next time when you need to generate records that are not normally stored in your table, you won’t be afraid to try it. Who knows, using a Cartesian Query could provide the solution you were looking for.

In my next post, I will show you one practical use of a Cartesian Query by generating all the calendar dates within a user defined range. See you next time…

Original post date: January 20, 2013