Argee's Access Blog

In the last installment we explored the many to many relationships that exist between our various table. At that point we had not yet created the tables. This article will deal with the nuts and bolts of actually creating a table.

A word about names

The names you use when you create your database objects are important and should follow certain guidelines. Access reserves certain words for its own use. Logically these words are known as reserved words. Unfortunately some of these words are the first that comes to mind when you are trying to name fields for example. In this article I discussed at a conceptual lever, the tables we need in the Ice Cream Parlour database. If you look back to that article you will see names like ‘Name”, “Description”, and “Type”. These words all have special meaning within Access and should not be used to name Access objects.

So you will notice that the field names in the relationship diagrams in my last article are different from those I used in the conceptual diagrams. So, for example I have a field ‘strSundaeName’ rather than simply ‘Name’. What about the ‘str.’ That part of the name comes from a convention I follow to add a prefix to the name that indicates the datatype of the field. Many Access developers do not use prefixes for field names. So SundaeName would be perfectly acceptable and, in fact, preferred by many developers.

On the other hand, many developers do follow a naming convention that prefixes the names of Access objects like tables, queries, forms and reports, with three characters to signify the type of object to which the name pertains. So, for example, the Sundaes table is actually called ‘tblSundaes’.

Notice also that tables are usually plural. That is because the table contains records where each record is one example (or instance) of the subject of the table. So, tblSundaes has fields with names like strSundaeName (or SundaeName, if you prefer.)

Creating a table

There are several ways to create a table in Access. Although I don’t recommend it, you can design a table by entering data into a datasheet. Access will determine the data type for each field based on the data you enter. And unless you are careful to name each field, Access will assign such helpful field names as Field1, Field2, etc.

In Access versions prior to 2007 there are table creation wizards and in 2007 there are table templates to help you out. Unfortunately, tables created by wizards or from Access 2007 templates have field names that do not follow accepted naming standards. They include spaces in the field names. Now, while this may seem perfectly natural to you if you are an Access beginner, those spaces will come back to haunt you when you decide to enhance your work using advanced features like Visual Basic for Applications (VBA).

I prefer to create all tables using the Table Design View. Before we go there, however, we should map out the fields we will need and determine what their respective data types should be. For the sake of illustration I am going to deal with creating one of the tables we have decided we need. If you are following along and creating your own ice cream parlour database, the best way for you to learn will be for you to create the other tables on your own. I will be providing a link to my version of the database with all tables created.

Before we look at the table designer, let’s summarize the information we will need for each table. I am indicating two possible names for each field. The first name follows the naming convention I prefer to use. The second is a valid name in a format that omits the data type prefix. Whichever style you choose, it is important to be consistent throughout the database.

Sundaes - tblSundaes

Dishes - tblDishes

Sizes - tblSizes

Flavors - tblFlavors

Ingredients - tblIngredients

Those are the basic definitions of our five central table. As already noted, we will need additional tables in the database to allow us to connect these central tables together so that the database reflects the one to many relationships between them. We will also need a lookup table for types the type descriptions used by tblDishes, tblSizes, and tblIngredients.

Coming next: Using the table designer.

While an actual release date is still to be announced, the next version of Office and Access are looking pretty exciting. For an overview of what’s coming in Access, have a look a the Access team blog.

Thanks to grovelli who prodded my about the next installment in the Ice Cream Parlour tutorial, here it is.

When we last looked  at the ice cream parlour database, we had identified the main tables of the database:

• sundaes
• dishes
• sizes
• flavors
• ingredients

For now, let’s focus on how to connect these tables together rather than on the contents of each field. You may recall that we identified the relationships between most of the pairs of tables turned out to be many to many. That is, for example, one sundae may use many ingredients and each ingredient can be used in more than one sundae.

To keep it simple I am going to focus on just this one pair of tables, sundaes and ingredients. Let’s say, for the sake of argument, that we have come up with a recipe for what we are going to call Chocoholic’s Mega Gooey Sundae Delight. Because we have chosen to use autonumber primary keys when we enter this recipe title in the Sundaes table access assigns 621 as the primary key. Now, if we need to refer to our Mega Gooey Sundae Delight outside of the table all we (or Access) need to know is that this particular Sunday is number 621 in our table.

Actually the end user does not need to know the value of the primary key at all. There are ways of getting the value and causing it to be entered in other tables without actually having to actually know the number.

So far, so good. Now the Mega Gooey Sundae Delight recipe calls for these ingredients

• Chocolate Ice Cream (832)
• Chocolate Sauce (365)
• Chocolate Sprinkles (798)
• Marshmallow Sauce (922)
• Toasted Almond Slivers (305)

After each ingredient name, I have shown for the sake of this illustration, the primary key that Access assigned to each of these ingredients when they were added to the ingredients table. The actual number doesn’t matter. What is important is that no two ingredients share the same number. To put it in Access’s terms, the values are unique. That means that we can be confident that whenever we refer it item 922 in the ingredients table, we are talking about Marshmallow Sauce.

Here is the relationship diagram for these two tables:

The junction table I have been describing is tblSundaeIngredients. The infinity symbol indicates that that side of the relationship is ‘many’, meaning that each sundae in the tblSundaes can have many corresponding records in the junction table. Similarly, each ingredient can have many corresponding records in the junction table as well.

I will go through the process of actually creating the tables and relationships in later installments of this series of articles. For now, I just want to focus on what things look like when we bring together a sundae with its ingredients using a junction table.

To keep things simple, I have included in the diagram only the fields and records necessary to illustrate the point. Notice how the junction table includes only numbers and those numbers are the same as the primary keys of each of the ingredients in tblIngredients. This is typical of tables that are related. The fields lngIngredient and lngSundae are known as foreign keys. In other words the values in those fields tell us what values we are talking about in the other (or primary) table.

Next time, we will take a look at actually creating these Access tables. Following that, we will look at the process for creating relationships.

Yesterday, July 1, I received the notification that I have been recognized as a Microsoft Most Valuable Professional for the third year in a row. This is indeed a great honour, considering that there are fewer than 5,000 MVPs world-wide. One of the benefits of being an MVP is that we have a designated person (our MVP lead) at Microsoft) with whom we can correspond on a personal basis. You can read more about the MVP program on the MVP website.