Getting First Date and Order Number

[Samantha]

I've been stuck with this query for hours. I need to get the first date and
the Order Number of the first date. But what I'm getting is the fist date but
the minimum Order Number.

Here's my query:
SELECT qryTotalWOPO.PartNumber, Min(qryTotalWOPO.FirstWOReqDate) AS
MinOfFirstWOReqDate, First(qryTotalWOPO.FirstWOQty) AS FirstOfFirstWOQty
FROM qryTotalWOPO
GROUP BY qryTotalWOPO.PartNumber
ORDER BY qryTotalWOPO.PartNumber, Min(qryTotalWOPO.FirstWOReqDate);

Sample data from query qryTotalWOPO:
PartNumber, FirstWOReqDate, FirstWOQty
00-000001, 6/15/2006, 500
00-000001, 7/14/2006, 475

The query result should should be first line:
PartNumber, FirstWOReqDate, FirstWOQty
00-000001, 6/15/2006, 500

Instead, the result is a mixed of the two lines of data:
PartNumber, FirstWOReqDate, FirstWOQty
00-000001, 6/15/2006, 475

Can anyone help me with this? It's frustrating to mention the least!
Thanks in advance!

 

[MGFoster]

I've been stuck with this query for hours. I need to get the first date and
the Order Number of the first date. But what I'm getting is the fist date but
the minimum Order Number.

Here's my query:
SELECT qryTotalWOPO.PartNumber, Min(qryTotalWOPO.FirstWOReqDate) AS
MinOfFirstWOReqDate, First(qryTotalWOPO.FirstWOQty) AS FirstOfFirstWOQty
FROM qryTotalWOPO
GROUP BY qryTotalWOPO.PartNumber
ORDER BY qryTotalWOPO.PartNumber, Min(qryTotalWOPO.FirstWOReqDate);

Sample data from query qryTotalWOPO:
PartNumber, FirstWOReqDate, FirstWOQty
00-000001, 6/15/2006, 500
00-000001, 7/14/2006, 475

The query result should should be first line:
PartNumber, FirstWOReqDate, FirstWOQty
00-000001, 6/15/2006, 500

Instead, the result is a mixed of the two lines of data:
PartNumber, FirstWOReqDate, FirstWOQty
00-000001, 6/15/2006, 475

Can anyone help me with this? It's frustrating to mention the least!
Thanks in advance!

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

SELECT PartNumber, FirstWOReqDate, FirstWOQty
FROM qryTotalWOPO As Q
WHERE FirstWOReqDate = (SELECT MIN(FirstWOReqDate)
FROM qryTotalWOPO
WHERE PartNumber = Q.PartNumber)
ORDER BY PartNumber, FirstWOReqDate

This will run slowly if there are a lot of records (rows) in the query
'cuz the query is an unindexed recordset. For better speed, it would be
better to use all, or parts, of the qryTotalWOPO and the correlated
subquery I show above.

Search the WEB for the string "correlated subquery" to understand how
they work.
--
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/AwUBRMrDCYechKqOuFEgEQI9dACfQ60rdAKi/DZFDFHxOjRvcWsh07IAoI2A
IHUfjvXC3twm4WJ+VJLllTtJ
=jQv1
-----END PGP SIGNATURE-----