Select last row before value changes-CodePudding

I have a table in an access db with columns for testseries, sample, result, name, and measurement. For each testseries and name I want to select the last sample and result where measurement is TRUE and all previous results are TRUE.

So, if I have

FID_testseries	FID_sample	ID_result	Name	Measurement
1	1	1	John	true
1	2	2	John	true
1	3	3	John	false
1	4	4	John	true
1	5	5	John	false
1	1	6	Jane	true
1	2	7	Jane	true
1	3	8	Jane	true
1	4	9	Jane	false
1	5	10	Jane	true
1	1	11	Jeff	false
1	2	12	Jeff	true
1	3	13	Jeff	true
1	4	14	Jeff	false
1	5	15	Jeff	true

I want to select

FID_testseries	FID_sample	ID_result	Name	Measurement
1	2	2	John	true
1	3	8	Jane	true

Select rows where value changes got me pretty close to the result, but I get a result each time the measurement changes from true to false, which is not what i want.

EDIT 22-12-2021: The abovementioned SQL:

SELECT
        qRes.FID_testseries ,
        qRes.FID_sample     ,
        qRes.ID_result      ,
        qRes.name           ,
        qRes.measurement    ,
        (
                SELECT
                        TOP 1 sqRes.measurement
                FROM
                        qryResultater sqRes
                WHERE
                        qRes.name           = sqRes.name
                AND     qRes.FID_testseries = sqRes.FID_testseries
                AND     qRes.FID_sample     < sqRes.FID_sample
                ORDER BY
                        sqRes.FID_sample ) AS nextMeasurement
FROM
        qryResultater AS qRes
WHERE
        ((
                SELECT
                        TOP 1 sqRes.measurement
                FROM
                        qryResultater sqRes
                WHERE
                        qRes.name           = sqRes.name
                AND     qRes.FID_testseries = sqRes.FID_testseries
                AND     qRes.FID_sample     < sqRes.FID_sample
                ORDER BY
                        sqRes.FID_sample)) <> qRes.measurement
AND     qRes.measurement                    = -1;

Which returns

FID_testseries	FID_sample	ID_result	Name	Measurement
1	2	2	John	true
1	4	4	John	true
1	3	8	Jane	true
1	3	13	Jeff	true

CodePudding user response：

Consider:

Query1:

SELECT Data.FID_testseries, Data.FID_sample, Data.ID_result, Data.Name, Data.Measurement, 
   DCount("*","Data","Measurement = False AND FID_testseries=" & [FID_testseries] & " AND [Name]='" & [Name] & "' AND ID_result <=" & [ID_result]) AS Cnt
FROM Data
WHERE (((DCount("*","Data","Measurement = False AND FID_testseries=" & [FID_testseries] & " AND [Name]='" & [Name] & "' AND ID_result <=" & [ID_result]))=0));

SELECT Data.FID_testseries, Data.FID_sample, Data.ID_result, Data.Name, Data.Measurement, 
   (SELECT Count("*") FROM Data AS D WHERE Measurement = False AND FID_testseries=Data.[FID_testseries] AND [Name]=Data.[Name] AND ID_result <=Data.[ID_result]) AS Cnt
FROM Data
WHERE ((((SELECT Count("*") FROM Data AS D WHERE Measurement = False AND FID_testseries=Data.[FID_testseries] AND [Name]=Data.[Name] AND ID_result <=Data.[ID_result]))=0));

If Measurement is a text field instead of yes/no type, enclose False in quote marks.

Query2:

SELECT Query1.* FROM Query1 INNER JOIN 
    (SELECT Max(ID_result) AS IDR, FID_testseries, Name
     FROM Query1 GROUP BY FID_testseries, Name) AS Q 
ON Query1.ID_result = Q.IDR;