Count Max Consecutive equal values in a field

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

In a table "status" there is a field "onoff" that has
values of "on" or "off". example

on
on
off
on
off
off
off
on

I need to report the max and current counts of consecutive
on's and off's. In the above example the results would be
2 on's for max on's, 3 off's for max off's and 1 on for
current. I have tried various expressions and code with no
results and would appreciate any help. Thanks.
 
Dear Mark:

"Consecutive" implies you have put the rows of data in some order.
This requires there to be one or more other columns that define that
ordering to be used. This set of ordering columns will have to be
unique for the data as well in order to make sense of the term
"consecutive."

You will need to reveal these columns before I can proceed. I then
propose to create a query that will first count the number of
consecutive rows following each unique consecutive order, based on the
unique key to that row. You can then perform a simple MAX() in a
total query based on that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks for the reply Tom.
The order of the data in the table is by time. There are 3
fields in the table status, key(auto number), time, onoff.
Data is in order of oldest to newest, so as far as order I
need to base the "consecutive" on's or off's it would be
by the key from smallest to largest. Example
Key Time OnOff
1 10:09 on
2 10:10 on
3 10:11 off
4 10:12 on
5 10:13 off
6 10:14 off
7 10:15 off
8 10:16 on
Thanks for the help, Mark
 
Dear Mark:

Depending on the resolution to which you record the time, it may be
possible to have duplicates. That would really screw up what we're
going to try to do.

Next, I'm not sure how you are recording the time. What happens at
12:59? Will you be able to sort by this Time field and actually put
thing into proper order.

Assuming there are no problems as I have feared:

SELECT [Time],
(SELECT COUNT(*) FROM YourTable
WHERE [Time] BETWEEN T.[Time]
AND (SELECT MIN([Time]) FROM YourTable T1
WHERE T2.[Time] > T.Time AND T2.OnOff <> T.OnOff)
AS ConsecutiveCt
FROM YourTable T
ORDER BY [Time]

Because this requires a reference out of two levels of subquery, I'm
not so sure Jet can run it. With MSDE you'd be OK on that.

Are your times always recorded at one minute intervals? Is what
you're trying to accomplish just a measure of how long it remains ON
and how long it remains OFF? If so, some simplification is in order!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi,


If you have large tables, it may be preferable to use temporary tables,
temp1 and temp2, each one having an autonumber field, f1, and your key
field, pk.


Step1. Fill your temp tables.

INSERT INTO temp1(pk) SELECT pk FROM myTable ORDER BY [time] ASC;

INSERT INTO temp2(pk) SELECT pk FROM myTable ORDER BY onoff, [time]
ASC;



Step2. Create the result.

SELECT x.onoff, MAX(x.countOf)
FROM ( SELECT onoff, COUNT(*) as CountOf
FROM (myTable INNER JOIN temp1 ON temp1.pk=myTable.pk)
INNER JOIN temp2 ON temp2.pk=myTable.pk
GROUP BY onoff, temp1.f1-temp2.f1
) as x
GROUP BY x.onoff


Step3 Cleaning


DROP TABLE temp1

DROP TABLE temp2


So, what is going on? better working with an example for illustration.


With your date, temp1 is
f1 key
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8


but if your key was not sequential, uninterruped, f1 would be.

temp2 is ordering the on (-1) first:

f1, key key time onoff
1 1 -> 1 10:09 on
2 2 -> 2 10:10 on
3 4 -> 4 10:12 on
4 8 -> 8 10:16 on
5 3 -> 3 10:11 off
6 5 -> 5 10:13 off
7 6 -> 6 10:14 off
8 7 -> 7 10:15 off


ie, the on first, the off after, and in case of equality, accordingly to the
key (or the time, in fact)

Now, the step 2 just subtrat the f1 values for the same key:


key temp1.f1 temp2.f1 difference
1 1 1 0
2 2 2 0
3 3 5 -2
4 4 3 1
5 5 6 -1
6 6 7 -1
7 7 8 -1
8 8 4 4



so, these difference used as group, now make the COUNT(*)

difference COUNT(*) associated_on/off
0 2 on
-2 1 off
1 1 on
-1 3 off
4 1 on


(we keept track of the associated on/off by grouping on those values, in
addition to the differences).

and finally, finding the max (count ) for each on/off

on 2
off 3






Hoping it may help,
Vanderghast, Access MVP
 
Thanks for the help Tom and Michel. The tables are not big
but I tried your solution Michel and it worked well except
for a couple of wrong results with certain combinations of
values. Example
on
off
off
off
off
on
produce 4 off 2 on, instead of 4 off 1 on. Tom I have had
trouble running yours as is. I either get errors it is too
complex or it asks for input for T2. Both your ideas have
made me try some other ideas to make this simple. I have
this simple idea which counts the on/offs in two fields in
a query and which I can report the MAX of each field. The
problem is that I can't get the count to reset(start again
from 0) each time a new sequence of on or off is
encountered. Can that be done, If so then I have what I
need. Thanks for taking the time to share your ideas with
me. Here is the code and current results.

SELECT T.ID, T.OnOff, (SELECT COUNT(*) FROM Status T1
WHERE T1.OnOff = T.OnOff AND T1.OnOff ="on"
AND T1.ID <= T.ID) AS CON,
(SELECT COUNT(*) FROM Status T1
WHERE T1.OnOff = T.OnOff AND T1.OnOff ="off"
AND T1.ID <= T.ID) AS COFF
FROM Status AS T
ORDER BY T.ID;

This is what it produces.
ID onoff CON COFF
1 off 0 1
2 off 0 2
3 on 1 0
4 on 2 0
5 on 3 0
6 off 0 3<--- would like this to start at 1 again

Here is what I would like it to do.
ID onoff CON COFF
1 off 0 1
2 off 0 2
3 on 1 0
4 on 2 0
5 on 3 0
6 off 0 1<----
I can add another field if I need another reference.

Thanks again.
-----Original Message-----
Hi,


If you have large tables, it may be preferable to use temporary tables,
temp1 and temp2, each one having an autonumber field, f1, and your key
field, pk.


Step1. Fill your temp tables.

INSERT INTO temp1(pk) SELECT pk FROM myTable ORDER BY [time] ASC;

INSERT INTO temp2(pk) SELECT pk FROM myTable ORDER BY onoff, [time]
ASC;



Step2. Create the result.

SELECT x.onoff, MAX(x.countOf)
FROM ( SELECT onoff, COUNT(*) as CountOf
FROM (myTable INNER JOIN temp1 ON temp1.pk=myTable.pk)
INNER JOIN temp2 ON temp2.pk=myTable.pk
GROUP BY onoff, temp1.f1-temp2.f1
) as x
GROUP BY x.onoff


Step3 Cleaning


DROP TABLE temp1

DROP TABLE temp2


So, what is going on? better working with an example for illustration.


With your date, temp1 is
f1 key
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8


but if your key was not sequential, uninterruped, f1 would be.

temp2 is ordering the on (-1) first:

f1, key key time onoff
1 1 -> 1 10:09 on
2 2 -> 2 10:10 on
3 4 -> 4 10:12 on
4 8 -> 8 10:16 on
5 3 -> 3 10:11 off
6 5 -> 5 10:13 off
7 6 -> 6 10:14 off
8 7 -> 7 10:15 off


ie, the on first, the off after, and in case of equality, accordingly to the
key (or the time, in fact)

Now, the step 2 just subtrat the f1 values for the same key:


key temp1.f1 temp2.f1 difference
1 1 1 0
2 2 2 0
3 3 5 -2
4 4 3 1
5 5 6 -1
6 6 7 -1
7 7 8 -1
8 8 4 4



so, these difference used as group, now make the COUNT(*)

difference COUNT(*) associated_on/off
0 2 on
-2 1 off
1 1 on
-1 3 off
4 1 on


(we keept track of the associated on/off by grouping on those values, in
addition to the differences).

and finally, finding the max (count ) for each on/off

on 2
off 3






Hoping it may help,
Vanderghast, Access MVP



Thanks for the reply Tom.
The order of the data in the table is by time. There are 3
fields in the table status, key(auto number), time, onoff.
Data is in order of oldest to newest, so as far as order I
need to base the "consecutive" on's or off's it would be
by the key from smallest to largest. Example
Key Time OnOff
1 10:09 on
2 10:10 on
3 10:11 off
4 10:12 on
5 10:13 off
6 10:14 off
7 10:15 off
8 10:16 on
Thanks for the help, Mark
that
define that the
term order,
based on the MAX
() in a with
no
Click to expand...


.
Click to expand...
 
Hi,


With the data

---------------------------
Table10
key time onoff
1 10:01:00 On
2 10:02:00 Off
3 10:03:00 Off
4 10:04:00 On
5 10:05:00 On


-------------------------------

and query28 (my first query)

SELECT a.key, a.onoff, COUNT(*) As f1
FROM Table10 AS a INNER JOIN Table10 AS b
ON a.time>=b.time
GROUP BY a.key

I got
----------------
Query28
key f1
1 1
2 2
3 3
4 4
5 5

------------------

while with query27 (my second query)

SELECT a.key, (SELECT COUNT(*)
FROM table10 As b
WHERE iif(a.onoff, b.onoff and a.time>=b.time,
b.onoff or a.time>=b.time)) AS f1
FROM table10 AS a;

I got


---------------
Query27
key f1
1 1
2 4
3 5
4 2
5 3

-----------------


I didn't like that query, I rewrote it to make it looks more standard, more
recognizable ( the on clause I now used describes the "order by" concept in
a more natural way ):

SELECT a.key, a.onoff, COUNT(*) As f1
FROM Table10 AS a INNER JOIN Table10 AS b
ON (a.onoff > b.onoff) OR ((a.onoff=b.onoff ) and ( a.time>=b.time))
GROUP BY a.key, a.onoff


and the intermediate result is the same.

The final result return max sequences of 2-on and 2-off.


Is there some difference between the intermediate results I supplied here
and yours?



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top