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! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Hi Ryan,

Just got lucky, I guess... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

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.