Remove/omit duplicate records in MS Access Query result

[ZAVILA]

This is the SQL code for starters.

SELECT DISTINCTROW [Qry_CRM_MNGT_150+_No_Action_NEW].CYCLE_DATE, [Qry_CRM_MNGT_150+_No_Action_NEW].SERIES_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].SERVICER_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].SERVICER_NAME, [Qry_CRM_MNGT_150+_No_Action_NEW].SERVICER_LOAN_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].LOAN_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].INTERNAL_LOAN_ID, [Qry_CRM_MNGT_150+_No_Action_NEW].ARCC_LOAN_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].LIEN_STATUS_CODE, [Qry_CRM_MNGT_150+_No_Action_NEW].MaxOfACTL_END_PRIN_BAL, [Qry_CRM_MNGT_150+_No_Action_NEW].REPORTED_ACTION_CODE, [Qry_CRM_MNGT_150+_No_Action_NEW].MONTHS_DELQ, IIf([COMMENT_CODE]="CX",[COMMENT_DATE_TIME],"") AS NEW_COMMENT_DATE_TIME, (IIf([COMMENT_CODE]="CX",[STAFF_NAME],"")) AS NEW_STAFF_NAME, (IIf([COMMENT_CODE]="CX",[COMMENT_TEXT],"")) AS NEW_COMMENT_TEXT, (IIf([COMMENT_CODE]="CX","CX","")) AS NEW_COMMENT_CODE
FROM ([Qry_CRM_MNGT_150+_No_Action_NEW] LEFT JOIN MARS_COMMENTS_INFO ON [Qry_CRM_MNGT_150+_No_Action_NEW].INTERNAL_LOAN_ID = MARS_COMMENTS_INFO.INTERNAL_LOAN_ID) LEFT JOIN MARS_IFS_STAFF_INFO ON MARS_COMMENTS_INFO.COMMENT_AUTHOR = MARS_IFS_STAFF_INFO.INTERNAL_STAFF_LOGON
GROUP BY [Qry_CRM_MNGT_150+_No_Action_NEW].CYCLE_DATE, [Qry_CRM_MNGT_150+_No_Action_NEW].SERIES_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].SERVICER_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].SERVICER_NAME, [Qry_CRM_MNGT_150+_No_Action_NEW].SERVICER_LOAN_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].LOAN_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].INTERNAL_LOAN_ID, [Qry_CRM_MNGT_150+_No_Action_NEW].ARCC_LOAN_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].LIEN_STATUS_CODE, [Qry_CRM_MNGT_150+_No_Action_NEW].MaxOfACTL_END_PRIN_BAL, [Qry_CRM_MNGT_150+_No_Action_NEW].REPORTED_ACTION_CODE, [Qry_CRM_MNGT_150+_No_Action_NEW].MONTHS_DELQ, IIf([COMMENT_CODE]="CX",[COMMENT_DATE_TIME],""), (IIf([COMMENT_CODE]="CX",[STAFF_NAME],"")), (IIf([COMMENT_CODE]="CX",[COMMENT_TEXT],"")), (IIf([COMMENT_CODE]="CX","CX","")); 

The purpose of this query is to identify the records that meet the specified parameters, with it's respective comment (s)if there is(are) any, and also the those records that have no comment (null) but meet the parameters. The problem I have is that for records that have a comment(s), and additional record shows up in the results with null comment value.

I am trying to figure out how I can eliminate the duplicate record with the Null comment value if that same record has a comment already showing in the results.

[nico5038]

Looks to me you can have multiple comments.
If that's the case and one has a code <> CX it will add an empty row.

One solution is to use a groupby query to filter the comments (MARS_COMMENTS_INFO) for "CX" and select the comment of the MAX(COMMENT_DATE_TIME). (So first a groupby on INTERNAL_LOAN_ID with the MAX(COMMENT_DATE_TIME) for the "CX" comments and that query JOINED by INTERNAL_LOAN_ID and MAX(COMMENT_DATE_TIME) to get the (single) "CX" comment.
When the COMMENT_DATE_TIME is unique this will work, otherwise I would add also a MAX(COMMENT_TEXT).

Getting the idea ?

Nic;o)