CODEWITHSUNDEEP
ms-accessms-access-2010
Alok
Alok

Reputation: 509

MS Access SQL Query with grouping across rows and columns

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

Upvotes: 0

Views: 207

Answers (2)

Ravi Makwana
Ravi Makwana

Reputation: 2914

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

Upvotes: 1

June7
June7

Reputation: 21379

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.

Upvotes: 1

Related Questions