asked on

QUERY TO GET THE EARLIEST DATE AND QTY and DESCRIPTION ASSOCIATED TO THE EARLIEST IN ACCESS

What I'm trying to do is get the earliest date from ON_DOCK_DATE field and on QTY_OPEN and DSESCRIPTION field get the qty associated to the earliest.

For Example:
PART DESCRIPTION QTY_OPEN MinOfON_DOCK_DATE
900-64009-505 SCREW 2 11/2/2017
900-64009-505 SCREW FAI 12 10/5/2017
900-64009-505 TEST SCREW 28 12/7/2017

I would want to only see:
900-64009-505 SCREW FAI 12 10/5/2017

Here is my Sql String:

SELECT DISTINCT tbl_PART.PART_NUMBER, tbl_PART.DESCRIPTION, tbl_PART.QTY_OPEN, Min(tbl_PART.ON_DOCK_DATE) AS MinOfON_DOCK_DATE
FROM tbl_PART
GROUP BY tbl_PART.PART_NUMBER, tbl_PART.DESCRIPTION, tbl_PART.QTY_OPEN;

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

if you wish, you can combine those 2 into one as

SELECT tbl_PART.*
FROM tbl_PART INNER JOIN (
SELECT PART_NUMBER, min(ON_DOCK_DATE) AS MinOfON_DOCK_DATE
FROM tbl_PART
GROUP BY tbl_PART.PART_NUMBER
) v_Parts_MinDate ON (v_Parts_MinDate.MinOfON_DOCK_DATE = tbl_PART.ON_DOCK_DATE) AND (tbl_PART.PART_NUMBER = v_Parts_MinDate.PART_NUMBER);

Open in new window

just copied / pasted 1st query into 2nd one...

LUIS FREUND

ASKER

AWESOME! Thank you!