need your help to modifying Access query

Roman F
Roman F used Ask the Experts™
on
year      month      submission      Count            
2019      1             Category1      2            --------------------->A
2019      1             Category2      5            --------------------->B
2019      1             Category3      1            --------------------->B
2019      1             Category4      3            --------------------->A
this a simple query, need your help to modify and grouped by Submission

Category1 and Category4 should be group A and the total should be 5 (2+3)
Category2 and Category3 should be group B and the total should be 6 (5+1)

and finally the results should be:
Year   Month  Category      total
2019  1            A                   5
2019  1            B                   6
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
The easyest way for this is to add a group table, and define a relationship between the 2 tables.
Then the query will be a simple join.

Author

Commented:
thank you, I need a single query possible using a switch?
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This should do:

Select [year], [month], IIf(submission=1 Or submission=4), "A", "B") As Category, Sum([Count]) As Total
From YourTable
Group By [year], [month], IIf(submission=1 Or submission=4), "A", "B")

Open in new window

Author

Commented:
exactly!
thank you so much
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial