Your query would have the same table occurring multiple times ( the 
2nd/3rd occurrences having a different alias table name) with joins from 
say parent_category to category_id. A query something like the following:

SELECT Product_1.Category_Name
FROM Product AS Product_1 INNER JOIN Product ON Product_1.Parent_category 
= Product.Category_ID
WHERE Product.Category_Name)="Nike"

If you set up a query in Access with 2 occurrences of the same table and 
join the relevant fields you can get the SQL.

Regards


> I have a table in access to represent a hierarchical directory tree.
There are four columns, as seen below.  The numbers within the
master_category, and parent_category columns refer to the respective
masters category or parents category id.  The tree, written out in plain
English would look like the following:

Footwear
   Nike
      Womens
      Mens
   Adidas
      Womens
      Mens

And the respective table in Access looks like the following:

category_id      master_category     parent_category    category_name
     1                0                    0            TOP LEVEL
     2                1                    1            Footwear
     3                2                    2            Nike
     4                2                    3            Womens
     5                2                    3            Mens
     6                2                    2            Adidas
     7                2                    6            Womens
     8                2                    7            Mens


PROBLEM:  I would like to replace the ID's in the Master Category and
Parent Category with their respective category_names. (ie. Instead of
having the category id's 2 and 3 represent Footwear/Nike/Womens I would
like the actual category names to fill those columns).  How would I, in
MS-ACCESS (ultimately in SQL) write a query to perform this task if all
of the category id's and category names are part of the same table?

I need this query so that the user can perform a search on Nike for
instance and have the recordset return with all of those styles within
the Nike category.  Right now, the query only sees the ID 3 and is not
very intuitive to search on.

I know this is a bit confusing but I hope someone can offer some
assistance.

Regards,
Eric ave-access_asp-801410P@p...