Find first instance 1

[molly]

I would like to find the Date when a product code was first purchased by anyone.

For simplicity here, I have a ProductID field and a SaleDate field. As an example for you, i put an "x" on the 4 records which i hope the query will discover as the first sales ever for a product.

ProductID SaleDate
204662-704032-620967 12/31/2006 x
204662-704032-620967 1/31/2007

204662-708099-452010 4/30/2007 x
204662-708099-452010 7/31/2007

204662-708099-607101 6/30/2004 x
204662-708099-607101 7/31/2006

204662-708099-607216 9/30/2005 x
204662-708099-607216 7/31/2006
204662-708099-607216 4/30/2007

Here is the Access 2003 in qry SQL view:

SELECT tblProduct.ProductID, tblProduct.SaleDate
FROM tblProduct
ORDER BY tblProduct.ProductID, tblProduct.SaleDate;

In this example, i would want just 4 records to appear from the qry.

thanks
Molly

 

[PWise]

Select ProductID ,min(SaleDate) as FirstSalesDate
from tablename
group by ProductID

 

[molly]

Thanks Pwise. it worked. For the readers, here is what i ended up with in sql.

Select ProductID,min(SaleDate) as FirstSalesDate
from tblProduct
group by ProductID

molly