Return Top 3

[stapuff]

I looking to return the last 3 shipment based on SHIP_DATE where Zone and Part are the same.


Looking for this result from the data set below it.
002600 A 11 1613011 0.37 8/3/2016
002600 A 11 1599263 0.36 7/11/2016
002600 A 11 1572675 0.38 5/20/2016
002600 B 11 1524571 0.39 2/18/2016
002600 B 11 1412722 0.52 8/10/2015
002600 B 11 1402827 0.4 7/17/2015
002600 C 11 1627915 0.14 8/25/2016
002600 C 11 1613011 0.15 8/3/2016
002600 D 11 1599263 0.15 7/11/2016
002600 D 11 1585137 0.15 6/14/2016
002600 D 11 1572675 0.16 5/20/2016



Zone Part GC Order# Pct SHIP_DATE
002600 A 11 1613011 0.37 8/3/2016
002600 A 11 1599263 0.36 7/11/2016
002600 A 11 1572675 0.38 5/20/2016
002600 A 12 1562916 0.39 5/3/2016
002600 A 11 1541251 0.37 3/22/2016
002600 A 11 1463800 0.43 11/10/2015
002600 A 11 1434692 0.41 9/22/2015
002600 A 11 1422736 0.41 9/1/2015
002600 A 11 1402827 0.43 7/17/2015
002600 A 11 1388824 0.43 6/17/2015
002600 A 11 1315539 0.51 1/20/2015
002600 B 11 1524571 0.39 2/18/2016
002600 B 11 1412722 0.52 8/10/2015
002600 B 11 1402827 0.4 7/17/2015
002600 B 11 1388824 0.4 6/17/2015
002600 B 11 1350305 0.49 3/31/2015
002600 C 11 1627915 0.14 8/25/2016
002600 C 11 1613011 0.15 8/3/2016
002600 D 11 1599263 0.15 7/11/2016
002600 D 11 1585137 0.15 6/14/2016
002600 D 11 1572675 0.16 5/20/2016
002600 D 12 1562916 0.16 5/3/2016
002600 D 11 1545336 0.16 3/29/2016
002600 D 11 1541251 0.16 3/22/2016
002600 D 11 1524571 0.16 2/18/2016
002600 D 11 1494411 0.16 12/29/2015
002600 D 11 1463800 0.18 11/10/2015
002600 D 11 1450204 0.17 10/19/2015


My latest attempt out of 57 others:

SELECT Zone, Part, GC, [Order#], Pct, SHIP_DATE

FROM Orders

WHERE SHIP_DATE IN

(SELECT TOP 3 SHIP_DATE
FROM Orders AS Dupe
WHERE Dupe.Zone = Orders.Zone
AND Dupe.Part = Orders.Part
ORDER BY Dupe.Zone,Dupe.Part,Dupe.SHIP_DATE DESC
)
ORDER BY Zone, Part, SHIP_DATE DESC;

Any suggestions would be greatly appreciated,

stapuff

 

[Sinon]

One way you can do it is to identify the latest 3 shipments for each zone and part and then filter the list out and copy the values to a different sheet.

Assuming your data is in A1:F29, add this in G2:
=IF(F2<LARGE(IF($B$2:$B$29=B2,IF($A$2:$A$29=A2,$F$2:$F$29)),IF(COUNTIFS($B$2:$B$29,B2,$A$2:$A$29,A2)<3,COUNTIFS($B$2:$B$29,B2,$A$2:$A$29,A2),3)),0,1)
and confirm with CTRL+SHIFT+ENTER not just enter

The results will be either 1, meaning it's one the latest 3 shipments, or 0, meaning it's older. you can change this if you want in the last bit of the formula to even show text:

=IF(F2<LARGE(IF($B$2:$B$29=B2,IF($A$2:$A$29=A2,$F$2:$F$29)),IF(COUNTIFS($B$2:$B$29,B2,$A$2:$A$29,A2)<3,COUNTIFS($B$2:$B$29,B2,$A$2:$A$29,A2),3)),"Too old","Recent shipment")

Apply to the rest of the column and then filter out the 1s and copy to a different sheet for a final list.

 

[alansidman]

Look at this Access Video on how to do this: Macromedia Flash (SWF) Movie Created by Camtasia Studio 2

 

[alansidman]

Been wrangling with this myself. Seems that the date field causes a little bit of an issue. Anyway, with the help of someone else, I was able to retrieve a workable solution.

SELECT *
FROM Sheet1 AS s
WHERE s.Ship_Date >= ANY
(SELECT TOP 3 Ship_Date FROM Sheet1 WHERE Part = s.Part ORDER BY Ship_Date DESC);