---------------------- more details below this line ---------------

I have a table the stores the following data. The table name is tblTracking.
The fields are Product, Equipment, Results and Date. The results have 3
lookup options, passed, failed or not sure. I need a query that generates
data for all products and equipment that has 3 consecutive passes.

Below is my tblTracking

Product    Equipment     Results    Date
           
Wisk                    Dryer     Failed                   03/06/02
Tide                   Dryer     Failed                          04/23/03
Wisk                    Dryer     Passed                 04/06/04
Wisk                    Dryer     Passed                 05/06/04
Tide                    Washer    Passed                 05/06/04
Wisk                     Washer    Failed                   05/08/04
Wisk                     Dryer    Passed                 05/18/04
Tide                    Washer     Passed           03/23/05
           

I need it to return only the product and equipment that have 3 consecutive
“passing” results by date which would be.

Wisk                    Dryer     Passed                 04/06/04
Wisk                    Dryer     Passed                 05/06/04
Wisk                     Dryer    Passed                 05/18/04

Anything other than what’s listed above would not meet the criteria.

JT

SELECT T.Product
, T.Equipment
, T.Results
, Min(T.Date) as FirstDate
, Max(T.Date) as LastDate
FROM tblTracking as T
WHERE T.Date In
   (SELECT TOP 3 Ta.Date
   FROM tblTracking as Ta
   WHERE Ta.Date <= T.Date
   AND Ta.Product = T.Product
   AND Ta.Equipment = T.Equipment
   ORDER BY Ta.Date Desc)
GROUP BY T.Product, T.Equipment, T.Results
HAVING Count(*) = 3
  AND T.Results = "Passed"

If you still need the middle date, then you could join the above saved query
to your table with a non-equi join.

SELECT tblTracking.*
FROM tblTracking INNER JOIN SavedQuery as Q
ON tblTracking.Product = Q.Product
AND tblTracking.Equipment = Q.Equipment
AND TblTracking.Date >= Q.FirstDate
AND tblTracking.Date <= Q.LastDate
AND tblTracking.Results = Q.Results

Or POSSIBLYall in one query like

SELECT tblTracking.*
FROM tblTracking INNER JOIN

(
  SELECT T.Product
  , T.Equipment
  , T.Results
  , Min(T.Date) as FirstDate
  , Max(T.Date) as LastDate
  FROM tblTracking as T
  WHERE T.Date In
      (SELECT TOP 3 Ta.Date
      FROM tblTracking as Ta
      WHERE Ta.Date <= T.Date
      AND Ta.Product = T.Product
      AND Ta.Equipment = T.Equipment
      ORDER BY Ta.Date Desc)
  GROUP BY T.Product, T.Equipment, T.Results
  HAVING Count(*) = 3
     AND T.Results = "Passed"
   ) as Q

ON tblTracking.Product = Q.Product
AND tblTracking.Equipment = Q.Equipment
AND TblTracking.Date >= Q.FirstDate
AND tblTracking.Date <= Q.LastDate
AND tblTracking.Results = Q.Results

If you intend to limit the query by criteria I suggest you get the criteria
into the inner most queries (if possible) in order to get the best speed
available.