MS Access "Group by" continuous values

I Have the following Access table (Primary Key = Date+Id):

Date                     Id       Value
01/07/2011 00:10:00      5        200
01/07/2011 00:30:00      5   开发者_开发技巧     210
01/07/2011 00:40:00      2        458
01/07/2011 00:50:00      2        500
01/07/2011 01:00:00      4        600
01/07/2011 01:10:00      5        359
01/07/2011 01:20:00      5        360
01/07/2011 01:30:00      5        370
01/07/2011 01:40:00      5        380

Of course, the query "SELECT Id, MAX(Value) FROM DATAS GROUP BY Id;" returns:

Id  Max
2   500
4   600
5   380

But is it possible in MS Access to have a query which groups by "sequences" of Id? Expected result:

Id  Max
5   210
2   500
4   600
5   380

There is no easy way to solve your problem. Here is a workaround.

declare @t table(date datetime, id int, value int)

insert @t values('01/07/2011 00:10:00',5,200) 
insert @t values('01/07/2011 00:30:00',5,210)
insert @t values('01/07/2011 00:40:00',2,458)
insert @t values('01/07/2011 00:50:00',2,500) 
insert @t values('01/07/2011 01:00:00',4,600) 
insert @t values('01/07/2011 01:10:00',5,359) 
insert @t values('01/07/2011 01:20:00',5,360) 
insert @t values('01/07/2011 01:30:00',5,370) 
insert @t values('01/07/2011 01:40:00',5,380) 

;with a as
(
select date, id, value, (select count(*) from @t where t.date > date) rn
from @t t
), b as
(
select a.date, a.id, a.value, coalesce(cast(b.id - a.id as bit),1) d
from a left join a b on a.rn -1 = b.rn
), c as 
(
select date,id, value, (select sum(d) from b b2 where date <=b.date) e from b
)
select id, max(value) max 
from c group by id, e

Result:

id    max
----- ---
5     210
2     500
4     600
5     380