Search code examples
sqlms-accesssumjet

Summing two tables into one table in Jet SQL

Another SQL question if I may.

I have two tables (generated from TRANSFORM - PIVOT queries in Jet SQL)

Category  ID     Account     Jan     Feb    ...    Dec
1         1      Cash        10      20            30
1         2      Card        100     200           300
1         3      Savings     200     400           600

and

Category  ID     Account     Jan     Feb    ...    Dec
1         1      Cash        -5      -10           -20
1         2      Card                -100          -200
1         3      Savings     -100                  -400

Category, ID and Account will always be the same in two tables. There will be no accounts that occur in one table that don't occur in others. There may be NULL values in either table but there will always be a matching cell in each table.

What I would like is

Category  ID     Account     Jan     Feb    ...    Dec
1         1      Cash        5       10            10
1         2      Card        100     100           100
1         3      Savings     100     400           200

I have played around with UNION and JOIN queries but can't get there.

Thanks again, Andy

Edit - The original queries are

TRANSFORM Sum(Items.amount) AS total
SELECT Accounts.accCategory, Accounts.ID, Accounts.comment AS Account
FROM Accounts INNER JOIN Items ON Accounts.ID = Items.accFrom
WHERE (((Year([idate]))=2013) AND ((Items.category)<>3 Or (Items.category) Is Null) AND ((Accounts.accCategory)=6 OR (Accounts.accCategory)=7) AND ((Accounts.curr)=1))
GROUP BY Accounts.accCategory, Accounts.ID, Accounts.comment
PIVOT Format(idate,'mmm') IN ('Jan','Feb','Mar','Apr', 'May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');

and

TRANSFORM Sum(Items.amount) AS total
SELECT Accounts.accCategory, Accounts.ID, Accounts.comment AS Account
FROM Accounts INNER JOIN Items ON Accounts.ID = Items.accFrom
WHERE (((Year([idate]))=2013) AND ((Items.category)=3) AND ((Items.comment)='Monthly') AND ((Accounts.accCategory)=6) AND ((Accounts.curr)=1))
GROUP BY Accounts.accCategory, Accounts.ID, Accounts.comment
PIVOT Format(idate,'mmm') In ('Jan','Feb','Mar','Apr', 'May','Jun','Jul','Aug','Sep','Oct','Nov','Dec');
Solution

  • One approach using the join of the 2 tables deb and cred is as follows:

    SELECT cred.accCategory, cred.ID, cred.Account, [deb].[Jan]+[cred].[Jan] AS tot_Jan
FROM cred INNER JOIN deb ON (cred.Account = deb.Account) AND (cred.ID = deb.ID) AND (cred.accCategory = deb.accCategory);

    This screenshot shows how the query is constructed:

    And here are the results (excuse the table formatting):

    accCategory ID  Account tot_Jan
1   1   Cash    5
1   2   Card    100
1   3   Savings 100

    Be warned that the JOIN is an equi-JOIN so it means the same number of records must exist in BOTH tables else you will find that credits where a debit does not exist will be dropped! So make sure that both input tables are consistent with the same key values (the 3 GROUP BY fields in your earlier PIVOT query).

    The alternative approach is the UNION with a GROUP BY to calculate the total from it. I will paste that shortly.

    ========== EDIT ============

    You can build this up in stages. First I did SELECT * FROM cred UNION SELECT * FROM deb and looked at in Design View.

    Went back into the SQL view and made sure the UNION query has brackets around it and SELECT sub.* FROM before the brackets and AS SUB after the brackets.

    Then I modified the query to include the GROUP BY and Sum(Jan) bits. The final SQL is here and a screenshot follows.

    SELECT sub.accCategory, sub.ID, sub.Account, Sum(sub.Jan) AS SumOfJan
FROM (SELECT * FROM cred UNION SELECT * FROM deb)  AS sub
GROUP BY sub.accCategory, sub.ID, sub.Account;