You have sales data scattered in several tables. Structure is not same from table to table.

The first table has columns ID, product, and sales volume. The second table has columns ID, product, region, and sales volume. The third table has columns ID, product, region, sales representative and sales volume.

Product and sales volume can be found in all the three tables. We can create a report to count sales volume per product throughout the three tables.

Table 1.

Table 2.

Table 3.

Load the tables into data model.

Take the menu path data – get data – combine queries – append.

Append means that we are building a table on top of another.

Inculde all the three tables.

For the tables without values in region or sales rep fields, the Excel enters “null”.  Region and sales rep fields cannot be used as the fields are not in every table. The common table is named as T1T2T3.

Create a pivot on top of data model.

Create a simple SUM formula.

Pivot settings.

The results.

One way is to check with SQL in MS Access.

SQL is here:

SELECT product, SUM(sales)

FROM (SELECT product, sales

FROM t1

UNION ALL

SELECT product, sales

FROM t2

UNION ALL

SELECT product, sales

FROM t3

)  AS t

GROUP BY product;

If you don’t need grouping by product but you would like to see just the total number. In that case just remove the GROUP BY line, and product in SELECT statements.

If you have data bit scattered between several tables, but if the tables have common fields, it is possible to sum up the columns.