CODEWITHSUNDEEP
mysqlvbams-access
user4131408
user4131408

Reputation:

Access Query Grouping/Sum

Im trying to create a Report/Query which groups per age group. My Current Query:

SELECT QUE_REP_GENERE_AGEGR.genere, QUE_REP_GENERE_AGEGR.age, Count(*) AS [count] FROM QUE_REP_GENERE_AGEGR
GROUP BY QUE_REP_GENERE_AGEGR.genere, QUE_REP_GENERE_AGEGR.age;

Outputs following: (I know Genre is spellt wrong)

Screenshot of Output

Now would like to sum up to Age groups like 0-9;10-19;20-29;... (Yes Im aware that there are currently ony records for one age group)

I have tried to use the grouping function for reports but wasn't succesfull as it doesnt sum up the genres but displays like 

...
Drama  1
Horror 2
Horror 5
Musical 1
...

How can i group that? do i need another Query or can i group in the current one? Is VBA an option?

Upvotes: 1

Views: 44

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

I haven't done MS-Access in ages, but as I commented, you need to have a concept of "age group" somewhere, and group by that.

I'd strongly recommend you define these groups in a table, otherwise your query will have to define them. I think something like this would work (maybe not exactly that, but you get the idea):

SELECT
     genere [Genre]
    ,AgeGroup
    ,COUNT(*) [Count]
FROM (
    SELECT
         genere
        ,CASE WHEN age < 10 THEN '0-9'
              WHEN age < 20 THEN '10-19'
              WHEN age < 30 THEN '20-29'
         END AgeGroup
     FROM QUE_REP_GENERE_AGEGR
    )
GROUP BY genere, AgeGroup

Upvotes: 1

Related Questions