Need help with work project.

[JT]

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.

My sql is below. I have been working on this for about a week, I would
appreciate any help. Thank you.


SELECT tblTracking.Product, tblTracking.Equipment, tblTracking.Results,
tblTracking.Date
FROM tblTracking
WHERE (((([tblTracking].[Equipment]) And ([tblTracking].[Results]))="Passed"))
ORDER BY tblTracking.Product, tblTracking.Equipment,tblTracking.Date;

 

[JT]

--
Pay it foward.

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.

My sql is below. I have been working on this for about a week, I would
appreciate any help. Thank you.


SELECT tblTracking.Product, tblTracking.Equipment, tblTracking.Results,
tblTracking.Date
FROM tblTracking
WHERE (((([tblTracking].[Equipment]) And ([tblTracking].[Results]))="Passed"))
ORDER BY tblTracking.Product, tblTracking.Equipment,tblTracking.Date;

---------------------- 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

 

[John Spencer]

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.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..