Argee's Access 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.