SQL question

A

azu_daioh

Hi All,

I have this query:

SELECT "ID Referral" as TableName, logID, actID, caseNum FROM
[tbl_logReferralAction] WHERE [caseNum] = Forms!frm_AdvSearch!
findKeyword
UNION ALL SELECT "Doc Request" as TableName, logID, refNum, caseNum
FROM [tbl_LogDocRequest] WHERE [caseNum] = Forms!frm_AdvSearch!
findKeyword;

But, how do I display additional fields from another tables:
From [tbl_Claimant] (pk: clmtID) I want to add these fields
[clmtFName] and [clmtLName]
From [tbl_LogBasic] (pk: logID), I want to add these fields [logSSN]
From [tbl_Claim] (pk: clmID), I want to add these fields [ofcNum] and
[effDate]

The [tbl_LogBasic] has fk: clmID and clmtID.


Any help is greatly appreciated.
Thank you,
 
K

KARL DEWEY

You need to join the tables but you did not supply enough information to
complete the query. Here is a start --
SELECT "ID Referral" as TableName, logID, actID, caseNum, [logSSN]
FROM [tbl_logReferralAction] LEFT JOIN [tbl_LogBasic] ON
[tbl_logReferralAction].logID = [tbl_LogBasic]. logID
WHERE [caseNum] = Forms!frm_AdvSearch!findKeyword
UNION ALL SELECT "Doc Request" as ………..
 
A

azu_daioh

Thanks Karl. It worked when adding [logSSN] from the [tbl_LogBasic].

How about adding more fields from other tables? Here are the tables
and fields in my DB.
[tbl_LogBasic] = logID, clmtID, clmID, logSSN
[tbl_Claimant] = clmtID, LName, FName
[tbl_ClaimDetails] = clmID, effDate, ofcNum

The above query displays the following fields only
TableName, logID, refNum, caseNum, logSSN

But, I would like it to display all these fields:
TableName, logID, refNum, caseNum, logSSN, LName, FName, effDate,
ofcNum

Thanks again,
Sei
 
A

azu_daioh

Nevermind. I found a solution after researching more about LEFT JOIN.

Thanks again for the help!!!

Sei
 
Top