Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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