Kamis, 28 April 2016

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: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)

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