SELECT a field from similar fields by MAX from another field

Question

I have a table with three columns: Item, Quantity, and Date.

The values in the Item column may be duplicates, but the Quantity and Dates will be unique.

For example:

Item - Quantity - Date
Hammer - 3 - 1/12/15
Hammer - 7 - 5/18/15
Hammer - 6 - 8/1/15
Wrench - 8 - 2/24/15
Wrench - 3 - 6/10/15

I am trying to write a query that will only return:

Item - Quantity - Date
Hammer - 6 - 8/1/15
Wrench - 3 - 6/10/15

This is my code:

SELECT DISTINCT stock.stc_st AS Store, stock.art_st AS UPC, articles.descr AS Description, stock.quan_st AS Quantity, articles.rp AS Cost
FROM stock LEFT JOIN articles ON stock.art_st = articles.article
WHERE stock.ym_st = 
(SELECT Max(stock.ym_st) 
 FROM stock t1
 WHERE stock.art_st=t1.art_st 
 GROUP BY t1.art_st)

GROUP BY stock.stc_st, stock.art_st, articles.descr, stock.quan_st, articles.rp, articles.act, articles.stat
HAVING (((stock.stc_st)=[Which Store?]) AND ((articles.act)="Y") AND ((articles.stat)="Y"));

However, my code is returning all items when I only want it to return the items with the max date. If anyone could take a look at this and tell me what I am doing wrong, I would really appreciate it.

========================

Now I'm trying to use this code from the answers below and it's giving me a Syntax Error on JOIN on the Inner Join at tmaxdate.art_st. I'm sure this is something stupid like a parenthesis out of place. Could anyone more familiar with Access's SQL syntax tell me what I'm doing wrong? Thanks!

    SELECT DISTINCT stock.stc_st AS Store, stock.art_st AS UPC, articles.descr AS Description, stock.quan_st AS Quantity, articles.rp AS Cost
FROM stock AS t1

INNER JOIN
    (
        SELECT tmaxdate.art_st, Max(tmaxdate.ym_st) AS MaxOfDate
        FROM stock AS tmaxdate
        GROUP BY tmaxdate.art_sc
    ) AS sub
    ON (t1.ym_st = sub.MaxOfDate) AND (tmaxdate.art_st = sub.art_st)


LEFT JOIN articles ON stock.art_st = articles.article
GROUP BY stock.stc_st, stock.art_st, articles.descr, stock.quan_st, articles.rp, articles.act, articles.stat
HAVING (((stock.stc_st)=[Which Store?]) AND ((articles.act)="Y") AND ((articles.stat)="Y"));

Answer 1

This has not been tested, but it is more along the lines of what you need. It uses a subquery to find the maximum date for each primary key of stock table (I have assumed this is art_st )

SELECT stock.stc_st AS Store
     , stock.art_st AS UPC
     , articles.descr AS Description
     , stock.quan_st AS Quantity
     , articles.rp AS Cost
FROM ( stock 
       LEFT JOIN articles 
              ON stock.art_st = articles.article
     )
     INNER JOIN (SELECT t1.art_st,  Max(stock.ym_st)  AS t1MaxDate
                   FROM stock t1
                 GROUP BY t1.art_st
                ) AS TabMax
               ON ( TabMax.art_st = stock.art_st
                  AND TabMax.t1MaxDate = stock.ym_st )
GROUP BY stock.stc_st
    , stock.art_st
    , articles.descr
    , stock.quan_st
    , articles.rp
    , articles.act
    , articles.stat
HAVING (((stock.stc_st)=[Which Store?]) AND ((articles.act)="Y") AND ((articles.stat)="Y"));

Answer 2

I couldn't figure out how that sample data is distributed among your tables. So I stored those data in a table named YourTable.

First create a GROUP BY query to show you the most recent Date for each Item:

SELECT t1.Item, Max(t1.Date) AS MaxOfDate
FROM YourTable AS t1
GROUP BY t1.Item

Then you can use that as a subquery which you join back to the main table in order to select only its rows with the matching Item/Date pairs:

SELECT t2.Item, t2.Quantity, t2.Date
FROM
    YourTable AS t2
    INNER JOIN
    (
        SELECT t1.Item, Max(t1.Date) AS MaxOfDate
        FROM YourTable AS t1
        GROUP BY t1.Item
    ) AS sub
    ON (t2.Date = sub.MaxOfDate) AND (t2.Item = sub.Item);

With your sample data in Access 2010, that query returns your requested output.

Since you don't actually have a single YourTable, you will need to adapt that approach for your actual tables, but this strategy should work there, too.