Group ranges of values in access

Question

I have one table in access with like these:

Name:-----Birthdate:-----Section----etc...
John------10/10/1985-----etc...  
Mike------02/03/1976-----etc...

And many more.

How can I do a sql query that gets the age of the people in the table, counts it and shows ranges?

Something like:

Group1 ( From 18 to 25 ): 2 people  
Group2 ( From 26 to 35 ): 1 person  
...

Thanks for the answers!

How do you form the group? Are those static values? – SelvaS Apr 01 '15 at 08:56 — SelvaS, Apr 01 '15 at 08:56

score 1 · Accepted Answer · edited May 23 '17 at 12:06

1

You can calculate someone's age using datediff:

datediff('yyyy', Birthdate, now())

A switch should allow you to group on ranges:

select  AgeGroup            
,       count(*)
from    (
        select  switch(
                  datediff('yyyy', Birthdate, now()) between 18 and 25, '18 to 25',
                  datediff('yyyy', Birthdate, now()) between 26 and 35,  '26 to 35',
                  true, 'other') as AgeGroup 
        from    YourTable
        ) as SubQueriesMustBeNamed
group by 
        AgeGroup

edited May 23 '17 at 12:06

Community

1
1

answered Apr 01 '15 at 08:59

Andomar

232,371
49
380
404

Great! More about switch function: http://www.techonthenet.com/access/functions/advanced/switch.php – Maciej Los Apr 01 '15 at 09:26

koushik veldanda · Answer 2 · 2015-04-01T09:23:01.417

0

It might helps you

    select d,cast(count(d) as nvarchar(max)) + ' persons' as total  from
(
    select case 
           when CONVERT(int,ROUND(DATEDIFF(hour,Birthdate,GETDATE())/8766.0,0)) between 10 and 20   then '10-20' 
           else '>20' end as d from  YourTable
) a
    group by d

edited Apr 01 '15 at 09:23

answered Apr 01 '15 at 09:17

koushik veldanda

1,079
10
23

Not sure if ms-access supports `case` (my version doesn't) – Andomar Apr 01 '15 at 11:56

Group ranges of values in access

2 Answers2