Solved

Three subforms and one master table

Posted on 2012-04-04
6
272 Views
Last Modified: 2012-04-10
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
Comment
Question by:rodneygray
  • 4
  • 2
6 Comments
 
LVL 1

Author Comment

by:rodneygray
ID: 37808422
I am attaching the a copy of the database with just the category forms and tables.
0
 
LVL 1

Author Comment

by:rodneygray
ID: 37808431
Let's try that again.
Database1.accdb
0
 
LVL 42

Expert Comment

by:dqmq
ID: 37809011
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:rodneygray
ID: 37809132
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
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 37812216
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
 
LVL 1

Author Comment

by:rodneygray
ID: 37814795
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now