Removing duplicates

[annysjunkmail]

Hi Group,

I'm trying desparetly to remove duplicates from the query listed below.
The query returns 39 records but I know that there are exactly 38
unique records (ApplicationRefNo is the primary field).

Applications may contain more than one category but I only want to show
unique applications if it has more than one category.

I am using distinct clause but to no avail. I have then used Group By
and selecting First but it shows 37 instead of 38 records?Can someone
help - pretty please

Regards
Chris


SELECT DISTINCTROW tblApplication.ApplicationRefNo,
tblApplication.ApplicationStatus, tblRDPApplRDPCategory.RDPCategory,
tblApplication.Programme, tblMeasure.MeasureCode
FROM ((((tblApplication LEFT JOIN tblProgramme ON
tblApplication.Programme = tblProgramme.OperationalProgrammeCode) LEFT
JOIN tblPriority ON (tblApplication.Priority =
tblPriority.PriorityCode) AND (tblApplication.Programme =
tblPriority.OperationalProgrammeCode)) LEFT JOIN tblMeasure ON
(tblApplication.Measure = tblMeasure.MeasureCode) AND
(tblApplication.Priority = tblMeasure.PriorityCode) AND
(tblApplication.Programme = tblMeasure.OperationalProgrammeCode)) LEFT
JOIN tblDestination ON (tblApplication.DestCode =
tblDestination.DestCode) AND (tblApplication.IBCode =
tblDestination.IBCode) AND (tblApplication.Measure =
tblDestination.MeasureCode) AND (tblApplication.Priority =
tblDestination.PriorityCode) AND (tblApplication.Programme =
tblDestination.ProgrammeCode)) LEFT JOIN tblRDPApplRDPCategory ON
tblApplication.ApplicationRefNo =
tblRDPApplRDPCategory.ApplicationRefNo
WHERE (((tblApplication.ApplicationStatus)="looaccepted" Or
(tblApplication.ApplicationStatus)="completed" Or
(tblApplication.ApplicationStatus)="partbreceived" Or
(tblApplication.ApplicationStatus)="looissued") AND
((tblRDPApplRDPCategory.RDPCategory)="rdp05") AND
((tblApplication.Programme)="001" Or (tblApplication.Programme)="006"))
OR (((tblApplication.ApplicationRefNo)="003598") AND
((tblApplication.ApplicationStatus)="looaccepted" Or
(tblApplication.ApplicationStatus)="completed" Or
(tblApplication.ApplicationStatus)="partbreceived" Or
(tblApplication.ApplicationStatus)="looissued") AND
((tblApplication.Programme)="001" Or (tblApplication.Programme)="006"))
OR (((tblApplication.ApplicationRefNo)="018817") AND
((tblApplication.ApplicationStatus)="looaccepted" Or
(tblApplication.ApplicationStatus)="completed" Or
(tblApplication.ApplicationStatus)="partbreceived" Or
(tblApplication.ApplicationStatus)="looissued") AND
((tblApplication.Programme)="001" Or (tblApplication.Programme)="006"))
OR (((tblApplication.ApplicationRefNo)="018978") AND
((tblApplication.ApplicationStatus)="looaccepted" Or
(tblApplication.ApplicationStatus)="completed" Or
(tblApplication.ApplicationStatus)="partbreceived" Or
(tblApplication.ApplicationStatus)="looissued") AND
((tblApplication.Programme)="001" Or (tblApplication.Programme)="006"))
OR (((tblApplication.Programme)="002") AND
((tblMeasure.MeasureCode)="002")) OR
(((tblApplication.Programme)="003") AND ((tblMeasure.MeasureCode)="005"
Or (tblMeasure.MeasureCode)="006" Or (tblMeasure.MeasureCode)="007"));

 

[KARL DEWEY]

Below will find your duplicates --

SELECT Table1.xx, Count(Table1.xx) AS CountOfxx
FROM Table1
GROUP BY Table1.xx
HAVING (((Count(Table1.xx))>1));