Many to many relathionship filter?

  • Thread starter Thread starter ChrisPapad
  • Start date Start date
C

ChrisPapad

I have 3 tables as follows:

Products (ID,Name)
Categories (ID,Name)
ProductCategory (ID,CategoryID,ProductID)

There is a many to many relationship between Products and Categories.

How could i filter the products shown in a form by category or
categories? Is there a standard practice?

Chris
 
The Filter of the form can be anything you can use in the WHERE clause of a
query. You are allowed to use a subquery in the WHERE clause, so you can use
a subquery as the Filter of your form.

This example assumes a form bound to the product table (no reference to
categories anywhere), but filters to form to those products in cagegories 3,
6, and 99:

Me.Filter = "EXISTS (SELECT ProductID FROM ProductCategory WHERE
((ProductCategory.ProductID = Products.ID) AND (ProductCategory.CategoryID
IN (3, 6, 99))))"
Me.FilterOn = True

If subqueries are new here is Microsoft's introductory article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Allen said:
The Filter of the form can be anything you can use in the WHERE clause of a
query. You are allowed to use a subquery in the WHERE clause, so you can use
a subquery as the Filter of your form.

This example assumes a form bound to the product table (no reference to
categories anywhere), but filters to form to those products in cagegories 3,
6, and 99:

Me.Filter = "EXISTS (SELECT ProductID FROM ProductCategory WHERE
((ProductCategory.ProductID = Products.ID) AND (ProductCategory.CategoryID
IN (3, 6, 99))))"
Me.FilterOn = True

If subqueries are new here is Microsoft's introductory article:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
Click to expand...

Got it!Thanks!

Chris
 
Back
Top