Question using Min or First for a date field

[Sean]

In my query I am asking it to pick the min or first date associated with an
Sales Doc # and a material. There is also a field called LDB. On the line
with the earlier date this field is not null, but the line with the later
date it is. Regardless of what is in this field (and I do need this field
whether it's null or not, as it can be either) I only want the line to appear
with the earliest date (min date or first). How can I do this?
Example below please help and thanks.

Sales Doc# Material LDB FirstOfImport_Date
9787602 Y425537 8/10/2009
9787602 Y425537 05 8/6/2009

 

[MGFoster]

In my query I am asking it to pick the min or first date associated with an
Sales Doc # and a material. There is also a field called LDB. On the line
with the earlier date this field is not null, but the line with the later
date it is. Regardless of what is in this field (and I do need this field
whether it's null or not, as it can be either) I only want the line to appear
with the earliest date (min date or first). How can I do this?
Example below please help and thanks.

Sales Doc# Material LDB FirstOfImport_Date
9787602 Y425537 8/10/2009
9787602 Y425537 05 8/6/2009

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Possibly like this:

SELECT [Sales Doc#], Material, LDB, [Import Date]
FROM table_name As T1
WHERE [Import Date] = (SELECT MIN([Import Date])
FROM table_name
WHERE [Sales Doc#] = T1.[Sales Doc#])

Insert your table name for "table_name."

If the key for the record is [Sales Doc#] and some other column in the
main query's SELECT clause, include that column in the sub-query's WHERE
clause criteria. E.g.: if [Sales Doc#] and [Material] were required to
uniquely identify the record the sub-query's WHERE clause would look
like this:

WHERE [Sales Doc#] = T1.[Sales Doc#]
AND Material = T1.Material

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqk9mIechKqOuFEgEQJkkACfWQwX+u/zxozJ5Q98MfXFfJHTm+gAoNOy
QB5umiGDE2Wsl6mJ0F9uU7ex
=Du+G
-----END PGP SIGNATURE-----