• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

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/Children
                  : OutsideProducts/Korea/Teen
Category 3: OutsideProducts/Korea/Children/Airplanes
                   : OutsideProducts/Korea/Children/Trains

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?
0
rodneygray
Asked:
rodneygray
  • 4
  • 2
1 Solution
 
rodneygrayAuthor Commented:
I am attaching the a copy of the database with just the category forms and tables.
0
 
rodneygrayAuthor Commented:
Let's try that again.
Database1.accdb
0
 
dqmqCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rodneygrayAuthor Commented:
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
0
 
dqmqCommented:
That database design is much, much better--it's a classical 4-level hierarchy. I do have some observations about the indexes, but before delving into those and the issues with the form, though, I want to step back and make sure we are on the same page in regard to two issues.

1.  The 4-level hierarchy is a rather rigid design.  That's fine if it serves your requirements, but I'm not sure it does.  Just want to double-check that before we invest too much energy in a form based on it.

2.  I want to make sure I am perceiving the purpose of your form correctly and then ask you if you are open to a method that is simpler than using subforms?

------------------------------

1. You said:
A product can be assigned any number of categories from Master to Category 3.

With your table design, it is trivial to assign an item to any number of category3's. And each such assignment represents a full hierarchy: master-subcat1-subcat2-subcat3.   But it is not the best design if you want to also assign categories at a different level, for example:  master_subcat1.   Or perhaps, just subcat1 without regard to any master category.

Also, and perhaps more importantly, with that design you may have the same description multiple times at each level.  For example, "outside/india" and "inside/india" .   It occurs to me (and this is presumptive, based on what the words in your sample data mean to me) that both "india's" are really the same thing.  Said another way, I suspect "outside" and "india" do not really have a hierarchical relationship and, if not, it is a mistake to represent it that way.  

Deciding whether your categories are hierarchically dependent or independent is HUGE and needs to be made before you go further with your development.  It changes EVERYTHING.  

2.  You describe your form as a  "drill down": choose category, then sub-categories within that category, then sub-categories within that sub-category and so forth.  At the end of the day, we'll have drilled down to a list of sub-categories at the lowest level.  I assume at that point, you want to either choose one and see all the products assigned to it, or choose one and assign it to a product.  

I'm don't know why  you chosen to use linked subforms for each category, but I suggest there are simpler, more flexible ways.  For example, if you use listboxes or comboboxes, or even independent subforms, then you can choose the categories in any order.  What do you think?
0
 
rodneygrayAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now