'nested fields' in MS Access

Question

I have a primary field called 'Unit', and I need to specify how many objects (exhibiting great variability) were found within them. There are 116 types of these objects, which can be broken down into 6 categories. Each category dictates what attributes may be used to describe each object type. For every unit, I need to record how many types of objects are found within it, and document how many of them exhibit each attribute. I sketched out examples of the schema and how I need to apply it. Perhaps the simplest solution would be to create a table for each type, and relate them to the table containing the unit list, but then there will be so many tables (is there a limit in MS Access?). Otherwise, is it possible to create 'nested fields' in access? I just made that term up, but it seems to describe what I'm looking to do.

• Category 1 (attribs: a, b, c, d)

  • Type 1
  • Type 2

• Category 2 (attribs: x, y, z)

  • Type 3
  • Type 4

---

• Unit
  • Type 1 | a | b | c | d |
  • Type 2 | a | b | c | d |
  • Type 3 | x | y | z |
  • Type 4 | x | y | z |

---

UPDATE: To clarify, I essentially need to create subtables for each field of my primary table. Each field has sub-attributes, and I need to be able to specify the distribution of objects at this finer-grained resolution.

Answer 1

What you want is similar to this question on SO: Database design - multi category products with properties.

So you'll have a third table which relates each the attribute value to the Unit. And to control which attributes each Category can have, a fourth table will be required which specifies the attributes (names) for each category.

Unit:
    .id
    .categoryid

Category:
    .id
    .cat_name

Category_Attributes:
    .attribID
    .categoryid
    .attribute_name

Unit_Attributes:
    .unitid
    .attribID
    .attrib_value