Form filter with INNER JOIN

[whkoh]

I have two tables: Customer and Project with primary keys Customer ID and Project ID respectively.

A third table (Involvement) is the intersection table storing Customer ID and Project ID as foreign keys.

I need to select Project.* given Customer.[Customer Name].

So far my query is

SELECT Project.*
FROM Project INNER JOIN (Customer INNER JOIN Involvement ON (Customer.[Customer ID] = Involvement.[Customer ID]) AND (Customer.[Customer ID] = Involvement.[Customer ID])) ON (Project.[Project ID] = Involvement.[Project ID]) AND (Project.[Project ID] = Involvement.[Project ID])
WHERE (((Customer.[Company Name]) Like "*" & [Forms]![Customer-Project]![Company Name] & "*"));

as built by Access query wizard and it works.

I have to implement the same in a form of the Project Table, but I got stuck on the filter part, because it can only take a WHERE clause.

How do I make the inner joins work on a form filter that is on a different table?

[patjones]

Hi -

If the form is always going to display records according to what the WHERE clause of the query says, you can just set the "Record Source" property of the form to be the query itself.

Another way is to do the same as what I'm saying above, but remove the WHERE clause from the query and put it in the "Filter" property. Doing it this way allows you to use different filters for different sets of circumstances, while keeping the Record Source property the same.

Let me know what you think and we can discuss it further if you have problems.

Pat