CODEWITHSUNDEEP
ms-access
Timothy
Timothy

Reputation: 35

Form Functionality Verse Table Storage

I have some experience getting data out of database, but not so much in design. To work on this, I'm taking some personal projects and trying to create them in access. I've run across an issue that I was able to get a solution to, but I find it clumsy and was hoping to have some opinions on what I can do better.

My current project is a monthly budget. At this stage I would like a Form to appear as follows:

I found that I can do this if all the sub-categories are the fields in a table and the category names are hard-coded as labels in the form. However, I would like my table structure to be like this:
Category:
ID
Category_Name

Sub-Category:
ID
Sub-Category ID_Category
Show_Category

Budget: Id
Sub-CategoryID
Budget_Amount

The reason I want this structure is that not all sub-categories will be used every month, and in my mind it will be easier to match what was budgeted verses what was spent. I am also trying to practice minimizing space taken up by the database. Are there any ways to do this easily? Or am restricted to my current solution?

Upvotes: 0

Views: 29

Answers (1)

Rene
Rene

Reputation: 1093

I would dive into normalization principles first as you seem not to be too familiar on those principles yet. "The reason I want this structure..." is not a good reason at all. Your basic is a sound normalized database structure.

I'd suggest you structure your tables like this:

tblCategory -> CatID (autonumber PK), Categorie tblSubCategory -> SubCatID (autonumber, PK), CatID (number, FK), Subcategory

As for budgets, I'd expect that budgets tie in into projects. There's no logical reason to tie in budgets directly into any kind of category. We would need more and escepially more explicit information on what you're trying to achieve.

Similar question:

Database Design Question - Categories / Subcategories

There's sites that could help you to start understanding designing in MS Access, for instance

http://www.functionx.com/access/index.htm

Upvotes: 0

Related Questions