Help, how to arrange hierarchical data in tables

[Jim777]

Hi,

I am having so trouble understanding how I should organise my data and I would be grateful for some help.

I have several items of law. Each item is composed of several levels:
Titles
Parts
Articles

Each Title has several parts, each Part has several sections, etc.

Each item of law will cross relate to other items of law. I would like to be able to model those interrelationships in Access. So, for example,

Item 1
Title 1
-Part 1
–Article 1


–Article 2
–Article 3
-Part 2
–Article 4
–Article 5
Title 2
-Part 1
–Article 6
–Article 7
-Part 2
–Article 8
Title 3
—Article 9

Item 2
[same structure as above, for ease]


The problem:

Article 4 of Item 2 might cross refer to:
Item 1, Title 1
Item 1, Title 2, Part 1 [only]
Item 1, Article 8

So the cross references are to a mixture of heading levels. Consequently, I am struggling to think about how to arrange my tables and how to map out these relationships. I would like Access to understand the hierarchical structure. An extra Article (eg 3A) might get added to Item 1, Title 1, so that title now covers Articles 1 - 3A. I want that sort of adjustment to the data to automatically permeate through.

Could someone point me in the right direction as to how to consider this problem. How should i arrange the tables? And how can I express relationships to at multiple layers across multiple items of law. Perhaps this is just not something Access is suited to, in which case is there some other product I should be thinking about.

Although I am not very familiar with Access I can write macros in VBA and I am an advanced excel user.

Many thanks

[ranman256]

youd have a self relating table (like the SWITCHBOARD uses)

a ParentKey field to show who is the parent of the current record:
your table would be like:

ID	name	parentKey
1	Item 1
2	Title 1	1
3	-Part 1	2
4	–Article 1	3
5	–Article 2	3
6	–Article 3	3
7	-Part 2	2
8	–Article 4	7
9	–Article 5	7
10	Title 2	1
11	-Part 1	10
12	–Article 6	11
13	–Article 7	11
14	-Part 2	10
15	–Article 8	14
16	Title 3	10
17	—Article 9	16

[ArviLaanemets]

Another approach:
tblTitles: TitleID, TitleName, ...;
tblTitleArticles: TitleArticleID, TitleID, ArticleName, ...;
tblReferences: ReferenceID, TitleArticleID, RefferredTitleArticleID, ....


With this structure, any article can be have references registered to any number of articles in any number of titles.