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:


peopleID   PK




PeopleType = Claimant or Respondent


MediationLogID  PK


ClaimantID - Should link to tbl_People with PeopleType of Claimant

RespondentID- Should link to tbl_People with PeopleType of Responent


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