CODEWITHSUNDEEP
sqlms-accessgroupingrownum
MCTampa
MCTampa

Reputation: 1

Creating Groups of Consecutive Values in Access Query

To be clear, I'm not a developer, I'm just a business analyst trying to achieve something in Access which has stumped me.

I have a table of values as such:

Area    Week
232 1
232 2
232 3
232 4
232 5
232 6
232 7
232 8
232 9
232 10
232 11
232 12
232 35
232 36
232 37
232 38
232 39
232 41
232 42
232 43
232 44
232 45
232 46
232 47
232 48
232 49
232 50
232 51
232 52
330 1
330 2
330 3
330 4
330 33
330 34
330 35
330 36
330 37
330 38
330 39
330 40
330 41
330 42
330 43
330 44
330 45
330 47
330 48
330 49
330 50

I would like to create a query using SQL in Access to create grouping as follows:

Area Code   Week Start  Week End
232 1   12
232 35  39
232 41  52
330 1   4
330 33  45
330 47  50

However everything I have read leads me to use the ROWNUM() function which is not native to Access. I'm OK with general queries in Access, but am not very familiar with SQL. How can I go about achieving my task?

Thanks Mike

Upvotes: 0

Views: 154

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

Use another database! MS Access doesn't have good functionality (in general).

You can do what you want, but it is expensive:

select area, min(week), max(week)
from (select t.*,
             (select count(*)
              from t as t2
              where t2.area = t.area and t2.week <= t.week
             ) as seqnum
      from t
     ) as t
group by area, (week - seqnum);

The correlated subquery is essentially doing row_number().

Upvotes: 1

Related Questions