How to determine the earliest date in three records? 2

[e7akerson]

I have been racking my brain trying to figure out how to get the record with the earliest "StartTime" from three identical records. I have tried First and Last, Min and Max from the aggregate functions with no luck. Would prefer minimum programming or code for the answer.

Example


Board Ser.No. Fail Date Start Time

H445678L 11-12-99 12-22-99 1:00AM
H445678L 11-12-99 12-23-99 1:00PM
H445678L 11-12-99 12-24-99 1:00PM

 

[JerryDennison]

DMin("[Start Time]", "TableOrQueryName", "[Board Ser.No.] = 'H445678L'&quot

 

[e7akerson]

Thanks, Jerry but I oversimplified the problem. Of course I have lots of records with multiple duplicates so your solution would only work for this BD serial number. Thanks anyway.

 

[KMITCH]

Data:
SerialNo FailDate StartTime
X 01/01/2001 01/01/2001
X 01/01/2001 01/02/2001
X 01/01/2001 01/03/2001
Y 01/02/2001 02/05/2001
Y 01/02/2001 02/06/2001
Y 01/02/2001 02/07/2001

SQL:
SELECT Table1.SerialNo, Table1.FailDate,
Min(Table1.StartTime) AS MinOfStartTime
FROM Table1
GROUP BY Table1.SerialNo, Table1.FailDate;

RESULT:
SerialNo FailDate MinOfStartTime
X 01/01/2001 01/01/2001
Y 01/02/2001 02/05/2001

I'm probably oversimplifying this as well, but the SQL
above returns the RESULT above for the DATA above. A
subquery would also return the same result:

SELECT Table1.SerialNo, Table1.FailDate, Table1.StartTime
FROM Table1
WHERE Table1.StartTime =
(SELECT Min(X.StartTime) FROM Table1 as X
Where X.SerialNo = Table1.SerialNo
AND X.FailDate = Table1.FailDate );

Note: Not sure if FailDate is part of your key.

Hope this helps.

Keith

 

[rafe]

Jerry's on target...

in your case you could have a form control where you enter the Serial# in question then it's a simple change to...

DMin("[Start Time]", "TableOrQueryName", "[Board Ser.No.] = '" & Me.MySerialNoTextBox & "'&quot

Of course you could do other string build-ups...

 

[JerryDennison]

I only used that specific serial number to give you an idea of syntax and usage. If you want to say list every board number based on the fail date and the earliest start time. Create a totals query, put these three fields on the QBE grid, set the Total property of the board number and fail date to Group By and the total property of the Start Time to Min. This will give you a single record returned for each board for each failure date with only the earliest Start Time for that board/failure date combination.