Unlimited Categories/Subcategories/Sub-Subcategories

I'm receiving data from an application that has unlimited numbers of category and sub-categories.
As such, I cannot have a table to match each of these (they can be created and deleted at any time) so I wanted help creating a structure where my categories table contains all the categories and sub-categories with a structure that points to itself e.g.

CatParent - this contains the CatID of another record in the same table, 0 if top level

I then have product records that contain the CatID. In this case a product can only be in one category(sub) which simplifies things a little, but I need to show the product and its category hierarchy i.e. Category\Subcategory\Sub-subcategory\Sub-Sub-subcategory etc

How might I achieve this on a form or report?

many thanks
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Create a function to return the full category description - something like the following - and use it as the source of your control(s)

Publib Function FullCat(CatID as long) as string
 dim strCat as string

 Do While CatID > 0
   strCat = "/" & DLookUp("CatName", "CatTable", "CatID =" & CatID)  
   CatID = DlookUp("CatParent",   "CatTable", "CatID =" & CatID)
 If Len(strCat) > 0 then
  ' Get rid of the leading /
 End if
 Return strCat
End Function
pzh20Author Commented:
Is it possible to do the same in a query and return the Product and it's Category hierarchy?

Yes, use the function in the query:

Select FullCat(CatID) as FullCatDesc From MyProductRecords

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve BinkCommented:
>>> Is it possible to do the same in a query and return the Product and it's Category hierarchy?

Yes and no.  If you are looking to do this solely within Access, MikeToole's solution should work as-is.  If you need MySQL to return that dataset, you'll need to build a stored procedure in MySQL to duplicate that recursion.  A direct translation of MikeToole's VBA code would probably work excellently.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.