A common database issue in the world is the need to store lists of related items.  This might be a Bill of Material (or BOM) for a business application to describe all the parts and assemblies needed to ship a particular product, or it might be something like meal planning for multiple diners with recipes and ingredient lists.

While there are a few different techniques out there to accomplish this, with Microsoft Access I would recommend using a nested information technique called an “Adjacency List”.  This is mainly because recursive queries are not native or supported by Access and this particular technique allows us to create them within this limitation.

Let’s consider part of a meal planning database as an example:

This particular data will contain nested relationships between various components.  Consider our component table:

Table: RecipeItems

Columns: ID (Primary Key), ComponentName, ComponentWeight (I would make this the weight of a single component, like 1 carrot or 1 cup of sugar)

Data in RecipeItems Table:

1, Cooking Oil, 0.148

2, Flour, 0.325

3, Sugar, 0.507

4, Baking Powder, 0.051

5, Carrots, 0.413

6, Eggs, 0.128

7, Salt, 0.010

8, Chocolate, 0.754

9, Base cake batter, 0

10, Cake batter, 0

11, Base carrot cake, 0

12, Carrot Cake w/chocolate, 0

Note that the weights of items that will be made up of sub-components in the list are set to zero.  This is because our queries will calculate the weights of the higher level components using the base weights of their ingredients.

Next consider our relationship table which would contain the relationships between the components entered into the component table

Table: RecipeRelationships

Columns: ID (Primary Key), ParentID, ChildID, Quantity (This will be used to multiply against the weight of individual components for different recipes)

Data in RecipeRelationships Table:

1, 12, 11, 1

2, 12, 10, 1

3, 12, 8, 1

4, 12, 7, 1

5, 11, 6, 1

6, 11, 5, 1

7, 10, 9, 1

8, 10, 4, 1

9, 10, 3, 1

10, 9, 2, 1

11, 9, 1, 1

Note that this data is simply defining child / parent relationships at each level.  This recipe is for a carrot cake.

Creating queries

The next step is to create queries for each level of the recipe for the finished product you are looking at (Carrot Cake w/chocolate, #12 in this case).

We will first want to build queries for each level of the Recipe.  If you are going to have more levels, you will need more queries, one for each level, but in this case we have three levels, so let’s use that to see how we would build our queries to get all the relationships for an entire tree of a finished product and see how each query builds on the next:

Level 1, containing just the sub products and raw materials for the finished product (12) would contain the following SQL:

SELECT ParentID, ChildID FROM RecipeRelationships WHERE ParentID=12

Level 2, containing components and raw materials of the sub products of 12 (10 and 11 in this case) would contain the following SQL:

SELECT R1.ParentID, R2.ChildID, Sum(R1.Quantity * R2.Quantity) AS TotalQty

FROM RecipeRelationships AS R1, RecipeRelationships AS R2

WHERE R1.ChildID = R2.ParentID AND R1.ParentID = 12

GROUP BY R1.ParentID, R2.ChildID

Level 3, containing the raw materials in the component for the finished product would contain the following SQL:

SELECT R1.ParentID, R3.ChildID, Sum(R1.Quantity * R3.Quantity) AS TotalQty

FROM RecipeRelationships AS R1, RecipeRelationships AS R2, RecipeRelationships AS R3

WHERE R1.ChildID = R2.ParentID AND R2.ChildID = R3.ParentID AND R1.ParentID = 12

GROUP BY R1.ParentID, R3.ChildID