earliest date

[Steve]

I have an Access 2000 form that provides results from 2 tables created by
our order system each night.

Table1 contains ITEMs and Table2 contains ITEMs, ORDER_NO, and DATE_DUE

I have a join statement that ties the ITEM in Table1 to the ITEM in Table2
and gives me a report that shows - ITEM, ORDER_NO, and DATE_DUE.

The item field in table1 is unique but we may have several orders for the
item in table 2. I would like it to return the order_no and date_due fields
for the matching items with the earliest date due.

Can anyone point me in the right direct? Is DMIN a proper path?

I tried "Where DMIN([date_due], 'table2', 'table2.item = table1.item')" but
did not work...

 

[Guest]

SELECT Table2.Item, Min(Table2.Date_Due) AS (Oldest Order)
FROM Table2
GROUP BY Table2.Item;