I am currently updating a project I inherited. I am trying to simplify some of the queries and I come accross this:
Which gives this result:
As you can see, there are 2 records for WeldNo FW-54 and FW-61. How can I modify it to output only the latest record for those WeldNos without affecting the other records? I hope it makes sense.
Code:
SELECT DISTINCT DWR_1.NDEQRNo, DWR_1.ID AS RecID, DWR_Spools.ProjID, DWR_1.StatCode,
DWR_Spools.DwgNo, DWR_Spools.SpoolNo, ([DWR_Spools]![WeldNoPrefix]+"-") &
[DWR_Spools]![WeldNoType] & "-" & [DWR_Spools]![WeldNoSeq] & ("-"+[DWR_Spools]![WeldNoSuffix])
AS WeldNo2, DWR_Joint.Sortx1, DWR_1.DateWeld, DWR_1.NDERequestDate
FROM DWR_Joint INNER JOIN (DWR_Spool INNER JOIN (DWR_Spools
LEFT JOIN DWR_1 ON DWR_Spools.ID = DWR_1.SpoolID) ON DWR_Spool.SpoolNo = DWR_Spools.SpoolNo)
ON DWR_Joint.ID = DWR_Spools.ID
WHERE (((DWR_Spools.ProjID)=7))
ORDER BY DWR_Spools.DwgNo, DWR_Spools.SpoolNo, DWR_Joint.Sortx1, DWR_1.DateWeld, DWR_1.NDERequestDate;
Which gives this result:
As you can see, there are 2 records for WeldNo FW-54 and FW-61. How can I modify it to output only the latest record for those WeldNos without affecting the other records? I hope it makes sense.