If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to create a Multi Level List
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 |
#2
|
|||
|
|||
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. On Sat, 15 Jan 2005 17:01:01 -0800, "Perra Thomsson" wrote: 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 [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#3
|
|||
|
|||
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" wrote: 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. On Sat, 15 Jan 2005 17:01:01 -0800, "Perra Thomsson" wrote: 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 [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#4
|
|||
|
|||
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. On Sun, 16 Jan 2005 06:41:03 -0800, "Perra Thomsson" wrote: 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" wrote: 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. On Sat, 15 Jan 2005 17:01:01 -0800, "Perra Thomsson" wrote: 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 [Microsoft Access MVP] Please respond in the newgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#5
|
|||
|
|||
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 "John Nurick" wrote: 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. On Sun, 16 Jan 2005 06:41:03 -0800, "Perra Thomsson" wrote: 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" wrote: 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. On Sat, 15 Jan 2005 17:01:01 -0800, "Perra Thomsson" wrote: 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 [Microsoft Access MVP] Please respond in the newgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Create New Dictionary Problem Sort by Position | Jean Dophin | General Discussion | 8 | September 21st, 2004 06:33 PM |
create a distribution list that can only be used as a Bcc list? | Tagup52 | General Discussion | 1 | September 21st, 2004 05:42 AM |
synchronizing form and list box | Deb Smith | Using Forms | 8 | June 21st, 2004 08:15 PM |
Create chart from List Box | Brenda | Charts and Charting | 5 | November 25th, 2003 02:31 PM |