Code value is null in the required table and retrieve those recs with proper desc

[pranaksen]

Hi,



I have a following query and it is working fine.

But, The query retrieves the result based on MasterCodes.CODE_TYPE)="RESUL" and this (MasterCodes.CODE_TYPE is checked with SampleDetails.RESULT_STATUS_1. The table MasterCodes is a code table. It retrieves the information perfectly. However, if result_status_1 is null there is no corresponding entry in master.code.code_type. User is not interested to add corresponding code for Null column in the code table.




My problem is, if result_status_1 is null in my sample table it has to retrieve the corresponding record with Description as " Not in DB"


The query is:

SELECT DISTINCT SampleDetails.UNIT_ID, SampleDetails.COLL_DATE, TestKitReagents.TEST_KIT_ID, TestBatchDetails.TEST_ID, MasterCodes.CODE_DESC, TestBatchDetails.TB_ID, SampleDetails.ABSORBANCE, MasterCodes.CODE_TYPE
FROM (SampleDetails INNER JOIN (TestKitReagents INNER JOIN (ValidReagents INNER JOIN TestBatchDetails ON ValidReagents.TEST_ID = TestBatchDetails.TEST_ID) ON TestKitReagents.TEST_KIT_ID = TestBatchDetails.TEST_KIT_ID) ON (SampleDetails.TEST_ID = TestBatchDetails.TEST_ID) AND (SampleDetails.TB_ID = TestBatchDetails.TB_ID)) INNER JOIN MasterCodes ON SampleDetails.RESULT_STATUS_1 = MasterCodes.CODE
WHERE (((SampleDetails.UNIT_ID)=[Forms]![SampleDetails_Form].[UNIT_ID]) AND ((SampleDetails.COLL_DATE)=[Forms]![SampleDetails_Form].[COLL_DATE]) AND ((MasterCodes.CODE_TYPE)="RESUL"));


In the above query, I want to check SampleDetails.RESULT_STATUS_1 is null then I have to result_status as "NA in DB". I used IIF function and Is null, but couldn't succeed.

Please help me to solve this problem.

Thank you in advance.

[June7]

Why would there not be values in Result_Status_1? Are these two fields primary and foreign key fields?

Is there "NA" value in MasterCodes table? If not, won't do any good to calculate an alternate value for the null Result_Status_1 field. If there is no common value in both tables, records won't join and won't be retrieved.

[rpeare]

Try this: your query is only allowed to show data if there is a match on every table where you seem to be more interested in everything that was run:

SELECT DISTINCT SampleDetails.UNIT_ID, SampleDetails.COLL_DATE, TestKitReagents.TEST_KIT_ID, TestBatchDetails.TEST_ID, MasterCodes.CODE_DESC, TestBatchDetails.TB_ID, SampleDetails.ABSORBANCE, MasterCodes.CODE_TYPE, IIf(IsNull([code_type]),"NA in DB",[code_type]) AS CodeType_Conv
FROM (SampleDetails LEFT JOIN (TestKitReagents RIGHT JOIN (ValidReagents RIGHT JOIN TestBatchDetails ON ValidReagents.TEST_ID = TestBatchDetails.TEST_ID) ON TestKitReagents.TEST_KIT_ID = TestBatchDetails.TEST_KIT_ID) ON (SampleDetails.TB_ID = TestBatchDetails.TB_ID) AND (SampleDetails.TEST_ID = TestBatchDetails.TEST_ID)) LEFT JOIN MasterCodes ON SampleDetails.RESULT_STATUS_1 = MasterCodes.CODE
WHERE (((SampleDetails.UNIT_ID)=[Forms]![SampleDetails_Form].[UNIT_ID]) AND ((SampleDetails.COLL_DATE)=[Forms]![SampleDetails_Form].[COLL_DATE]) AND ((MasterCodes.CODE_TYPE)="resul" Or (MasterCodes.CODE_TYPE) Is Null));