Trees in Access

[stoutfellow]

Does anyone know how to implement a tree structure in Access?

As I mentioned, I want to organize my keywords into a tree, so that (e.g.) the keyword "Location" has children such as "Europe", "Asia", etc., and "Europe" in turn has children "Ireland", "Great Britain", and so on. It seems that the obvious way to do this is to have each Keyword record have a "Parent" field, containing the ID of the parent keyword.

So far, so good. I want to construct a form which will show a keyword and a list of its children, and which will allow me to add more children to that list. Thus far, I haven't been able to get the latter to work. Any ideas? ("Use a different database program" will be considered only as a last resort....)

Comments

[spiffikins]

I don't know Access but this is the normalized table structure you would use.

Table: PARENT_AREA
Columns: Area_Id, Name
Contents:
1,Europe
2,Asia
3,North America

Table: COUNTRY
Columns: Country_Id, Name
Contents:
1,Ireland
2,Great Britain
3,China
4,Canada
5,US

Table: Area_Country
Columns: Area_id, Country_id
Contents:
1,1
1,2
2,3
3,4
3,5

On the form, when you select "Asia" from the Parent list, run the query
select Name from country, area_country
where country.country_id = area_country.country_id
and area_country.area_id = 2;

will bring back "China" in this example

pass in the area_id that you select.


*************

Given that you have a limited number of combinations, you could simplify it by building a single table

Table: LOCATION
Columns: Area, Country
Contents:
Europe, Ireland
Europe, Great Britain
Europe, France
Asia, China
North America, USA
North America, Canada

Same concept though - when you pick "Europe" from the parent list, you run a query:

select country from location where area = 'Europe';

and it will bring back Ireland, Great Britain, France

[stoutfellow.livejournal.com]

Thanks, but that won't do quite what I want. I want all of my keywords in the same list, and I want the tree(s) to be of arbitrary depth (so, for instance, under "USA" there might be "California", and under "California", "Los Angeles", and so on).

I think I've figured out a way around the problem; it's a little kludgy, but it ought to work.

[subcreator.livejournal.com]

Try this.

http://www.vb123.com/toolshed/00_accvb/treeview.htm

I didn't read all of it, but it sounds like what you want to do.
Thanks - Peter

[stoutfellow.livejournal.com]

It's in the ballpark, yes, but I figured out another way to get what I wanted. It's macro-based, so it's probably not quite as efficient as something implemented in VB, but it seems to work. Thanks anyway!