Avatar of wkraft
wkraft

asked on 

flter columns based off information from another column

This is the SQL statement I have so far . . .

SELECT BCDW_PROD_V_VL_CAPTURE_CONTROL_V.EPD_IDENTIFIER, BCDW_PROD_V_VL_CAPTURE_CONTROL_V.RECEIVING_SUPPLIER_NAME, BCDW_PROD_V_VL_CAPTURE_CONTROL_V.EPD_STATUS, BCDW_PROD_V_VL_CAPTURE_CONTROL_V.NOTICE_RESPONSE_DUE_DT, BCDW_PROD_V_VL_CAPTURE_CONTROL_V.LINE_NUMBER, BCDW_PROD_V_VL_EPD_STAGE_ACTION_V.STAGE_NAME, BCDW_PROD_V_VL_EPD_STAGE_ACTION_V.STAGE_STATUS, BCDW_PROD_V_VL_EPD_STAGE_ACTION_V.STATUS_CHANGE_TS, BCDW_PROD_V_VL_EPD_STAGE_ACTION_V.CURRENT_WORK_CENTER, BCDW_PROD_V_VL_CAPTURE_CONTROL_V.NONCONFORMING_ITEM_DESC, BCDW_PROD_V_VL_CAPTURE_CONTROL_V.EVALUATION_NOTES
FROM BCDW_PROD_V_VL_CAPTURE_CONTROL_V LEFT JOIN BCDW_PROD_V_VL_EPD_STAGE_ACTION_V ON BCDW_PROD_V_VL_CAPTURE_CONTROL_V.EPD_IDENTIFIER = BCDW_PROD_V_VL_EPD_STAGE_ACTION_V.EPD_IDENTIFIER
WHERE (((BCDW_PROD_V_VL_CAPTURE_CONTROL_V.RECEIVING_SUPPLIER_NAME) Like "Vought*"))
ORDER BY BCDW_PROD_V_VL_CAPTURE_CONTROL_V.EPD_IDENTIFIER;

The BCDW_PROD_V_VL_CAPTURE_CONTROL_V.EPD_IDENTIFIER column contains many duplicate numbers.  I need one distinct number from each group of duplicates in the column based on the following criteria:

I only need the number if the corresponding field in the BCDW_PROD_V_VL_CAPTURE_CONTROL_V.EPD_STATUS column is OPEN, and if the OPEN is the latest entry (for the group of duplicate numbers) in the BCDW_PROD_V_VL_EPD_STAGE_ACTION_V.STATUS_CHANGE_TS column.
Microsoft Access

Avatar of undefined
Last Comment
wkraft

8/22/2022 - Mon