MS Access SQL Query with grouping across rows and columns

Question

I have a table in MS Access with following layout. I am using MS Access 2019

Category1  Qty1   Category2 Qyt2
================================
A           10       B       2
B           3        C       4
A           2        A       4
C           3        B       3

I want the output as

Category     Qty
================
A             16
B              8
C              7

Please suggest how to achieve this using query in MS Access.

Thanks

Ravi Makwana · Accepted Answer · 2020-02-21 07:29:29Z

1

Use UNIONALL and GROUP BY in inner sub query

HERE CHECK SQL Fiddle

  SELECT Category, SUM(Qty) as Qty FROM (
        SELECT Category1 AS Category, Qty1 AS Qty FROM TAB1
        UNION ALL
        SELECT Category2 as Category, Qty2 AS Qty FROM TAB2
    ) as TAB
    GROUP BY TAB.Category

edited Feb 21, 2020 at 7:29

answered Feb 21, 2020 at 7:14

Ravi Makwana

2,9201 gold badge32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

June7 · Accepted Answer · 2020-02-21 06:57:37Z

1

Rearrange data with UNION query into normalized structure should be in first place.

SELECT Category1 AS Cat, Qty1 AS Qty, 1 AS Src FROM tablename
UNION SELECT Category2, Qty2, 2 FROM tablename;

Use that query as source for an aggregate query.

answered Feb 21, 2020 at 6:57

June7

21.7k8 gold badges28 silver badges36 bronze badges

Collectives™ on Stack Overflow

MS Access SQL Query with grouping across rows and columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related