query filtering certain timespan

[asepticmonkey]

Hello,

I am importing a lot of raw data from a csv to an access table.

The Data looks like this:

"Time"	"Value"
10.01.2022 14:32:37​	73,59999847​
10.01.2022 14:32:38​	73,59999847​
10.01.2022 14:32:39​	73,59999847​
10.01.2022 14:32:40​	73,59999847​

11.01.2022 04:44:38​	67,59999847​
11.01.2022 04:44:39​	67​
11.01.2022 04:44:40​	67​
11.01.2022 04:44:41​	67​
11.01.2022 04:44:42​	67​
11.01.2022 04:44:43​	67​
11.01.2022 04:44:44​	67​
11.01.2022 04:44:45​	67​
11.01.2022 04:44:46​	67​
11.01.2022 04:44:47​	67,59999847​
11.01.2022 04:44:48​	67,59999847​
11.01.2022 04:44:49​	67,59999847​
11.01.2022 04:44:50​	67,59999847​
11.01.2022 04:44:51​	67,90000153​
11.01.2022 04:44:52​	67,90000153​
11.01.2022 04:44:53​	67,90000153​
11.01.2022 04:44:54​	67,90000153​

I already "filtered" the data with a query to only show me the data where the value is below 75. Now I want to create a query that only shows me the data if the value was below 75 for > 30 minutes (that would be 1800 s -> 1800 consecutive values).

I want it either to show me all the values (so 1800 values or more), show me how many times the value was < 75 for > 30 minutes or ideally show me the first time and value and the last value if > 30 minutes.

Is it possible to create such a query? I hope my question is understandable.

Regards

 

[Eugene-LS]

s it possible to create such a query? I hope my question is understandable.

SELECT fTime, fValue, IIf([fValue]<75, DMin("fTime",'Your_Table_Name',"fTime>" &
        Format([fTime],    "\#mm\/dd\/yyyy hh:nn:ss\#") & " AND fValue>=75"), [fTime])
            AS Next75AndMoreTime,
    DateDiff("n",[fTime], IIf([fValue]<75,DMin("fTime",    'Your_Table_Name',"fTime>" &
        Format([fTime],"\#mm\/dd\/yyyy hh:nn:ss\#") & " AND fValue>=75"),[fTime]))
            AS MinDiff
FROM Your_Table_Name
ORDER BY fTime;

 

[Eugene-LS]

ideally show me the first time and value and the last value if > 30 minutes.

SELECT First(fTime) AS FirstOffTime, First(fValue) AS FirstOffValue, 
    Next75AndMoreTime, First(MinDiff) AS FirstOfMinDiff
FROM (
    SELECT fTime, fValue, IIf([fValue]<75, DMin("fTime",'Your_Table_Name',"fTime>" &
        Format([fTime],    "\#mm\/dd\/yyyy hh:nn:ss\#") & " AND fValue>=75"), [fTime])
            AS Next75AndMoreTime,
    DateDiff("n",[fTime], IIf([fValue]<75,DMin("fTime",    'Your_Table_Name',"fTime>" &
        Format([fTime],"\#mm\/dd\/yyyy hh:nn:ss\#") & " AND fValue>=75"),[fTime]))
            AS MinDiff
    FROM Your_Table_Name
    ORDER BY fTime
    ) AS QTest
WHERE (MinDiff>30)
GROUP BY Next75AndMoreTime;

 

[asepticmonkey]

Thank you, this seems to work perfectly!

So if MinDiff is > 30 this would mean that the value was > 30 min below 75?

It now shows me all MinDiff values, can i filter it so it only shows the ones with >30?

 

[Eugene-LS]

So if MinDiff is > 30 this would mean that the value was > 30 min below 75?

Yes.

It now shows me all MinDiff values, can i filter it so it only shows the ones with >30?

Yes.
Field "MinDiff" - means: "difference in minutes" - Name it as you like.

 

[arnelgp]

i think Query alone will not do it.
you need VBA to accomplish this.

copy your records to table1 and
run Sub fnLessThan75() in Module1.

the result will be written in table Output.

 

[maybiler1]

All I would need is to sort completed tasks by completion date and time, but that doesn't seem to be possible. I saw, that task-activities are a feature of the pro-plan and they record all changes to a task with a timestamp, but I don't see a sorting- or querying-capability there.