I have a list of products in which we need to categorize. These categories will probably go 4 deep as in - Category "Bearings", SubCat "Precision Bearings", SubCat2 "Metal Bearings", etc. Not all parts are Bearings so this could get complicated.

I have 1 to M from tblProducts to tblCategories using ProductID - option 2 on join type from tblCategories to tblsubCategories.

What I would like is to have someone choose the Category combobox and then it would populate the subcategory box. Not good in VB so if anyone has any ideas let me know.

Also not sure I have the relationships set up right.

Thanks for your help!

Hi,

Check out this tutorial: Cascading List for Access Forms

Hope that helps...

you could do something like this:

Set the rowsource of the 2nd or subsequent combo boxes equal to a sql statement where the value = what is in the first combo box

So in the afterupdate of the first combo, you would set the rowsource of the second combo and so forth

HTH

DbGuy you beat me by seconds!

Hi Ryan,

Just got lucky, I guess...

I see the tutorials but I'm not sure I have the relationships correct. Do I put ProductID in tblCategories as FK to serve as the 1 to M relationship form tblProducts to tblCategories? It seems as though these tutorials list how to do it without having any relationship to another table(i.e. 1toM). I need this to tie into my Products table so I can eventually manipulate the data.

Does this make sense?

Hi,

To be able to tell you where to put which foreign keys where, we'll need to really understand your needs.

You said that you have products that should be categorized but that there are different levels of categories.

When you store a product, which level of the category do you need to store with it. Taking your example above, let's say you have a product called "SuperDuperRollerBearing," you can store the foreign key for the subcategory for "Metal Bearings" with it.

When you construct your query, you can then go back and list the other categories above the subcategory "Metal Bearings."

If that is correct, then I think your table structure might be something like (assuming that "Metal Bearings" is Category Level 3):

tblProducts
ProductID, pk
ProductName
CategoryLevel3ID, fk

tblCategoryLevel3
CategoryLevel3ID, pk
CategoryLevel3Name

tblCategoryLevel2
CategoryLevel2ID, pk
CategoryLevel2Name

tblCategoryLevel1
CategoryLevel1ID, pk
CategoryLevel1Name

tblCategoryLevels1To2
CategoryLevel1To2ID, pk
CategoryLevel1ID, fk
CategoryLevel2ID, fk

tblCategoryLevels2To3
CategoryLevel2To3ID, pk
CategoryLevel2ID, fk
CategoryLevel3ID, fk

Hope that helps...

I see what you mean I think (I have attached a sample DB). However, if I do this according to the tutorials, the Category Level 3 would be my independent boxes correct? Once I would populate that combo box, the other levels above it would populate to the specific, broader categories right?

What I ultimately want is when I have a Products Form, the user will have 3 combo boxes (for 3 levels of categories). They will populate, starting with the first, most general category and select a choice which will in turn populate the following combo boxes. I thought I was getting this but with your set up I'm lost on how to apply the tutorials you linked to all the tables.

The junction tables are really throwing me off!

I really appreciate the help - just a newb here so sorry for any obvious questions.

Carlos

Ahh, so you want three comboboxes on your form, not just one. If that's the case, your products tables might look like this instead:

tblProducts
ProductID, pk
ProductName
CategoryLevel1ID, fk
CategoryLevel2ID, fk
CategoryLevel3ID, fk

That structure will record the progression of the product through all the categories and their subs. However, that structure is also agains normalization rules. To comply with normalization rules, we need to add another junction table, such as:

tblProducts
ProductID, pk
ProductName
ProductCategoryID, fk

tblProductCategories
ProductCategoryID, pk
ProductID, fk
CategoryLevelID, fk
CategoryID, fk

tblCategoryLevels
CategoryLevelID, pk
CategoryLevel

tblCategories
CategoryID, pk
CategoryName
ParentCategoryID, fk (self-join to CategoryID)

Then in your comboboxes, you will filter the CategoryID against the ParentCategoryID.

Hope that helps...

where do i self join the categoryid to parentcategoryid? in a query?

still trying to get this to work. i figured it would be easiest to do this on one table so i tried doing the code exactly as i saw it in the tutorial:

Combo8.RowSource = "Select tblMainCategories.SubCategory " & _
"FROM tblMainCategories " & _
"WHERE tblMainCategories.Category Name = '" & Combo6.Value & " ' " & _
"ORDER BY tblMainCategories.SubCategory;"

it's telling me that i have a syntax error on this line:

"WHERE tblMainCategories.Category Name = '" & Combo6.Value & " ' " & _

no idea how to fix it. what am i doing wrong?

trying to attach db....

db attached.

nothing?

Take a look at the attached. Check out the tables....

hth,
Jack

i see now what you did. now my next question is what relationship do i give these categories if I want them attached to a products table for example.

one product one category
one category has several products

Are these combo boxes still going to be unbound? I'd like to be able to see reports based on the way i organize the products.

is this possible?

thank you again for your help!!!

Take a look at the table structure provided by TheDBGuy. It is, I think, what you are after...

Good luck!

Jack

I appreciate the feedback. however i don't see the need for the category level table and the self-join to the parent category.

the category level table serves no purpose i think because i don't think it matters to justify what level it's on. maybe if it was a subcategory table??

what i want is what you showed me on your sample db. i need to have all levels of categories in a table related to the productID somehow. can you explain the category level and the self-join?

I'm afraid I am not following you. There are two tables, Category and SubCategory. If you want to normalize your structure then I would suggest you set up the tables as I suggested. If you look at the structure shown to you by the DBGuy you will see that there are a number of tables involved and a separate table (tblProductCategory) to join the Products and Categories. His setup is a normalized structure and I strongly suggest you use a normalized structure for your database as it will make things much easier as you further develop it....

hth,
Jack

i think i am understanding the structure now. but i'm not following as to why there is a need to self-join the ParentCategoryID with the CategoryID? Where would I need this?

i think this is what i want to do. i have the structure but having problems on the product form having the combo boxes work. do i need to add a table somewhere???

help!

see attached.

Edited by: carosemena on Wed Dec 23 16:56:38 EST 2009.

You have a list of Categories. You also have a list of SubCategories that belong to various Categories. When you have a setup like that you want to create the tables as I did in the demo and then save the CategoryID as the Foreign Key in the SubCategories table. What if you want to add a new Category? How can you do that if you don't have a table for it? Also, it is possible the a SubCategory could belong to more than one Category.

The setup that we are talking about is about normalizing the structure in a relational database. Access is not a flat-file system, but a relational one so things are done rather differently and somewhat counter-intuitively until you get the 'hang of it'.

I hope this makes sense and answers your question. If it does not then you will need to be more explicit in what it is you do not understand.

hth,
Jack

Ok. But if you have a SubCategory table with a Category FK, what's the difference between the SubCategoryID and your ProductCategoryID (which has both Category ID FK and SubCategoryID FK)?

I think what I have a hard time with is the fact that the subcategory has to be linked to the main Category. Then, a junction table has to be linked to both levels of Categories, which also has a ProductID FK.

If you have that setup, for every Subcategory2ID, you would have a combination of your CategoryID and SubCategoryID. The same thing would be for the Junction table of ProductCategories - you would have a ProductCategoryID for every combination of the CategoryID and SubCategoryID......not sure I'm getting this right but am i?

If so, then if I wanted to hook all this up to the tblProducts, the Products table would be linked with the ProductCategoryID. I would create a form with the tblProducts and the subform as the tblProductCategory (linked by Product ID).

Then, I would use SQL query to filter the first combo box and requery in the VBA code. Then, for the dependent box (SubCategory), I would put SubCategoryID and CategoryID in the SQL query but under the CategoryID, I would filter with the form control of CategoryID - is that right?

You are changing names in your tables so now it is a bit more confusing. I have been under the impression that you have a Category and each Category can have additional SubCategories. Using your current table names they should be set up like this:

tblCategory
CategoryID (PK and auto)
CategoryDescription (Text)

tblCategoryLevel2
CategoryLevel2ID (PK and auto)
CategoryID (FK to tblCategory)
CategoryLevel2Desctiption (Text)

tblProducts
ProductID (PK and auto)
ProductName
...other necessary fields to identify a product...

tblProductCategories
ProductCategoryID (PK and auto)
ProductID (FK to tblProducts)
CategoryID (FK to tblCategory)
CategoryLevel2ID (FK to tblCategoryLevel2)

You have the Products table, but it has a field called "ProductCategoryID" that you do not need. Your tblProductCategory is set up correctly....

hth,
Jack

i'm really sorry for changing it up on you. I'm trying not to be confusing.

I see your setup but if I do that, I would need to assign a product number every time I create a ProductCategoryID - is that right?

because i have 1000s of products already loaded in the products table and now i'm trying to organize them by putting them into categories like you have above.

again, I appreciate the continued help on this. i'm obsessed i think i'm so close!

Theoretically you do not need the CategoryID in the ProductCategory table as the SubCategoryID is unique for every Category/SubCategory that you set up.

The Junction table between Product and SubCategory is necessary. I realize that it seems like you don't need this table, but you do because of the way a relational database is structured. This junction table will work with just the ProductID and SubCategoryID because you can use a query to extract the CategoryID from the SubCategory table if you need that information...

I think you are getting very close to sorting this all out!!!

Jack

I agree that you are close! If you have a table with data already entered and you are now adding additional information you will need to add the necessary tables. Then you will need to manually update the new tables or write some functions to update the new tables with the necessary data. Once your new tables are updated you should be able to add products and categories easily....

Jack

Glad to have UtterAccess still here after the holidays!

Still have a few more questions. You said "If you have a table with data already entered and you are now adding additional information you will need to add the necessary tables." What would be the necessary tables and what functions would I need to write? I'm a beginner so I'm not that good with this FYI.

What I have right now is a table with 20,000 products. I need to assign categories to these products and then run queries to see what products fall in each category/subcategory. Since I have to have a product record for each ProductCategoryID could I create all the combinations of the Category/Subcategory tables, then when I'm ready to assign a Category to the product, I use the Product Category table right?

My combo boxes are sill messing up. But how would I set them up in cascading fashion? Using the ProductCategory table?

I imported some data and tried to work with the form you did. Check it out.

Kind of. I would prefer that we switch the roles of the combo boxes. Perhaps the dependent combo box be the product. Plus, how do you pull in the junction table of ProductCategories like above? I like how you got the combo boxes to work, though....still not getting it!


Ultimately, what I need is to have a form with all the tblProduct info with a couple of combo boxes so that I can assign them their respective category. That way, down the line, I can organize all the products by these assigned categories. I suppose I could do it this way but I need categorical information to be embedded with the product information in the same record somehow. I hope that makes sense.

Thank you,

Carlos

Carlos -

Access is not an easy program to learn as it has a rather steep leaning curve because of its power. A lot of us here at UA have been 'learning' Access for a very long time and we continue to learn so do not be too disappointed if you are not able to sort everything out with a couple of questions and answers.

As far as 'dependency' of your categories it should be in the order of Category -> SubCategory -> SubSubCategory... or however it is supposed to be. Once you have set up your junction table(s) properly you can put the categories in any order you like.

The most important part of your database is your table structure. Get that right and the pieces will fall into place....

I have to dash of for a bit, but I will return in a couple of hours.... Good luck with this!!!

Jack

Jack!

Thanks for your reply! I think I have the table structure set up correctly now. Can you check my previous attachment and let me know if I am on the right track? I also need some bound combo boxes (I think) so that the categories chosen will be assigned to the productID

Thanks again for your continued help!

Carlos

Carlos -

Sorry for the long delay...

tblProducts
ProductID (PK and auto)
ProductName
ProductDescription
...etc...

tblCategories
CategoryID (PK and auto)
Category

tblSubCategories
SubCategoryID (PK and auto)
SubCategory

tblProductsAndCategories
ProductsAndCategoriesID (PK and auto)
ProductID (FK to tblProducts)
CategoryID (FK to tblCategories)
SubCategoryID (FK to tblSubCategories)
...other necessary fields....

Based on what I have seen this is probably the way you should go. tblProductsAndCategories would be a subform on a form based on Products and CategoryID and SubCategoryID would be combo boxes based on their respective tables.

This may be somewhat different than what we have discussed previously, but I am not sure of your business model so this is my best guess as to how you should do it. The final decision will have to be yours based on how Products, Categories and SubCategories relate to each other...

I hope this helps and good luck! Do not hesitate to ask questions if you are unsure how to proceed...

Jack

Agreed. I think we were working towards that setup except that tblProductsandCategories was tblProductCategory previously.

Now.....I still can't get the combo boxes to work!!!

What in the world am I doing wrong?!!?!?

I'm so close yet again!

Carlos -

Look closely at the original demo I sent you for the 'cascading combos'. It is NOT like your version, but relies on a criteria-based query. Look at the Events for the first combo as well as the Row Source for the second combo...

I understand your frustration as I have spend a good deal of my Access years suffering that same frustration! Be patient, believe in your abilities and you will sort it out!!!

If you cannot work it out then post your attempt and we will do what we can to assist... It is not my approach to do it for you, but to try and point you in the right direction.... If I do it for you then what is your reason to learn it? I am a believer in you having that 'AH HA!' moment because that may give you the key to solving the next problem you encounter....

Hang in there as we ARE on your side...

Jack

Jack,

I appreciate the encouraging words. I certainly think that doing it for me does not help - it would probably add to the problem as I would have even more questions! However, it seems as though I have the right design down, and have used all the information in this thread to create this cascading combo box.

HOWEVER, for some reason, my combo boxes can't work because when I open the form on the form with the cascading boxes in the sub-form, it asks me for a parameter value. It seems as though when I selected the independent control in the SQL of the dependent control, it messes up for some reason! It's exactly like yours! How can I fix this?!

Perhaps because the control is in the sub-form? I have no idea I keep trying to change the filter value but to no avail! You'll see!

Thanks again in advance for your help.

Hopefully, this is the last attachment I will upload!

Carlos

Carlos -

I have not forgotten you! Today was my morning to go for a very long walk and then breakfast....

Take a look at the attached. Please note that there is some necessary code in the On Enter and On Lost Focus events of the SubCategoryID control because you are using a Continuous form. Without this code the combos will not show any data...

Let me know if this is now working for you!

Jack

EUREKA!!!!!! AT LAST! Now I'm going to create my database to use these EXACT control names so that I don't have trouble because the code is above my experience at this point. I'm so glad you could help me on this. Thank you so much!!!!!

Carlos -

You are very welcome! And don't worry about keeping the same control names....experiment so you learn as you go. I'm sure you will be able to sort out the code so just jump in with both feet and go for the gusto!!!

Good Luck!!

Jack