Combo boxes and products with multiple categories

[Guest]

Hi guys,
I have looked in the posts for this problem, but I guess I am not asking the
right questions.
I use Access 2003.
I have the typical setup for products …combo box A to select a category for
a product list in combo box B based on a query.
My problem is that my products can be in multiple categories and I am having
trouble filtering the list.
My products table is set up so each category has its own field with yes/no
data type (check box).
For instance, on the Main form I select Standard from the Style combo box
then I want the Products combo box on a sub form to show the products with
the Standard categories checked yes and in some cases I need more than one
category that is checked yes to be the result.
I tried “reverse engineering†the Northwind data base, but it only uses a
single category for each product.
Can I do this with a single query or do I need different queries for each
category?
I can get more specific, but I am hoping for a light-bulb moment.
(You guys must really be sick of hearing about combo boxes!)
Thanks for helping.

 

[Van T. Dinh]

It sounds like your (Product) Table is not normalised. If having a new
Category forces you to create an additional Boolean Field in the Table
Product then the Table is not normalised.

It is much better to use a Many-to-Many relationship, i.e. a Link / Resolver
Table in this case a a Product can belong to multiple Categories and a
Category can have multiple Products in it.

The Link / Resolver Table tblProductCategory should have Fields like:

ProdCatID AutoNumber PK
frg_ProductID FK from tblProduct
frg_CategoryID FK from (look-up) tblCategory
....

You should set a Unique Index on the comb. (frg_ProductID, frg_categoryID).
Alternatively, don't use the AutoNumber Field and simply use the comb. as
PrimaryKey for the Table.

 

[Guest]

It sounds like your (Product) Table is not normalised. If having a new
Category forces you to create an additional Boolean Field in the Table
Product then the Table is not normalised.

It is much better to use a Many-to-Many relationship, i.e. a Link / Resolver
Table in this case a a Product can belong to multiple Categories and a
Category can have multiple Products in it.

The Link / Resolver Table tblProductCategory should have Fields like:

ProdCatID AutoNumber PK
frg_ProductID FK from tblProduct
frg_CategoryID FK from (look-up) tblCategory
....

You should set a Unique Index on the comb. (frg_ProductID, frg_categoryID).
Alternatively, don't use the AutoNumber Field and simply use the comb. as
PrimaryKey for the Table.

--
HTH
Van T. Dinh
MVP (Access)






Thanks for the quick response!

I tried to run the Analize Table Tool, but I get a strange error message. I
will try to split the table manually.

 

[Guest]

I tried to run the Analize Table Tool, but I get a strange error message. I
will try to split the table manually.

Hi again,
I still need help.
I finally normalized my tables. Now I am having trouble setting up the query.
My tables are:
RailProducts with ProductID Pk
ProductCategories with CategoriesID Pk and RailProductsID Pk
Categories with CategoriesID Pk

I am using a combo box on my main form called “Style†to select a name of a
style to set the products in the subform ctrlIBalconyDatasub BalconyPart
combo box. By selecting a style I would like the query to limit the products
by categories. All the products use check marks in the category table to
select its category.

Am I to use a parameter query? I want the “Style†combo box to set the query
(I think) but can’t get it to work.
Any help would be appreciated.
Thanks

 

[Van T. Dinh]

You set-up is slightly different but check the Access Web article:

http://www.mvps.org/access/forms/frm0028.htm

The difference is that in the the article, the cascaded ComboBoxes are on
the same Form while in your set-up, the 2nd ComboBox is on the SubForm
(which is *another* Form inside the main Form) so you need to change the
references to the ComboBoxes.

 

[Guest]

You set-up is slightly different but check the Access Web article:

http://www.mvps.org/access/forms/frm0028.htm

The difference is that in the the article, the cascaded ComboBoxes are on
the same Form while in your set-up, the 2nd ComboBox is on the SubForm
(which is *another* Form inside the main Form) so you need to change the
references to the ComboBoxes.

--
HTH
Van T. Dinh
MVP (Access)






Again a quick response! Thanks!

Maybe I am going about this the wrong way. I checked out the pae you
suggested and I am confused.
Here is what I have that works right now except for the variable I need in
the query.

SELECT RailProducts.ProductName, Categories.BalconyCat, Categories.Standard
FROM RailProducts INNER JOIN (Categories INNER JOIN ProductCategories ON
Categories.CategoriesID = ProductCategories.CategoriesID) ON
RailProducts.ProductID = ProductCategories.RailProductsID
WHERE (((Categories.BalconyCat)=Yes) AND ((Categories.Standard)=Yes));

I would like the Categories.Standard to be able to be changed by the first
combo box on the main form. Sorry if I am not being clear. As you may have
guessed, I know only enough to be dangerous.
Thanks!