Recursive Table

I need to store and retrive hierarchial data. The data represents categories
for a product catalog that can have an undetermind number of subcategories. i
andt to use a recursive table but am not sure if that's right. I have the
recursive idea down but my problem is finding the lowest level category so i
can store it in a new product reecord without knowing that it is lowest level
because i enterded it.

How can I build a querry or form that will displat the data to where i can
select a "node" and have it give me the childern of thet node, then again
until i am at the lowest or "leaf" level? i need this so i can store the leaf
level in the product table. but i cant figure out how to make an interface or
querry that makes this "user" friendly.

Will this work for you ..........

TblCategory
CategoryID
Category

TblSubcategory
SubcategoryID
CategoryID
Subcategory

TblProduct
ProductID
ProductName
Other product fields
CategoryID
SubcategoryID

Subcategories of each Category are recorded in TblSubcategory. When you
record a Product, it is assigned to a Category(CategoryID) and then on your
form you have cascading comboboxes that limit the Product to being assigned
to ONLY a subcategory of the assigned Category.

Steve


"Mailmanny" wrote in message
...
I need to store and retrive hierarchial data. The data represents
categories
for a product catalog that can have an undetermind number of
subcategories. i
andt to use a recursive table but am not sure if that's right. I have the
recursive idea down but my problem is finding the lowest level category so
i
can store it in a new product reecord without knowing that it is lowest
level
because i enterded it.

How can I build a querry or form that will displat the data to where i can
select a "node" and have it give me the childern of thet node, then again
until i am at the lowest or "leaf" level? i need this so i can store the
leaf
level in the product table. but i cant figure out how to make an interface
or
querry that makes this "user" friendly.

hi,

Mailmanny wrote:
I need to store and retrive hierarchial data. The data represents categories
for a product catalog that can have an undetermind number of subcategories.
Use two tables:

Product: ID (PK, Autonumber) etc.
ProductCategory: ProductID (PK, FK, Long),
(Sub-)CategoryID (PK, FK, Long)

In the table ProductCategory you have a combined primary key (PK) of the
two fields. FK mean you have a foreign key relationship to the
corresponding tables.

andt to use a recursive table but am not sure if that's right. I have the
recursive idea down but my problem is finding the lowest level category so i
can store it in a new product reecord without knowing that it is lowest level
because i enterded it.
You may give us a concrete example?

How can I build a querry or form that will displat the data to where i can
select a "node" and have it give me the childern of thet node, then again
until i am at the lowest or "leaf" level?
Okay, what does your categories/subcategories look like:

a)

Category 1
Subcategory 11
Subcategory 12
Category 2
Category 3
Subcategory 31
Subcategory 32
Subcategory 33


b)

Category 1
Category 11
Category 111
Category 112
Category 2
Category 21
Category 22
Category 221
Category 3

In the first case you have a table layout like Steve posted consisting
of two tables. In the second case also two tables, but with a different
structu

Category: ID (PK, Autonumber), Denomination (Text, not NULL) etc.
CategoryStructu ID (PK, FK, Long), ParentID (FK, Long, not NULL)

There is a 1:1 relationship between Category.ID and
CategoryStructure.ID. Root (top) categories are

SELECT *
FROM Category
WHERE ID NOT IN (SELECT ID FROM CategoryStructure)

This layout should be used, when your categories are changing often.

If your categories are invariant, then you may merge these two tables:

Category: ID (PK, Autonumber), ParentID (FK, Long),
Denomination (Text, not NULL) etc.

ParentID should be NULL for root/top level categories:

SELECT *
FROM Category
WHERE IsNull(ParentID)



mfG
-- stefan --

Okay, what does your categories/subcategories look like:

b)

Category 1

Category 11

Category 111
Category 112
Category 2

Category 21
Category 22

Category 221

Category 3
Category 31
Category 311
Category 3111

My categories look like option b
they do not change often but i have a varying number of subcategories form 1
to 4.

The table that I have now looks like

tblCategories:
ID (PK, AutoNumber)
CatName ( text, Not Null)
ParentCatID (Int, FK)


Kind of like what you posted.

If your categories are invariant, then you may merge these two tables:

Category: ID (PK, Autonumber), ParentID (FK, Long),
Denomination (Text, not NULL) etc.

ParentID should be NULL for root/top level categories:

SELECT *
FROM Category
WHERE IsNull(ParentID)


My only reamaing question is how to take that flat table and us it in a
hierachial way with like a treeview maybee.

I have done it in ASP.NET with C# for a website to view products by
selecting the categories and opening nodes, and when a child node is selected
it populates a view with the products that fit that subcategory. But i cant
figure out how to use that to add products. i want to be able to select the
lowest level of any given category and have that sub(sub(sub))category ID
stored in the products table.

hi,

mailmanny wrote:
I have done it in ASP.NET with C# for a website to view products by
selecting the categories and opening nodes, and when a child node is selected
it populates a view with the products that fit that subcategory. But i cant
figure out how to use that to add products. i want to be able to select the
lowest level of any given category and have that sub(sub(sub))category ID
stored in the products table.
So, when you select a category, you want to select all products assigned
to this category or assigned to its subcategories?

In this case, you need either a helper function, as Access/Jet has no
recursive type of query or you need another way of storing your tree:

Nested Sets

http://www.mvps.org/access/queries/qry0023.htm


mfG
-- stefan --