By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,599 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Form filter with INNER JOIN

P: 1
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
Expand|Select|Wrap|Line Numbers
  1. SELECT Project.*
  2. 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])
  3. WHERE (((Customer.[Company Name]) Like "*" & [Forms]![Customer-Project]![Company Name] & "*"));
  4.  
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?
Mar 21 '10 #1
Share this Question
Share on Google+
1 Reply


patjones
Expert 100+
P: 931
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
Mar 22 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.