Help with structure of database

[Tim F]

Hi all!

I've been asked to develop a database at work and I'm a bit stumped about the best way to structure it. I wonder if anyone could offer any thoughts?

The issue is that data will have multiple nested categories, with different numbers of entries and sub entries for each.

For example

Item 1
	Heading 1
		Category 1
			Data
			Data
			Data
			Data
		Category 2
			Data
			Data
	Heading 2
		Category 3
			Data
		Category 4
			Data
			Data

My first thought was to do a relational database with tables for each level of data - so tables for Item, Heading, Category, Data, but from what I understand it is not straight forward to then present the user with a form that allows data entry for all of those fields at once.

My second thought was to do a single table with each row having fields for Item, Heading, Category, Data and just have duplicate entries in a lot of those. But now I'm struggling to design a form that allows entry of data in multiple rows in a table with just one field - eg I want the user to select Item just once, then enter all of the Heading, Category, Data fields relating to that Item.

Perhaps the answer is just "this isn't suited to an Access database"?

[Seth Schrock]

You will need a table for each level with a field that links the current level with the level above it. For example, the first table would be (based on the names you have chosen) tblItems and would have an ID field as the Primary key and a Description field. The second table would be tblHeadings and would have an ID field as the PK, a description field and a ItemID field as the foreign key field that will link to the ID field in tblItems. The third table would be tblCategories and would have an ID field as the PK, a description field and HeadingID field as the foreign key field to the ID field in tblHeadings. You do the same thing for your data table. If your main form is viewing the data at the Item level, then you can use nested subforms to allow you to enter data for the Headings, Categories, and data. You might also look at Cascaded Form Filtering to see if that would help you.

[Tim F]

Many thanks for the suggestions!