DISTINCT CRITERIA IN QUERY 1

[netrusher]

Below is the SQL that I have for a Query. Is there a way I can get the Distinct Last name without moving the [1-StationVerifyTbl].WorkerNameLast to the first field of the query?

SELECT [1-StationVerifyTbl].Process, [1-StationVerifyTbl].OperationName, [1-StationVerifyTbl].StaPrimary, [1-StationVerifyTbl].StaUp, [1-StationVerifyTbl].StaBack, [1-StationVerifyTbl].StaOther,[b][1-StationVerifyTbl].WorkerNameLast[/b], [1-StationVerifyTbl].WorkerNameFirst
FROM [1-StationVerifyTbl]
WHERE ((([1-StationVerifyTbl].Process) Like "1/*")) OR ((([1-StationVerifyTbl].Process) Like "2/*"))
ORDER BY [1-StationVerifyTbl].Process, [1-StationVerifyTbl].OperationName, [1-StationVerifyTbl].StaPrimary, [1-StationVerifyTbl].StaUp, [1-StationVerifyTbl].StaBack, [1-StationVerifyTbl].StaOther;

 

[Golom]

The DISTINCT clause works on ALL the fields in the select ... not just the first one. It will produce a single record for each unique combination of those fields.

The real problem is that, with only unique values for WorkerNameLast, what values do you expect to appear for the other fields in the SELECT? You would need to do something like

SELECT MAX(Process) As MaxProcess
     , MAX(OperationName) As MaxOp
     , MAX(StaPrimary) As MaxPrimary
     , MAX(StaUp) As MaxUp
     , MAX(StaBack) As MaxBack
     , MAX(StaOther) As MaxOther
     , WorkerNameLast
     , MAX(WorkerNameFirst) As MaxFirst

FROM [1-StationVerifyTbl]

WHERE Left(Process,2) IN ("1/", "2/")

GROUP BY WorkerNameLast

ORDER BY 1, 2, 3, 4, 5, 6;

which will give distinct last names but probably will not produce the results that you want because it eliminates many of the values in the other fields.

 

[netrusher]

Thanks,

I will give that a try.