NEED A SINGLE RECORD RETURNED FROM ONE TO MANY QUERY

[Guest]

I am having a mental block as to how to return just 1 instance of records
from the INVESTIGATOR table. Each investigator may have many projects, that
is captured in a JOINING table using the relationshipID. The JOINING table
is my middle table of a Many-to-Many Relationship with a table PROJECTS.

Presently, I'm getting multiple instances of INVESTIGATORS because they
obviously work multiple PROJECTS.

Here is the SQL from my query thus far:
SELECT tbl_Investigator.*, lk_Join.InvestigatorRelationID
FROM tbl_Investigator INNER JOIN lk_Join ON tbl_Investigator.InvestigatorID
= lk_Join.InvestigatorID
WHERE (((lk_Join.InvestigatorRelationID)=1));

Again; I am trying to create a query that will return only a single record
from the INVESTIGATOR table.

Thank you.
Bill

 

[Allen Browne]

One investigator can be involved in many projects, so how to you decide
*which* one to return?

The answer will probably involve a subquery, asking for the TOP 1.
See:
Subquery Basics: Top N records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

 

[John Spencer]

Add the distinct key word (record will not be updateable)

SELECT DISTINCT tbl_Investigator.*
, lk_Join.InvestigatorRelationID
FROM tbl_Investigator INNER JOIN lk_Join
ON tbl_Investigator.InvestigatorID = lk_Join.InvestigatorID
WHERE (((lk_Join.InvestigatorRelationID)=1));

Or Use a subquery if you want the tbl_Investgator records to be updateable
SELECT Tbl_Investigator.*
FROM tbl_Investigator
WHERE InvestigatorID in
(SELECT InvestigatorID
FROM lk_Join
WHERE InvestigatorRelationID =1)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thank you John for your advice and time. I will try your suggestions as I
move forward.

Take care,
Bill

 

[Guest]

Allen:
Thanks for pointing me in a direction I hadn't considered.

Thanks again,
Bill