Three subforms and one master table
I have an inventory database. One of the tables within the db is the item master table. For simplicity, assume it has two fields:
ItemNumber(key)
Description
The primary user of the system has requested the ability to print reports based on categories and subcategories. My solution was to create 4 tables.
TABLE 1: Master Category
CategoryID (key/autonumber)
Description
Table 2: SubCategory1
CategoryID (key/long integer)
Category1Id (key/autonumber)
Description
Table 3: SubCategory2
CategoryID (key/long integer)
Category1Id (key/long integer)
Category2Id (key/autonumber)
Description
Table 4: SubCategory3
CategoryID (key/long integer)
Category1Id (key/long integer)
Category2Id (key/long integer)
Category3Id (key/autonumber)
Description
Examples:
Master : OutsideProducts
: InsideProducts
Category 1: OutsideProducts/Korea
: Outside Products/India
Category 2: OutsideProducts/Korea/Chil
: OutsideProducts/Korea/Teen
Category 3: OutsideProducts/Korea/Chil
: OutsideProducts/Korea/Chil
A product can be assigned any number of categories from Master to Category 3. Multiple products can be assigned to each category/subcategory. Now I can run a report which will summarize all products by each category. Presently, the products are not assigned any categories. Perhaps, I am approaching this in the wrong manner. However, presently, the problem is the fact that I cannot get all three subcategories to appear as separate forms within the Main Category form
+-------------------------
|Main Category: Outside Products
|
| +-------------------------
| CATEGORY 1 CATEGORY 2 CATEGORY3
| +-------------------------
| Korea Children Airplanes
| India Teen Trains
|
Select Korea and only categories linked to Korea will be displayed
Select Children and only categories linked to Korea/Children will be displayed.
I can use the form wizard, selecting all tables and a form will be created which looks similar to the concept above. However, Access will only link 2 subforms (Category1 / Category 2). It attempts to add Category 3 to Category 2 subform. Is this a limitation to number of subforms on main form? Is there a better way to accomplish this task?
ItemNumber(key)
Description
The primary user of the system has requested the ability to print reports based on categories and subcategories. My solution was to create 4 tables.
TABLE 1: Master Category
CategoryID (key/autonumber)
Description
Table 2: SubCategory1
CategoryID (key/long integer)
Category1Id (key/autonumber)
Description
Table 3: SubCategory2
CategoryID (key/long integer)
Category1Id (key/long integer)
Category2Id (key/autonumber)
Description
Table 4: SubCategory3
CategoryID (key/long integer)
Category1Id (key/long integer)
Category2Id (key/long integer)
Category3Id (key/autonumber)
Description
Examples:
Master : OutsideProducts
: InsideProducts
Category 1: OutsideProducts/Korea
: Outside Products/India
Category 2: OutsideProducts/Korea/Chil
: OutsideProducts/Korea/Teen
Category 3: OutsideProducts/Korea/Chil
: OutsideProducts/Korea/Chil
A product can be assigned any number of categories from Master to Category 3. Multiple products can be assigned to each category/subcategory. Now I can run a report which will summarize all products by each category. Presently, the products are not assigned any categories. Perhaps, I am approaching this in the wrong manner. However, presently, the problem is the fact that I cannot get all three subcategories to appear as separate forms within the Main Category form
+-------------------------
|Main Category: Outside Products
|
| +-------------------------
| CATEGORY 1 CATEGORY 2 CATEGORY3
| +-------------------------
| Korea Children Airplanes
| India Teen Trains
|
Select Korea and only categories linked to Korea will be displayed
Select Children and only categories linked to Korea/Children will be displayed.
I can use the form wizard, selecting all tables and a form will be created which looks similar to the concept above. However, Access will only link 2 subforms (Category1 / Category 2). It attempts to add Category 3 to Category 2 subform. Is this a limitation to number of subforms on main form? Is there a better way to accomplish this task?
ASKER
Let's try that again.
Database1.accdb
Database1.accdb
I think you attached the wrong database.
That aside, there is at least one issue in your table design. In particular, you have an ambiguous category hierarchy. To illustrate, look at a sub-category2 row. That row contains two references to the master category. The first is the direct reference in CategoryID. The second is indirect via Catetory1ID, which I presume references a sub_category1 row, and that in turn has a different reference to the master category.
subcategory3 table has the same issue, only it is worse it potentially references 4 different master categories.
That aside, there is at least one issue in your table design. In particular, you have an ambiguous category hierarchy. To illustrate, look at a sub-category2 row. That row contains two references to the master category. The first is the direct reference in CategoryID. The second is indirect via Catetory1ID, which I presume references a sub_category1 row, and that in turn has a different reference to the master category.
subcategory3 table has the same issue, only it is worse it potentially references 4 different master categories.
ASKER
Thanks for your response. I didn't want to upload the entire db as it was so large. So, I created a work db. Obviously, I attached the wrong db. The one attached is the correct one. Based on your response, I changed the way the tables were keyed. However, now the forms don't sync. With the other structure, moving from one subcategory to the next resulted in the attached subcategory changing to allow me to view only the subcategories related to the master subcategory.
Database2.accdb
Database2.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Again, thanks for your insights. The entire purpose of the category/subcategory scenario is to get product totals by the various categories. I have attached an updated database for you to look at. I created an item table and assigned category/subcategories to it. Then I created a report. The results were nothing I expected. What I want is a total by Outside / Country / Age Group / Product Category. I'm not sure I understand what you mean by using listboxes.
Database2.accdb
Database2.accdb
ASKER