SQL question

[DBARNETT]

Hi

I have a table with an ITM_NO and a PO_Date and a few other fields.
There are duplicate Table entries with the same ITM_NO and different
PO_Dates.

What I need to do is get 1 table entry for each Unique ITM_NO but the
entry that has the most recent PO_Date.

ITM_NO PO_DATE OTHER
123 1/27/2006 a
123 6/20/2006 b
123 5/14/2005 c

456 3/15/2007 d
456 4/15/2007 e
456 5/15/2007 f

Should yield:
123 6/20/2006 b
456 5/15/2007 f

Thanks for your help
Dennis

 

[John Vinson]

What I need to do is get 1 table entry for each Unique ITM_NO but the
entry that has the most recent PO_Date.

ITM_NO PO_DATE OTHER

A Subquery will do this:

SELECT ITM_NO, PO_DATE, OTHER
FROM mytable
WHERE PO_DATE = (SELECT Min(PO_DATE FROM mytable AS A
WHERE A.ITM_NO = mytable.ITM_NO);


John W. Vinson[MVP]

 

[Joan Wild]

SELECT ITM_NO, Max(PO_DATE) AS MaxOfPO_DATE
FROM SomeTable
GROUP BY ITM_NO;

However since you also want to get a related piece of information i.e.
other, you'll need to use one of the methods outlined at
http://www.mvps.org/access/queries/qry0020.htm