Cascading Combo Box

[shaul-r]

Cascading Combo Box?! Help ME Please!

I have three tables:
1) store_info: store_id, name, url
2) category_by_store: cat_id, store_id, cat_type, cat_parent_name, cat_name, cat_path
3) cat_by_product: ID, sku, store_id, cat_1, cat_2, cat_3, cat_4.... cat_15

The table category_by_store.cat_type contain the generation information for a category ie. the generation for category 'indoor > living room > coffee table' is gen3 and the cat_parent_name is living room.

I have a form of comboboxes that I would like to use for entering in the categories for each product by site. The control source for these combo boxes correspond to table 3 mentioned above. ID is an autonumber field, sku is the product alphanumeric code, cat_1 etc. is the respective category path from table 2.

Now I have created the form with the sku, combo box for the store id (row source is table 1), and a combo box for cat_1 (ComboCat1) (chooses all the first generation categories for the respective storeid).

My Challenge:
1) Not every category has a second generation level
2) some storeid will have one product in two category strings (ex. one category tree by function, the other by designer, etc)

Therefore

for comboboxes 2-15 I need an if equation that says:
"If the 'next generation' (i can change this field to be numeric if it helps) categories are null where cat_parent_name equals the cat_parent_name of ComboCat1 , then select gen1 for all categories for storeid, if not then select 'next generation' categories where cat_parent_name equals the cat_parent_name of ComboCat1".

I am open to any and all changes of improvement and critique.
Thank you very much in advance.

 

[pr2-eugin]

Hello shaul-r, Welcome to AWF..

I think I skimmed most of the post after the Table structure you mentioned for Table 3. That is not a good structure. You need to look up on Normalization. Each category should be stored as Rows not columns.. A separate table listing the Categories needs to be added..

The Cascading Combo style would be more simple and easier to implement if your Tables are structured properly. So an immediate redesign would help you now and on the long run. Good luck..

 

[shaul-r]

Thank you for the advice.
I have known that this structure needed to be normalized since the beginning, but I had another issue then (which I still haven't worked out) which was I didn't want empty columns when it was exported as a cross tab query.
If you could tell me how to answer my first question, and how to not have empty rows, I will be willing to normalize the table. But as of now it has 6700 rows and I don't want to go through all the work of normalization just to come back to these same problems.
When I fist learned about normalization I also read that a table only needs to be normalized to the extent that it will be useful- if it is time to kick it up a notch let me know the answers to the above - if not, I'm happy with work arounds.