Hierarchical Relationships in Access

What about when a record in a table is related to another record in the same table? such as a contact to a parent or grandparent? or a part that contains another part, that contains another part, that contains another part, that contains a little baby part. or a category that’s part of another category? These kinds of relationships are called hierarchical relationships, and often appear in databases. Hi, this is crystal There are various ways to store hierarchies. The simplest form is where each item has a single parent, and each parent can have zero (0) or more children. This database stores questions and answers.

01:02 - Each question has a category This question, “What is 1 + 1?”, is a Math question, and specifically, an Addition question. “Math” and “Addition” are both categories “Addition” is part of “Math but Math isn’t part of Addition. CatID is an AutoNumber and the primary key of the Category table. The CatID for Math is 1. The field to store a parent topic is called CatID_ In the Addition record, by storing 1 in the parent category, any Addition questions can also be categorized under Math. As you add more questions and get more categories, you might change how categories are related. Arithmetic has been added. Its parent category is Math.

02:07 - Now the parent category of Addition is changed to Arithmetic. To show each category with its parent category, you can make a query. From the ribbon, Create, Query Design I like to drag tables from the Navigation Pane and resize the fieldlists to see everything. Double click fields in the fieldlist to put them on the grid. Category is a text field with the name. CatID is the primary key The CatID for Addition is 2, so 2 is the criteria.

02:51 - The Datasheet view shows one record, for Addition. In the Design view, add another copy of the Category table. The names, or aliases, of the tables must be unique. Since the Category table is already there, Access appends “1” to the end of the name for the copy. How are these two fieldlists related? In the Category table, CatID is the parent category It’s related to the CatID primary key in another record. Drag from CatID_ to CatID.

03:39 - Edit the join property to show all records from Category so you see records even if they don’t have a parent. The line starts from the main table The arrow points to the less-important table, the one with records that may, or may not, be there. A child doesn’t have to have a parent. Now we add Category from the parent table to the grid. Look at the datasheet view. Column names have to be unique. Since Category is coming from more than one source, Access adds the source name dot to the front of the duplicate fields. Let’s fix that. Go back to design. Give the parent category an alias of Category1.

04:34 - The calculated field name, or alias, is anything before a colon in the Field cell on the QBE grid. QBE is Query By Example. Look at the datasheet view. That works. Each field has a unique name Now how do we add another level? Go back to the design view Drag another copy of the Category table over Again, the relationship goes from CatID_ in the lower table to the CatID primary key in the parent table. Put its Category on the grid I’m double-clicking the fieldname in the fieldlist. Shift-F2 in the Field cell to zoom, add an alias of Category2. Edit the Join Properties of the link line to show all records from Category_1.

05:38 - Keep the arrows going in the same direction. The datasheet view shows our Addition category, its parent category of Arithmetic, and its grandparent category of Math. Two levels up might not be enough though. How do we add another level? Go back to design. Drag another copy of Category. Link on the parent CatID_ in the lower table to the CatID primary key in the next table. Put the Category on the grid Shift-F2 to zoom. Add an alias of Category3.

06:22 - What if we don’t change the join properties? When you try to show the Datasheet View, Access gives an error message that there are ambiguous outer joins. That’s because the last link line needs to have an arrow. Change the join properties to show all records from the left table, Category_2. Look at the Datasheet View. Addition doesn ’t have a category 3 levels up but shows the other categories above it. Save this query as qCatTree3 Later, we’ll remove the criteria. When you’re building a query, its good to test it with data that you know you have. The most important thing that Access stores for a query is the SQL statement. SQL stands for Structured Query Language. Access doesn’t show it to us in a very readable way so I wrote some code to make it more clear. Right now, I’m running that code in Word, and just replacing the SQL statement. Category and CatID are coming from the category table Category from the Category_1 table is called Category1.

07:51 - Category from the Category_2 table is called Category2. Category from the Category_3 table is called Category3. The FROM clause lists the Category table 4 times. The relationships use Left Joins, so there will be an arrow on each link line in design view. The WHERE clause limits the records to showing information for CatID=2 only. That’s the Addition category. It was limited for now since we knew what the results should be. Back on the question and answer form, we haven’t picked a category for our question yet This list shows each category name but doesn’t show how that category is related to other categories. I’ll pick ‘Addition’ for the category. If I click the button to edit the category, I see that the parent category for Addition is Arithmetic. Let’s go to the design view of this form and get the combo box to show more columns. Click the combobox control to select it. If the property sheet isn’t showing, turn it on.

09:22 - The Control Source is CatID That’s a foreign key to CatID in the Category table. Its a combo box so its RowSource can come from another table, or from a query, or SQL statement. This RowSource is an SQL Statement. The Builder,… , or Ctrl-F2 shows the design view and the grid. First, I’ll replace the SQL statement with the one I formatted by running VBA in Microsoft Word. Shift-F2 to Zoom. Notice how variations of the F2 key are edit, and other ways of editing? Just a little tip to help you remember it.

10:14 - In this case, we don’t want any criteria, so I’ll delete the WHERE clause Here we are. There are a couple things to change, but this is a good start. OK to change the SQL statement. Now we’ll look at it with the Query builder. This is the graphical representation of the SQL Statement. There is our Category table, and 3 more copies of it, each using the parent ID to link to the primary key of another record. The fieldlists are arranged so the arrows all point from the left to the right. The grid shows the Category and CatID fields from the Category fieldlist. Then come the parent category, Category1, the grandparent category, Category2, and the great-grandparent category, Category3. The Datasheet view shows what we created to show levels for Addition, minus the criteria The data doesn’t appear to be in any particular order and thats because the SQL statement didn’t have an ORDER BY clause. Originally, it was just getting one record, but we deleted that criteria.

11:41 - More importantly, however, the combo box that this is a row source for, will collect a CatID, and display other columns with information. Although you can change the BoundColumn property of a combo box to be something other than 1, I don’t like to do that. I would rather change the order of the fields in the SQL statement. Let’s go the the Design view and make CatID be the first column. When looking at the Datasheet View, I like to set the ColumnWidths. NotePad is handy to write them down as you’re looking at them. What is the ColumnCount? 1,2,3,4,5 What are the column widths? The first column will be hidden so it will be zero Lets say each of the Category columns needs 2 inches, which is about 5 cm. Its a good idea to figure out column count and column widths while you’re looking at the data In Notepad, copy the ColumnWidths and remember the ColumnCount. On the property sheet, fill ColumnCount - that’s 5. Press TAB paste the ColumnWidths constructed in NotePad, TAB TAB TAB to the ListWidth.

13:12 - Add the column widths, 0+2+2+2+2=8, and add 0.2 inches for a scrollbar, or about half a centimeter. So that makes it 8.2 Save See how it looks in the form view Some of the column widths could be changed. Let’s try 1.5” for the first column, 1.2” for the next 2, and 1” for the last column TAB TAB TAB to the ListWidth 1.5+2.4 is 3.9 +1 is 4.9 + 0.2 for a scrollbar is 5.1 I think I’ll put the first column width back to 2 That makes the ListWidth half an inch wider, so 5.

6” 14:16 - The combo box still stores the same thing, CatID. But now it shows more columns. What if you want to go the other way? Create a new query on the Category table. Put CatID and Category on the grid. Let’s limit this to Math. The CatID for Math is 1 so that goes in the criteria cell under CatID. Now add more copies of the Category table. This time we’ll go one level deeper. Once the tables are there and resized, drag the relationships This time, they go from CatID in the main table the the parent CatID_ in the copy.

15:10 - Set the Join Properties to show all records from Category. Continue this same pattern for the rest of the fieldlists. Now let’s put Category from each table on the grid. On the grid, we see that 5 columns are using a field with the same name. Let’s assign aliases. To differentiate from the aliases we used before, the name for the child will be SubCat1, short for sub-category. The next level will be SubCat2.

15:56 - The third level will be SubCat3, and the last level is SubCat4. Sort in Ascending order by each category. That looks good for Math. Now that we’ve tested our logic, let’s change this query to show all the main categories and what’s below them. So we won’t put criteria under CatID. What IS the criteria to get just the main records? In the Category table, the top-most parent ID wouldn’t have a value, so add CatID_ from Category to the grid Put “null” in the criteria, and uncheck the Show box since we don’t need to see an empty column. Notice that Access corrected the criteria to “Is Null” Now we see our hierarchical data from the top down. This database is still being built. As more categories are needed, they can be added.

17:14 - I made a video showing you how to use the NotInList event to easily add new categories. The link is in the video description In summary, you learned how to create a simple hierarchical structure in Access by putting a foreign key in the same table as the primary key. To make it clear that this is a parent ID, I like to put an underscore after the primary key fieldname for the foreign key fieldname. You saw how to create a query from the bottom up showing upper levels for the categories We also created a top-down query to show all the main categories, and sub-categories below them. We looked at an SQL statement and assigned aliases You also saw how to modify the RowSource of a combo box to show more columns.

18:16 - You can download code, such as NotInList, tools, look at videos, and lots more from MsAccessGurus.com If you’re building a project, I’d love to connect and help you make it successful. Go to MsAccessGurus.com and contact me. I look forward to hearing from you. Thanks for joining me. Through sharing, we will all get better. .