Distinct rows

[hianjali]

Hi All,

Someone please help me. I wrote a query to extract the members where Child DOB is older than parent. It runs really quick but it is returning duplicate rows. Please help.

My Query:

SELECT DistinctROW elgdep.MEM_SOC_SEC_NUM, elgdep.MEM_DEP_CODE, elgdep.MEM_EFFEC_DATE, elgdep.MEM_BIRTH_DATE, elgemp.ENR_BIRTH_DATE, elgadr.erstat, elgadr.erzpcd, elgemp.ENR_GRP_NUMBER, elgdep.MEM_IND_TERM_DATE
FROM (elgadr INNER JOIN elgemp ON elgadr.ernmbr = elgemp.ENR_SEQ_NUMBER) INNER JOIN elgdep ON elgemp.ENR_SEQ_NUMBER = elgdep.MEM_ENROLLE_SEQ_NUM
WHERE (((elgdep.MEM_BIRTH_DATE)>([elgemp].[ENR_BIRTH_DATE]))
AND ((Left([ENR_LOC],1)) Not In ('Y','Z')))
ORDER BY elgemp.ENR_GRP_NUMBER;


My Excel Export:

MEM_EFFEC_DATE MEM_BIRTH_DATE ENR_BIRTH_DATE erstat erzpcd ENR_GRP_NUMBER MEM_IND_TERM_DATE
20020701 19841008 19570310 NV 89704 00005555 99999999
20020701 20020621 19561101 NV 89511 00005555 99999999
0 20001106 19750606 NV 89503 00005555 99999999
0 19751013 19750606 NV 89503 00005555 99999999
20020101 19910712 19570504 CA 92604 00010001 99999999
20020601 19751230 19740508 PA 19020 00010001 99999999
0 19961113 19640128 CA 92692 00010001 99999999
0 19980713 19640128 CA 92692 00010001 99999999
0 19640528 19640128 CA 92692 00010001 99999999
20020101 19910307 19580601 CA 92679 00010001 99999999
20020101 19880831 19580601 CA 92679 00010001 99999999
0 19830919 19611001 CA 90804 00010001 99999999
20010501 19940708 19610319 CO 81001 00010001 99999999
20010501 19940708 19610319 CO 81001 00010001 99999999
20010501 19940708 19610319 CO 81001 00010001 99999999
20010501 19940708 19610319 CO 81001 00010001 99999999
20010501 19940708 19610319 CO 81001 00010001 99999999
20010501 19940708 19610319 CO 81001 00010001 99999999
20010501 19940708 19610319 CO 81001 00010001 99999999
20010501 19950720 19610319 CO 81001 00010001 99999999
20010501 19950720 19610319 CO 81001 00010001 99999999
20010501 19950720 19610319 CO 81001 00010001 99999999
20010501 19950720 19610319 CO 81001 00010001 99999999
20010501 19950720 19610319 CO 81001 00010001 99999999
20010501 19950720 19610319 CO 81001 00010001 99999999
20010501 19950720 19610319 CO 81001 00010001 99999999

 

[lynchg]

Use Select Distinct instead of Distinctrow.