How to find X consecutive occurences? (1 Viewer)

[cyanidem]

Hi All,

I got a table with records of inspections of some sort:

Parts may come from different suppliers. I need to find every PartID from supplier with 4th consecutive positive InspScore but, to make it harder (at least for me) I need that 4th to be last inspections (chronologically). So in this:

example none of visible PartIDs should appear in query results but:

should show record with InspDate=29/08/2015.

Question is HOW? I could probably rig up some loop through records but I'd rather use SQL for that (if it's possible of course)

 

[plog]

This can be done with SQL, but will require a few subqueries. First, get the last chronological record for each Supplier/Part with a valid InspScore:

SELECT SupplierID, PartID, MAX(InspDate) AS LastInspection
FROM Query2
WHERE InspScore=True;

Then, you will need a query to count how many positive InspScore records have occured since the last negative one. This will involve a query to determine when the prior InspScore=False record occured for each record (you can get this using a DMAX. Then you would use that date in a DCount, to count how many records occured between it and the current record. In that query you would filter to just those that equal 4.

Then you INNER JOIN your 2 sub-queries together, any matches between the 2 subqueries are the ones you want.

 

[Galaxiom]

Using Domain functions in queries is generally a recipe for queries that run like molasses because every time the function is called is like running another separate query. The engine cannot optimise these queries.

Please post a table with some data.

 

[cyanidem]

Thank you guys,
I got it sorted using principles from Allen Browne's website. Very helpful reading indeed.