How to create a Multi Level List

[Guest]

Dear friends,

In an application handling books, I have a field, Data Type=Number(Single),
in the tblCategories table holding the Category number, related to tblBooks.
It’s a two-level list, manually created. The descreptions in level one are
repeted in level 2. Se example:
1 Language
1.1 Language, English
1.2 Language, Swedish
2 Sports
2.1 Sports, Games
2.2 Sports, History

Now I need a third level (1.1.1) in the list and the data type Number is no
longer usable. I tried to use Text instead but as I thought, the sort order
would not work. (11 between 1 and 2) In a thread somewhere I read a tip to
get around this, but it did not work with more than two levels.

I am thinking about two possible way to solve this.
1. Create three tables, one fore each level. But how do I relate them to the
Books?
2. Create a Text(6) field holding a string reflecting the level and another
the actual level number, example:
010000 1 Language
010100 1.1 English
010101 1.1.1 English for beginners (as me J)
010102 1.1.2 English for business

In example 2 I created a listbox looking like a normal multi level list. By
selecting a level from the list it creates a relation between the level and
the book.
Then I need to put this level and the levels to the root together in some
calculated textboxes so it looks like the old list (1.1.2 Language, English
for business).

Perhaps I made this clear to somebody and that someone would help me with
useful tips or links.

Thanks
Perra

 

[John Nurick]

Hi Perra,

One approach would be to use three number fields for the three levels:

Level1 Level2 Level3 Category
1 0 0 "Language"
1 1 0 "Language, Swedish"
1 2 0 "Language, English"

with all three fields in the primary key, and sorting on Level1, Level2,
Level3.

Because you can't have Null values in a primary key, this would require
you to store 0 to indicate "no lower level" as in the example above.
There's no need to display it.

 

[Guest]

This is the one approach that I also have had in mind. But as I mentioned in
my second example and the wishes I have to let the list look like a numbered
list in e.g. Word, I wonder how to put the three levels together in a
textbox, showing it in forms and reports.

This is the listbox I created:
1 Language
1.1 English
1.1.1 English for beginners
1.1.2 English for business
2 Sports

This is the textbox viewing the combined category levels:
1.1.2 Language, English, English for business).

This textbox is the missing part of my application.

But if it is a better way of building the tables or so, I’m ready to do
that. However, the list has to look like above.

/Perra

 

[John Nurick]

Hi Perra,

For a textbox with combined category levels, I'd use a calculated field
in the underlying query, something like this
fCombined: [Level1] & IIf(IsNull([Level2]), " ", "." & Cstr([Level2])
& IIf(IsNull([Level3]), " ", "." & Cstr([Level3]))) & " " &
[CategoryName]

If there are many categories and subcategories, I'd probably use
"cascaded" comboboxes to select them in a form: the user would select a
top-level category from the first combobox (or listbox if you prefer)
and the rowsource of the second would be filtered according to the
choice made in the first.

 

[Guest]

Hi John,

I came up with a solution by my self.

Instead of working with level numbers in the level fields, I made a field
holding a serial number. I put this number in the three Level fields as shown
in this example:

S/N L1 L2 L3 CatNo CatDescription
1 1 0 0 1 Language
2 1 2 0 1.1 English
3 1 2 3 1.1.1 English for beginners
4 1 2 4 1.1.2 English for business
5 5 0 0 2 Sports

Then I created a query and put four copies of the table tblCategories into
it. I changed the alias of copy 2 to 4 just to recognise them (Level1, Level
2 and Level 3). I joined the first table to the copies (outer join) and then
it was easy to make the calculated field I needed.

I can use this query wherever I need to show the complete category, e.g.
1.1.2 Language, English, English for business

I hope this will help anyone with a similar problem.

Take care,
Perra Thomsson

Hi Perra,

For a textbox with combined category levels, I'd use a calculated field
in the underlying query, something like this
fCombined: [Level1] & IIf(IsNull([Level2]), " ", "." & Cstr([Level2])
& IIf(IsNull([Level3]), " ", "." & Cstr([Level3]))) & " " &
[CategoryName]

If there are many categories and subcategories, I'd probably use
"cascaded" comboboxes to select them in a form: the user would select a
top-level category from the first combobox (or listbox if you prefer)
and the rowsource of the second would be filtered according to the
choice made in the first.

This is the one approach that I also have had in mind. But as I mentioned in
my second example and the wishes I have to let the list look like a numbered
list in e.g. Word, I wonder how to put the three levels together in a
textbox, showing it in forms and reports.

This is the listbox I created:
1 Language
1.1 English
1.1.1 English for beginners
1.1.2 English for business
2 Sports

This is the textbox viewing the combined category levels:
1.1.2 Language, English, English for business).

This textbox is the missing part of my application.

But if it is a better way of building the tables or so, I’m ready to do
that. However, the list has to look like above.

/Perra