Trying to find MIN or FIRST date

[Indigo]

I am running Access 2003 and I am trying to create a query that will provide me with the first date entered for a particular steel coil serial number. I have a table with over 12,000 records. Everytime a steel coil
is received from the supplier, its serial number and weight along with the date is recorded in this table. Each time the coil is pulled for use and returned to inventory, the new weight is recorded in the table along with the date. I need to create a query that will return only the very first time the coil is entered into the table - by date. Can anyone help?

I have tried:

SELECT DISTINCT tblReceivedCoilHistory.CoilSerialNumber, First(tblReceivedCoilHistory.DateReceived) AS FirstOfDateReceived, tblReceivedCoilHistory.CoilWeight
FROM tblReceivedCoilHistory
GROUP BY tblReceivedCoilHistory.CoilSerialNumber, tblReceivedCoilHistory.CoilWeight
ORDER BY First(tblReceivedCoilHistory.DateReceived) DESC;

Thanks.

 

[LPurvis]

Hi

Something like:

SELECT T.CoilSerialNumber, T.DateReceived, T.CoilWeight
FROM 
 tblReceivedCoilHistory T
INNER JOIN 
 (
    SELECT T2.CoilSerialNumber, Min(T2.DateReceived) AS MinDate
    FROM tblReceivedCoilHistory T2
    GROUP BY T2.CoilSerialNumber
) X
ON T.CoilSerialNumber = X.CoilSerialNumber AND T.DateReceived = X.MinDate
ORDER BY T.DateReceived DESC

Cheers.

 

[Indigo]

Works perfectly. Thank you so much!

 

[LPurvis]

No worries.