Hierachy Structure

[anandram]

Hi there,



Wondered if anyone can help?

I have the requirement to have a 4 level hierarchal database including the top level.

I have created 5 flat tables and was wondering how i put these in hierarchical order? I tried sub data sheets but these are a headache because EACH record in the top level table will contain different records stored in the other 3 tables i created,

When user expands the top level in the table it should display only records from level 2 that are relevant, when expanding level 3 the same should happen but obviously relating to level 2, and when expanding level 3, the level 4 records should be relevant to level 3.

I know this is a basic question but i cant wrap my head around it because none of the tables have any unique identifiers in order to create relationships.

Top level has approx 20 records,

Level 2 (approx 50+ records in table),

level 3 ( approx 80+ records in that table),

level 4 (possibly hundreds of records in that table)


This relates to food terminology so top level is perhaps the most elaborate name of a dish (some french term to describe a burger) where level 4 is the most common used term (burger).


Hope i have explained this appropriately if not please let me know, what's the best way to go about this?



Thanks for any help,

Anand

[ssanfu]

What are the the table and field names?

[anandram]

hi ssanfu, thanks for your response.

I would prefer to start from scratch so any help you can provide would be appreciated. I dont mind starting from scratch if you think it's required.

Top Level Table: Field 1 -Correct Name: this is the name of the most technical term for the dish, this name can encompass many more dishes of this sort which brings us to...

Level 2: Field 1: Group Term - this is the name for a group term for dishes of this sort. This can be further broken down to...

Level 3: Field 1: Industry term - this is the preferred term for these dishes, and can be broken further down to...

Level 4 -Field 1 - Common Term - is the name most commonly associated with this dish, so perhaps "burger", cheese burger, piri piri burger, bacon burger, etc

I basically just want to be able to put this in a hierarchical type db.

Appreciate your help, please let me know if you need any further info. The table names can be as shown above: Top Level, Level 2, level 3 level 4

Anand.

[ssanfu]

See attached mdb (A2K3 format)... Does this help??

[anandram]

hi ssanfu,

That looks like it could do the job. I will take a further look and let you know. Thanks very much for your kind help.

Could you explain the process in which you took in order to achieve the DB? The forms are great, however input of the terms can be through the tables as users will not have to enter the new terms. They will just view the hierachy.

I do like the list box views though!

[anandram]

Hi Ssanfu,

I've just had time to look over this and it was what i needed Thanks very much for your help.

Could i request if you could advise /point me in the right direction for the 2 following things, number 1 is a manual approach, where 2 is more automated (prefered).

1.
how to reverse the hireachy process? For example to be able to not only search from top > box 2 expands with records >then box 3 etc, but to be able to select the lowest level (list box 4 i think) and for the same process to occur but upwards? i presume it has something to do with making the "null" value .....a value in the VB code, but not sure what to enter. Obviously in order for this to be able to work, the lowest level (4th list box) will display ALL lowest level terms initially, (perhaps in alphabetical order).

(Thinking out loud: later i may just have a "show all" button above each list box, this way the user can decide where to start the hierachy search from. Preference at the moment is for lowest level though.)


2. Based on number 1 above i'd like to have a search box (on lowest level) on the same form you kindly created, that enables me to start typing an entry and access auto suggests and drops down the combo box displaying the relevant entries based on text entered as the user types. I understand i need a combo box for this, and this is fine. However the difficult part is
a) dropping down the combobox as user types and
b) when the correct term is selected, the 4th list box you made (lowest level) should display this term and present the correct hireachy structure in the remaining 3 list boxes as explained in point 1.

The reason it is important to work backwards is because not many people know the Highest level term (top list box), but many know the lowest common used term (4th list box)

Thanks very much for your help and expertise, anyone else is also welcome to provide comments, just thought as ssanfu created the listboxes previously, maybe easier to relate to the requirements.



Look forward to the replies!

[anandram]

Is anyone able to help with this please? It seems relatively straight forward however i am not that savy with access. Thanks. If it is complicated, i would gladly pay for someone to complete this for me..

[ssanfu]

Could you explain the process in which you took in order to achieve the DB?

It is a simple one to many structure. One top level can have many 2nd level. One 2nd level can have many 3rd level. etc.....

The forms are great, however input of the terms can be through the tables <snip>

Use the form to automatically update the PK/FK entries in the table. If you make entries using the table, you will manually have to find the PK and enter it into the FK field of the many table.

Again, use the forms to enter data....

[ssanfu]

See attached mdb.....

[anandram]

Thanks so much ssanfu, i will check this out and give you feedback shortly. I could use someone with expert talents in VB/Access.

How much would something like what you provided me with cost to pay a coder? I just mean for future, would be useful to know there's someone i can fall back on to get the job done just the way i want it!

[ssanfu]

Don't know... I have two full time jobs (only 1 programming) and two pro bono publico Access projects. So, not much time to do much else.

You should search your area (ask friends, check out user groups) to find someone local to help. And you can/should post & search here.

[anandram]

Thanks again for the help, your expertise came in handy. You can check out my recent question if you want

https://www.accessforums.net/program...orm-13254.html