Distinct PartNumber from Linked Tables 1

[netrusher]

Below is the SQL of a query I have. I have linked three tables at the PartNumber level. What I am trying to get is
a unique PartNumber. I added Distinct beofre the first SpedListTrackingTBL.PartNumber but that did not help. I realize that duplicate PartNumbers might have different other Fields associated with it but regardless of the other Fields I would like to see each PartNumber only once with the earliest Due Date associated to it. Any suggestions as how to accomplish this? Thanks for your help!

SELECT SpedListTrackingTBL.PartNumber, Prmspm.Description, MRP.[Due Date], SpedListTrackingTBL.MastAssy, SpedListTrackingTBL.[One-ThreeTon], SpedListTrackingTBL.[Four-SevenTon], SpedListTrackingTBL.[Seven-NineTon], SpedListTrackingTBL.Fab, SpedListTrackingTBL.Paint, SpedListTrackingTBL.MastWeld, SpedListTrackingTBL.Machine
FROM Prmspm INNER JOIN (MRP INNER JOIN SpedListTrackingTBL ON MRP.[Part Number] = SpedListTrackingTBL.PartNumber) ON Prmspm.[Part Number] = SpedListTrackingTBL.PartNumber
ORDER BY MRP.[Due Date];

 

[PHV]

And what about something like this ?
SELECT SpedListTrackingTBL.PartNumber, Prmspm.Description, MRP.[Due Date], SpedListTrackingTBL.MastAssy, SpedListTrackingTBL.[One-ThreeTon], SpedListTrackingTBL.[Four-SevenTon], SpedListTrackingTBL.[Seven-NineTon], SpedListTrackingTBL.Fab, SpedListTrackingTBL.Paint, SpedListTrackingTBL.MastWeld, SpedListTrackingTBL.Machine
FROM [!]([/!]Prmspm INNER JOIN (MRP INNER JOIN SpedListTrackingTBL ON MRP.[Part Number] = SpedListTrackingTBL.PartNumber) ON Prmspm.[Part Number] = SpedListTrackingTBL.PartNumber[!])
INNER JOIN (SELECT [Part Number] AS PartNumber, Min([Due Date]) AS EarliestDueDate FROM MRP GROUP BY [Part Number]) AS E ON MRP.[Part Number] = E.PartNumber AND MRP.[Due Date] = E.EarliestDueDate[/!]
ORDER BY MRP.[Due Date];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netrusher]

Thanks a lot PH.

That worked great!