Summing multiple values into one table

Question

I have three tables in Microsoft Access:

• Table1 includes the names of various companies and the corresponding unique ID of each (along with other unique information about the company);
• Table2 includes sales information for each company from 2013;

• Table3 includes sales information for each from 2014.

  In Tables 2 & 3, a single company is often listed more than once, corresponding to sales in different states.

In Tables 2 & 3, the company name is actually its corresponding ID number, and I'm using a lookup to display the name. Thus, Table1 is the parent of Table2 and Table3.

I want to create a query that lists each company exactly once along with the sum of their sales from Tables 2 & 3. This works fine when I only try to use Table2 OR Table3, but when I incorporate both, the sales are inflated by a constant scalar (a company will show sales exactly 16X what they should be, for example). What's going on here?

Here's what the SQL shows in Access:

SELECT Table2.CompanyName,
       Sum(Table2.TotalRevenue) AS [2013 Revenue],
       Sum(Table3.TotalRevenue) AS [2014 Revenue]
FROM (Table1 INNER JOIN Table2 ON Table1.Company_ID = Table2.CompanyName)
   INNER JOIN Table3 ON Table1.Company_ID = Table3.CompanyName
GROUP BY Table2.CompanyName;

Why is it pulling these Revenue values multiple times? Thank you!

Answer 1

First you may consider merge TABLE2 and TABLE3 together and just add a YEAR field. If you keep growing one table for year will be messy in the future.

the problem is you are performing a cartesian product.

[Every company] x [Sales2013] x [Sales2014]

You need

[Every company] x [Sales2013] 
UNION
[Every company] x [Sales2014] 

Not sure what is the proper syntaxis in Access but you need a calculate totals on the subquery.

And you need LEFT JOIN in case the company didnt sell on that year. (Like a company join on 2014) and COALESCE to convert NULL to 0.

SELECT Table1.*, 
       COALESCE(T2.Revenue2013, 0)  Revenue2013, 
       COALESCE(T3.Revenue2014, 0)  Revenue2014
FROM Table1
LEFT JOIN ( SELECT CompanyName, SUM(TotalRevenue) as Revenue2013
            FROM Table2
            GROUP BY CompanyName) as T2
       ON  Table1.Company_ID = T2.CompanyName
LEFT JOIN ( SELECT CompanyName, SUM(TotalRevenue) as Revenue2014
            FROM Table3
            GROUP BY CompanyName) as T3
       ON  Table1.Company_ID = T3.CompanyName

Answer 2

You are getting multiple values because your Table2 and Table3 are not at the company level. They are at the sales level (I assume). So each table has multiple records for each customer. Joining them on company_id will cause each record in Table2 to join to each record in Table3 for each company_id.

If you want to understand this better, change your query to SELECT * FROM... with the same joins. You'll see for every 2013 record, there are many 2014 records. It won't make any sense, therefore your aggregation won't make much sense.

Instead, consider summing them before joining like:

SELECT Table2.CompanyName,
       t2.TotalRevenue AS [2013 Revenue],
       t3.TotalRevenue AS [2014 Revenue]
FROM 
    Table1 
    INNER JOIN (SELECT Company_Id, CompanyName, sum(TotalRevenue) as TotalRevenue FROM Table2 GROUP BY COmpany_ID, CompanyName) as t2 ON Table1.Company_ID = T2.CompanyName)
    INNER JOIN (SELECT Company_Id, CompanyName, sum(TotalRevenue) as TotalRevenue FROM Table3 GROUP BY COmpany_ID, CompanyName) as t3 ON Table1.Company_ID = T3.CompanyName
GROUP BY T2.CompanyName;

Answer 3

This happens because you are performing the aggregation after joining your tables. As an example

Table 1
Company_Id, CompanyName
1           One
2           Two
3           Three

Table 2 (2013)
Company_name, State, Sales
1             CA     50
1             WA     70
2             AK     30

Table 3 (2014)
Company_name, State, Sales
1             CA     60
1             WA     90
2             AK     50

Based on your query, first the sum of table 2 is performed, obtaining on memory:

Company_Id, Sales_2013
1           150
2           50

After the join with 2014 is performed, obtaining Still on memory:

Company_Id, Sales_2013, State_2014, Sales_2014
1           150        CA           50
1           150        WA           90
2           50         AK           30

As you can see at this point you have duplicates. Then, the second sum is performed, finally obtaining

Company_Id, Sales_2013, Sales_2014
1           300         140
2           50          30

For this to do what you expect you need to have a query that does:

SELECT TTable2.CompanyName,
   [2013 Revenue],
   [2014 Revenue]
FROM Table1 INNER JOIN (
    SELECT Table2.CompanyName,
        Sum(Table2.TotalRevenue) AS [2013 Revenue]
    FROM Table2
    GROUP BY Table2.CompanyName
)TTable2
ON Table1.Company_ID = TTable2.CompanyName
INNER JOIN (
    SELECT Table3.CompanyName,
        Sum(Table3.TotalRevenue) AS [2014 Revenue]
    FROM Table3
    GROUP BY Table3.CompanyName
)TTable3
ON Table1.Company_ID = TTable3.CompanyName;