Unlimited Categories/Subcategories/Sub-Subcategories

Posted on 2009-02-10
Medium Priority
Last Modified: 2012-05-06
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
Question by:pzh20
  • 2
LVL 27

Expert Comment

ID: 23609783
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

Author Comment

ID: 23609836
Is it possible to do the same in a query and return the Product and it's Category hierarchy?

LVL 27

Accepted Solution

MikeToole earned 375 total points
ID: 23609991
Yes, use the function in the query:

Select FullCat(CatID) as FullCatDesc From MyProductRecords
LVL 51

Expert Comment

by:Steve Bink
ID: 23618293
>>> 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.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question