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 
(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 Start a New Topic Messages in this topic (3)

Upgrade your account with the latest Yahoo Mail app
Get organized with the fast and easy-to-use Yahoo Mail app. Upgrade today!


.

__,_._,___

Tidak ada komentar:

Posting Komentar