sql question

[Thomas L]

Hi,

i have an access database with a project table, a contacts table, and a
table which links several contacts to the project in a certain role. For
example it links Tom to the project as project leader, it links Larry to the
project as External Contact and Pete as Internal Contact.

Now i have a search form where i can find projects based on several data.
Now it's possible to search on one contact at a time. We want to make it
possible to search on 2 contacts at the same time.

The person who created this app made an sql-query with LEFT JOIN's from the
project table to the linktable. Now when i try to make it search on two
contacts, it doesn't return any data, allthough i know the data is there.

What i want to do is find a project where Tom and Larry are linked as
contacts.

Anyone able to give me a clue on how to do this?

Regards,

Thomas L.

 

[James Goodman]

Can you post the SQL for the query?


SELECT * FROM Table WHERE Contact = 'Tom' OR Contact = 'Larry'

 

[Thomas L]

Hi,

i wish it was that simple. But i have a table called Projects and a table
called Project_Contacts and a table called Contacts.

Now i have a record in Projects, and then i have several records in
Project_Contacts linking different contacts to that project.

For example in table Projects i have

Project_id---Project_name
1------------My Project

and then in Project_Contacts i have

Project_id-----Contact_id----Role
1------------------5----------Leader
1------------------6----------Representative
1------------------3----------Secretary

etc...

and in the table Contacts i have

Contact_id-------Name
3-----------------Ann
5-----------------Peter
6-----------------Charly

etc...

Now i want to search my database for the project where Ann AND Charly are
connected to. Not where one or the other is connected to.

REgards,

Thomas L.

 

[James Goodman]

Ahh, ok. In that case you might want to use subqueries:

SELECT DISTINCT Projects.ProjectName
FROM (Project_Contacts INNER JOIN Projects ON Project_Contacts.Project_ID =
Projects.Project_ID) INNER JOIN Contacts ON Project_Contacts.Contact_ID =
Contacts.Contact_ID
WHERE (((Projects.ProjectName) In (SELECT DISTINCT Projects.ProjectName FROM
(Project_Contacts INNER JOIN Projects ON Project_Contacts.Project_ID =
Projects.Project_ID) INNER JOIN Contacts ON Project_Contacts.Contact_ID =
Contacts.Contact_ID WHERE (((Contacts.ContactName)="ann"))) AND
((Contacts.ContactName)="charlie"));


This would return a list of ProjectNames which Ann & Charlie are working on.
It is a little unwieldy though...

 

[Thomas L]

thx very much! this was what i was looking for!

Regards,

Thomas