Unlimited Categories/Subcategories/Sub-Subcategories

Posted on 2009-02-10
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
    LVL 27

    Expert Comment

    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

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

    LVL 27

    Accepted Solution

    Yes, use the function in the query:

    Select FullCat(CatID) as FullCatDesc From MyProductRecords
    LVL 50

    Expert Comment

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

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now