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?