Access query first records when multiple rows

My query returns these records:
Etch_Lot      Furnace_DateTime      temp7_ave
EAB6666      10/08/2013 03:27 PM      655.56
EAB6666      10/08/2013 03:29 PM      655.56
EAB6666      10/08/2013 03:31 PM      655.56
EAB6666      10/08/2013 03:33 PM      655.51
EAB6692      10/09/2013 01:01 PM      655.04
EAB6692      10/09/2013 01:03 PM      655.09
EAB6692      10/09/2013 01:05 PM      655.08
EAB6692      10/09/2013 12:59 PM      655.00
EAB6693      10/09/2013 01:01 PM      655.04
EAB6693      10/09/2013 01:03 PM      655.09
EAB6693      10/09/2013 01:05 PM      655.08

I only want the first record for each Etch_Lot.:
EAB6666      10/08/2013 03:27 PM      655.56  
EAB6692      10/09/2013 01:01 PM      655.04
EAB6693      10/09/2013 01:01 PM      655.04

Here is the query:
SELECT qry_Zone7_Temp.Etch_Lot, qry_Zone7_Temp.Furnace_DateTime, qry_Zone7_Temp.temp7_ave
FROM qry_Zone7_Temp
ORDER BY qry_Zone7_Temp.Etch_Lot, qry_Zone7_Temp.Furnace_DateTime;

Thank you,
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this query, copy and paste to the SQL view of a new query

SELECT Q.Etch_Lot, Q.Furnace_DateTime, Q.temp7_ave
FROM qry_Zone7_Temp As Q
Inner join
(Select min(t.Furnace_DateTime) as MinFDT, t.Etch_Lot
      from qry_Zone7_Temp t
      group by t.Etch_Lot) as Q1
On Q.Etch_Lot = Q1.Etch_Lot And Q.Furnace_DateTime = Q1.MinFDT
ORDER BY Q.Etch_Lot, Q.Furnace_DateTime;
Dale FyeConnect With a Mentor Commented:
It depends on what you want to do with the results.  The following is not updateable, but will generally run very quickly:

SELECT qry_Zone7_Temp.Etch_Lot
           , qry_Zone7_Temp.Furnace_DateTime
           , qry_Zone7_Temp.temp7_ave
FROM qry_Zone7_Temp
SELECT Etch_Lot, Min(furnace_DateTime) as MinDateTime
FROM qry_Zone7_Temp
GROUP BY Etch_Lot) as FirstRec
ON qry_Zone7_Temp.Etch_Lot = FirstRec.Etch_Lot
AND qry_Zone7_Temp.Furnace_DateTime = FirstRec.MinDateTime
ORDER BY qry_Zone7_Temp.Etch_Lot, qry_Zone7_Temp.Furnace_DateTime;
select t.etch_lot, t.furnace_datetime, t.temp7_ave
from qry_Zone7_temp t
join (select etch_lot, min(furnace_datetime) furnace_datetime
      from qry_Zone7_temp group by etch_lot) s
on (s.furnace_datetime = t.furnace_datetime AND s.etch_lot=t.etch_lot)

gbnortonAuthor Commented:
Thanks a bunch.  I can put this one to bed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.