Hierarchical Relationships in Access
Channel: LearnAccessByCrystal
Date: 01/11/2020

Hierarchical relationships are common in real data. This lesson shows you how to create a simple hierarchical structure in an Access database by putting a foreign key in the same table as the primary key.

We'll make 2 queries to show the hierarchy by using one table  multiple times: 1) show categories and parent categories, and 2) show main categories and sub-categories. When you're building a query, its good to test it with data that you know you have, so we use criteria to get the logic right.

We talk about SQL and using aliases, and how to set up relationships

We also modify the RowSource of a combo box to show more columns.

(00:48) simple hierarchy
(01:25) CatID is an AutoNumber and the primary key of the Category table.
(02:07) change the parent category
(02:14) Make a query to show each category with its parent category,
(03:39) Edit the join property
(03:50) Link lines
(04:27) Assign an alias
(04:43) QBE is Query By Example.
(04:53) add another level
(06:16) Shift-F2 to zoom.
(06:31) error: ambiguous outer joins
(07:14) The most important thing that Access stores  for a query is the SQL statement.
(07:21) SQL stands for Structured Query Language.
(08:07) The FROM clause lists the Category table 4 times.
(08:12) Left Joins
(08:22) WHERE clause limits the records
(09:10) on the form, get the combo box to show more columns.
(09:17) turn on the property sheet
(09:42) RowSource is an SQL Statement
(09:45) Builder is Ctrl-F2
(10:04) F2, Shift-F2, Ctrl-F2
(11:53) BoundColumn property
(12:00)  change the order of the fields
(12:16) use NotePad to write ColumnCount and  ColumnWidths
(13:08) ListWidth
(14:28) Make a query to show categories from top-down
(15:39) SubCat is sub-category
(16:05) Sort in Ascending order by each category.
(16:49) the Show box
(17:16) use the NotInList event to easily add new categories
(17:24) summary

related video:
Add New Data with NotInList VBA for Combo box
https://www.youtube.com/watch?v=gwZt5pb62K4

For remote training, let's connect! Email:
Training AT msaccessgurus.com

Visit my website for free code, tools, articles, and consulting
http://www.msaccessgurus.com

Please share your comments, thanks.

kind regards,
crystal

Remote Training and Programming

Let's connect and build your application together!
http://msaccessgurus.com/training.htm