473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Form filter with INNER JOIN

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
1 4023
patjones
931 Expert 512MB
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

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

Similar topics

3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
1
by: Jim Andersen | last post by:
Hi, I have this form I want to open, and filter the records. This is the forms recordsource: SELECT A.CustID, A.Name FROM TblCustomers AS A this is a filter-query "qryFindItems": SELECT...
2
by: Sean | last post by:
Greetings all, I am attempting to make a form that will filter through several tables that (I believe) have refretial integrity. I am pulling data from several tables into the form and i would...
3
by: Stewart | last post by:
Hi all! My (relatively small) database holds data on staff members and the projects (services) that they are assigned to. In my form frmStaff, I have a list of staff members - it is a...
3
by: dhowell | last post by:
In reading some of the posts on this group, it appears as though it is not strait forward at all to filter a form, which has subforms, by criteria which are either on subforms or span more than one...
15
by: Bo Diddly | last post by:
Hi everyone, I am relatively new to VB.Net, and database programming. I have a database with four tables, set up as follows: tblBook BookID... key Book... All the book titles of the Bible...
6
by: Dave | last post by:
On my form I have combo boxes. These combo boxes, after updating them, populate respective listboxes that are located below the combo boxes on the same form. I am trying to use a "generate...
5
by: inepu | last post by:
I have 3 tables, Actions, Objects and AO, where "AO" a table that relates Actions and Objects, each row is a pair the other tables' keys) I have a form that is generated with values from Objects,...
3
scubak1w1
by: scubak1w1 | last post by:
Hello, I posted this in the HTML forum, but it was suggested I post it over here as well by a moderator. I have a form that will not 'fire' in non-IE browsers?? I have poked and poked at the code...
1
by: CatchSandeepVaid | last post by:
Say i have one-to-many relationship between Product and ProductNames Product ----> PID (PK) ProductNames------> PID, USECODE, STARTTIME as composite ID and ENDTIME is another normal column. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.