ELIMINATE Repeating Records...

[stlrain95]

I have a SQL Query where i am pulling from 4 different tables. 3 of the table contain Order, Part, and Lot #. Lot # is unique in those 3 tables, so an Order # can repeat.

But I have the Shipping table that only contains the Order #. In the query I want to eliminate duplicate Order #s with either the first or last instance. Can I do this???


SELECT SHIPPING.OrderNumber, DAILY.DATEPRINTED, PICK.PickDate, VERIFICATION.VerDate, SHIPPING.ShipDate, DAILY.DUEDATE, SHIPPING.DueDate, DateDiff("h",[DatePrinted],[PickDate]) AS PRINTtoPICK, DateDiff("h",[PickDate],[VerDate]) AS PICKtoVERIFY, DateDiff("h",[VerDate],[ShipDate]) AS VERIFYtoSHIP, DateDiff("h",[DatePrinted],[ShipDate]) AS TotCycle, DateDiff("d",[Daily.DueDate],[ShipDate]) AS SPAN, DateDiff("d",[Shipping.DueDate],[ShipDate]) AS SPAN2
FROM ((DAILY INNER JOIN PICK ON DAILY.ORDER = PICK.OrderNumber) INNER JOIN VERIFICATION ON DAILY.ORDER = VERIFICATION.OrderNumber) INNER JOIN SHIPPING ON DAILY.ORDER = SHIPPING.OrderNumber
WHERE (((SHIPPING.ShipDate) Between FromDate() And ToDate() And (SHIPPING.ShipDate) Is Not Null))
ORDER BY SHIPPING.OrderNumber DESC;

 

[cghoga]

If the entire record is repeating and you only want to show one instance of the record you could use distinct.

Select distinct SHIPPING.OrderNumber, DAILY.DATEPRINTED, PICK.PickDate...etc.

Hope this helps.

 

[stlrain95]

I used Distinct...But it is still returning all records?

 

[stlrain95]

I have added "Distinct", but still getting the Same # of returns

SELECT DISTINCT VERIFICATION.OrderNumber, DAILY.DATEPRINTED, PICK.PickDate, VERIFICATION.VerDate, SHIPPING.ShipDate, DAILY.DUEDATE, SHIPPING.DueDate, DateDiff("h",[DatePrinted],[PickDate]) AS PRINTtoPICK, DateDiff("h",[PickDate],[VerDate]) AS PICKtoVERIFY, DateDiff("h",[VerDate],[ShipDate]) AS VERIFYtoSHIP, DateDiff("h",[DatePrinted],[ShipDate]) AS TotCycle, DateDiff("d",[Daily.DueDate],[ShipDate]) AS SPAN, DateDiff("d",[Shipping.DueDate],[ShipDate]) AS SPAN2
FROM SHIPPING INNER JOIN (PICK INNER JOIN (DAILY INNER JOIN VERIFICATION ON DAILY.ORDER = VERIFICATION.OrderNumber) ON PICK.OrderNumber = VERIFICATION.OrderNumber) ON SHIPPING.OrderNumber = VERIFICATION.OrderNumber
WHERE (((SHIPPING.ShipDate) Between FromDate() And ToDate() And (SHIPPING.ShipDate) Is Not Null))
ORDER BY VERIFICATION.OrderNumber DESC;