SQL Distinct

[dgkindy]

I am running a query in a combo box but for some reason the following SQL statement does not give me the result I am after. Which is to only displaying the distinct records. It continues to display the multiple occurrences of the records. I also tried distinctrow with no better results.

SELECT DISTINCT tblPart.PartID, [tblPart]![Part_Number] & " - " & [tblPart]![Description] AS Expr1, tblProject.ProjectID
FROM (tblProject INNER JOIN tblSkid ON tblProject.ProjectID = tblSkid.ProjectID) INNER JOIN tblPart ON tblSkid.SkidID = tblPart.SkidID
WHERE (((tblProject.ProjectID)=[Forms]![frmPartReview]![cboProjects]))
ORDER BY [tblPart]![Part_Number] & " - " & [tblPart]![Description];

 

[Moniker]

SELECT DISTINCT takes all of the columns and compares them, not just a specific field. For example, all of these will be returned:

PartID   Expr1     ProjectID
1        2-Thing   1
1        2-Thing   2
1        2-Thing   3

The entire record has to be a duplicate for DISTINCT to catch it, not just a single field. The following only return two of the three records since the last two are identical:

PartID   Expr1     ProjectID
1        2-Thing   1
1        2-Thing   2
1        2-Thing   2

 

[Kamolas]

I have a similar problem. Is there a way to show all fields where only one field is distinct?

 

[boblarson]

Yes, create a select distinct query on only that field and then create a query that ties the original table back with the new query and if joined with an inner join it will give you only the records that occur in each.

 

[Kamolas]

Thks. I will try it