Need help with SQL Select statement

[maxf]

Im trying to use this statement:

SELECT Distinct D.DocumentID,D.FileName,D.Title,D.ShortDescription,D.DatePublished,DTR.DocumentID,DT.DocumentType,DT.ThumbNailImage from Documents D, DocumentSubjectAreas DSA, DocumentSubjectAreasREL DSAR, DocumentTypes DT, DocumentTypesRel DTR WHERE Instr(Lcase(DSA.SubjectArea), 'new') > 0 AND D.DocumentID=DSAR.DocumentID And DSAR.SubjectAreaID=DSA.SubjectAreaID And D.DocumentID=DTR.DocumentID AND DT.DocumentTypeID=DTR.DocumentTypeID Order By D.DatePublished desc



The problem is that it doesnt return only distinct values for DocumentID. The table Documents, contains the DocumentID primary key column. The table DocumentTypesREL holds the relation between document types and documents. Therefore, a document may have multiple document types and therefore the DocumentID key will be found in DocumentTypesRel multiple times, however, I need to only select distinct DocumentID's from DocumentTypesRel. I cant seem to figure out why my statement isnt doing this. Can anyone help?

Thanks, lance

 

[Golom]

From the Access HELP on DISTINCT ...

If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results.

In your case it is the combination of ALL fields that is not unique (i.e. DISTINCT) and therefore the records with different values for "D.DocumnetID" are appearing. If it is the "DT.DocumentType" field that is different on each record for a given DocumentID then you need to remove that field from your select statement to get DISTINCT values for DocumentID.