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?