How to get DISTINCT to work properly?

[d4zza]

I'm trying to run a query on specific archived documents (relating to networking documents) where the reference number may have more than one superseded version, but I don't want to return duplicate reference number records, I only need to see the reference number once, the version or date isn't important. I can't seem to run a query using "SELECT DISTINCT" where I'm selecting more than one field.

This code returns all unique reference numbers...

SELECT DISTINCT Documents.ReferenceNumber
FROM Documents
WHERE (((Documents.ReferenceNumber)) Like "*" & "/NET/" & "*"));

But I want to add several other fields to my query output, such as Title and Type, all of which should be exactly the same no matter which version or record number.

The code I have that DOESN'T work, it will return duplicates...

SELECT DISTINCT Documents.ReferenceNumber, Documents.Title, Documents.Type
FROM Documents
WHERE (((Documents.ReferenceNumber) Like "*" & "/NET/" & "*"));

Any help would be appreciated.

thanks...
Daryl

[patjones]

Hi Daryl,

Would you be able to post some sample data? In particular, some rows from the Documents table (including column names), as well as some sample output from the query.

I'm asking you to do this because when I run a similar query in my test database it works fine. I would like to look and see if there are subtle differences in any of your SELECTed columns that could cause the DISTINCT to not work properly.

Pat