Show only Earliest Date in a Query 1

[BDLand]

I have a simple query that now shows part number and each day it is required. I would like to only show the earliest date that each part number is required (filtering out all later dates) (should result in only unique part number and the earliest date required). I am new at Access so it is probably something simple I am overlooking.

 

[Remou]

Create a query grouped by part number and select Min under the required date.

 

[BDLand]

Thanks Remou, That has gotten me a lot closer, but I am still returning "some" multiple results on some part numbers. I posted my code below, if you could provide a little more help it would be appreciatted.

SELECT DISTINCT ORtg.[Part Number], Prmspm.Description, Min(MRP.[Due Date]) AS [MinOfDue Date]
FROM MRP INNER JOIN (PS INNER JOIN (Rtngs INNER JOIN (Prmspm INNER JOIN ORtg ON Prmspm.[Part Number] = ORtg.[Part Number]) ON Rtngs.[Part Number] = ORtg.[Part Number]) ON PS.Child = ORtg.[Part Number]) ON MRP.[Part Number] = PS.Parent
GROUP BY ORtg.[Part Number], Prmspm.Description, Prmspm.[Product Class], Rtngs.Work, PS.Parent
HAVING (((Prmspm.[Product Class])<>"00") AND ((Rtngs.Work)<>"  0") AND ((PS.Parent) Like "S*"));

 

[Remou]

Distinct is not necessary because you are grouping. There is no need to group by Prmspm.[Product Class], Rtngs.Work or PS.Parent.

[tt]SELECT DISTINCT ORtg.[Part Number], Prmspm.Description, Min(MRP.[Due Date]) AS [MinOfDue Date]
FROM MRP INNER JOIN (PS INNER JOIN (Rtngs INNER JOIN (Prmspm INNER JOIN ORtg ON Prmspm.[Part Number] = ORtg.[Part Number]) ON Rtngs.[Part Number] = ORtg.[Part Number]) ON PS.Child = ORtg.[Part Number]) ON MRP.[Part Number] = PS.Parent
WHERE (((Prmspm.[Product Class])<>"00") AND ((Rtngs.Work)<>" 0") AND ((PS.Parent) Like "S*"))
GROUP BY ORtg.[Part Number], Prmspm.Description;[/tt]

Is it possible for a part to have more than one description? If so, that may be a problem.

The
Microsoft: Access Queries and JET SQL Forum: forum701
is very good for queries/SQL.