ACCESS FEED: Re: [MS_AccessPros] Pulling two different names in one query

Kamis, 28 April 2016

Re: [MS_AccessPros] Pulling two different names in one query

John,

Thank you. This is the final code:

SELECT tbl_mediation_log.MediationLog_ID, tbl_mediation_log.Case_Number, IIf(IsNull([tbl_People].[MiddleName]),[tbl_People].[LastName] & ", " & [tbl_People].[FirstName],[tbl_People].[LastName] & ", " & [tbl_People].[FirstName] & " " & [tbl_People].[MiddleName]) AS cClaimantName, IIf(IsNull([Respondent].[MiddleName]),[Respondent].[LastName] & ", " & [Respondent].[FirstName],[Respondent].[LastName] & ", " & [Respondent].[FirstName] & " " & [Respondent].[MiddleName]) AS cRespondentName, tbl_people.Salutation, tbl_people.FirstName, tbl_people.MiddleName, tbl_people.LastName, tbl_people.Suffix, tbl_mediation_log.Claimant_Petitioner_Name_ID, tbl_mediation_log.Respondent_Name_ID, tbl_mediation_log.Case_Summary, tbl_mediation_log.Facts, tbl_mediation_log.Claimant_Case, tbl_mediation_log.Respondent_Case, tbl_mediation_log.TERO_Ordinance, tbl_mediation_log.Applicable_TERO_Ordinance, tbl_mediation_log.Remarks, tbl_mediation_log.Decision, tbl_mediation_log.Stipulated_Res_Claimant_flag, tbl_mediation_log.Stipulated_Res_Respondent_flag, tbl_mediation_log.CaseDate
FROM tbl_people AS Respondent INNER JOIN (tbl_people INNER JOIN tbl_mediation_log ON tbl_people.PeopleID = tbl_mediation_log.Claimant_Petitioner_Name_ID) ON Respondent.PeopleID = tbl_mediation_log.Respondent_Name_ID;

The actual name is Respondent_Name_ID

With Warm Regards,

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265 Ext. 130

Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."

From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, April 28, 2016 1:44 AM
Subject: Re: [MS_AccessPros] Pulling two different names in one query

Add a second copy of tbl_People:

FROM (tbl_people As Claimant INNER JOIN tbl_mediation_log

ON Claimaint.PeopleID = tbl_mediation_log.Claimant_Petitioner_Name_ID)

INNER JOIN tbl_People AS Respondent

ON Respondent.PeopleID = tbl_mediation_log.RespondentID

Note that I gave each copy of tbl_people an alias name so that you can tell which is which.

BTW, in your list of fields below, you say it's ClaimantID, but you use Claimant_Petitioner_Name_ID in your SQL. Have no clue what is the actual name for "RespondentID" in tbl_Mediation_log.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Apr 28, 2016, at 7:42 AM, dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have the following tables:

tbl_People

peopleID PK

FirstName

LastName

MiddleInitial

PeopleType = Claimant or Respondent

tbl_mediation_log

MediationLogID PK

CaseNumber'

ClaimantID - Should link to tbl_People with PeopleType of Claimant

RespondentID- Should link to tbl_People with PeopleType of Responent

Facts

Case Note

I started on a query to try and pull it out:

SELECT tbl_mediation_log.MediationLog_ID, tbl_mediation_log.Case_Number, IIf(IsNull([tbl_People].[MiddleName]),[tbl_People].[LastName] & ", " & [tbl_People].[FirstName],[tbl_People].[LastName] & ", " & [tbl_People].[FirstName] & " " & [tbl_People].[MiddleName]) AS cClaimantName, tbl_people.Salutation, tbl_people.FirstName, tbl_people.MiddleName, tbl_people.LastName, tbl_people.Suffix, tbl_mediation_log.Claimant_Petitioner_Name_ID, tbl_mediation_log.Respondent_Name_ID, tbl_mediation_log.Case_Summary, tbl_mediation_log.Facts, tbl_mediation_log.Claimant_Case, tbl_mediation_log.Respondent_Case, tbl_mediation_log.TERO_Ordinance, tbl_mediation_log.Applicable_TERO_Ordinance, tbl_mediation_log.Remarks, tbl_mediation_log.Decision, tbl_mediation_log.Stipulated_Res_Claimant_flag, tbl_mediation_log.Stipulated_Res_Respondent_flag, tbl_mediation_log.CaseDate
FROM tbl_people INNER JOIN tbl_mediation_log ON tbl_people.PeopleID = tbl_mediation_log.Claimant_Petitioner_Name_ID;

I need to add this in:

]),[tbl_People].[LastName] & ", " & [tbl_People].[FirstName],[tbl_People].[LastName] & ", " & [tbl_People].[FirstName] & " " & [tbl_People].[MiddleName]) AS cRespondentName

But I need to pull the correct person out of the tbl_people.

Any ideas or even doable.

Thank you,

Art Lorenzini

Sioux Falls, SD

__._,_.___

Posted by: Art Lorenzini <dbalorenzini@yahoo.com>

Reply via web post

•

Reply to sender

•

Reply to group