MS Access SQL Duplicate Rows?

Question

Query:

SELECT DISTINCT ([Equipment List].ID) AS Expr1, [Job Assignments].Job
FROM [Equipment List] LEFT JOIN [Job Assignments] 
    ON [Equipment List].ID = [Job Assignments].EquipmentID;

In this query, the equipment ID is distinct if and only if I do not add the [Job Assignments].Job in the select statement. As soon as I do, I get many duplicates. The Job Assignments table has many jobs, but I am only interested in the one with the [Date Returned] blank or null. Any ideas how to accomplish this? I am using this as a report, so It gives me an error when I try to use inner sql queries. Also this is a simplified query, as the original has multiple selects. What am I doing wrong? Any help would be greatly appreciated.

Update: I am looking to select only one row from [Job Assignments] Table. Any ideas?

Answer 1

DISTINCT returns distinct combinations of all columns in the SELECT statement, which is why introducing a second column also introduces dupes.

It sounds like you can get away with a WHERE clause:

SELECT DISTINCT ([Equipment List].ID) AS Expr1, [Job Assignments].Job 
FROM [Equipment List] 
LEFT JOIN [Job Assignments] ON [Equipment List].ID = [Job Assignments].EquipmentID
WHERE [Job Assignments].[Date Returned] IS NULL

You can't have a "blank" date, so if you're seeing blanks then chances are you're not using a datetime type to store your date - in this case, you'll need to say:

WHERE COALESCE([Job Assignments].[Date Returned], '') = ''

Edit: Latest solution based on comments below:

;WITH LatestJobs
AS
(SELECT  ja.EquipmentID, ja.Job,
      ROW_NUMBER() OVER ( PARTITION BY EquipmentID 
           ORDER BY [Date Returned] DESC) AS RowNumber
FROM     [Job Assignments] AS ja)

SELECT   el.ID AS EquipmentID, 
      lj.Job
FROM     [Equipment List] AS el
LEFT JOIN   LatestJobs AS lj ON el.ID = lj.EquipmentID
      AND lj.RowNumber = 1

Answer 2

What are the datatypes of each field on the select? If it is longchar(sql) or memo(in access) you will not be able to use the distinct function.

Answer 3

I am trying to give away coupons based on years of service & i wrote this query: PARAMETERS [please enter cutoff date] DateTime; SELECT ([F_Name] & " " & [L_Name]) AS [Employee Name], [Employee Details].O_H_DT, [Employee Details].M_R_H_DT, [Employee Details].Termination_check, Int((([please enter cutoff date]-[m_R_H_DT])/365)) AS [no of Years], Int((([please enter cutoff date]-[m_R_H_DT])/365)/3) AS [nos of coupens], ([please enter cutoff date]+365) AS [Coupen Expiry], IIf([nos of coupens]>4,4,[nos of coupens]) AS noofcoupens, Choose([noofcoupens],"One","Two","Three","Four") AS inwords FROM [Employee Details] WHERE ((([Employee Details].Termination_check)=False) AND ((Int((([please enter cutoff date]-[m_R_H_DT])/365)))>=3)); now manager wants to print number of copons based on copon entitlement as they can attach it with timesheet, any idea how i can generate duplicate coupons based on their entitlements.

Thank you