combine data in 2 tables with query

Question

I am trying to do this in Access.

I have 2 tables: named 2012 & 2013 Both Tables have the same fields: Year, Sales, Invoices, Part Number, Description

I need a final report (query) that adds sales and invoices for all part numbers. For example; Part Number 123 has 1 sale in 2012 and 3 sales in 2013 the result will be 4 sales. I need sum of both sales and invoices fields.

Tables have different number of records. Need to display all records.

TABLE 1 EXAMPLE

Year Sales Invoices Part Number Description
2012     0        0        1234 HAT
2012     3        3        5678 JACKET

TABLE 2 EXAMPLE

Year  Sales Invoices Part Number Description
2013     17       17        1234 HAT
2013      1        1        5678 JACKET

QUERY EXAMPLE

Sales Invoices Part Number Description
17          17        1234 HAT
 4           4        5678 JACKET

HansUp HansUp · Accepted Answer · 2013-10-07T20:10:52

You need to do 2 things:

Use a UNION query to consolidate the data from the 2 tables.
Create an aggregate (GROUP BY) query to compute the sums from the results returned by the UNION query.

First tackle the UNION query.

SELECT
    t1.Sales,
    t1.Invoices,
    t1.[Part Number],
    t1.Description
FROM [TABLE 1] AS t1
UNION ALL
SELECT
    t2.Sales,
    t2.Invoices,
    t2.[Part Number],
    t2.Description
FROM [TABLE 2] AS t2

Tweak that (untested SQL) as needed. Then use it as the subquery data source for the GROUP BY query.

SELECT
    sub.[Part Number],
    sub.Description,
    Sum(sub.Sales) AS SumOfSales,
    Sum(sub.Invoices) AS SumOfInvoices
FROM
    (
        SELECT
            t1.Sales,
            t1.Invoices,
            t1.[Part Number],
            t1.Description
        FROM [TABLE 1] AS t1
        UNION ALL
        SELECT
            t2.Sales,
            t2.Invoices,
            t2.[Part Number],
            t2.Description
        FROM [TABLE 2] AS t2
    ) AS sub
GROUP BY
    sub.[Part Number],
    sub.Description

combine data in 2 tables with query

1 Answers