How to combine records from different tables?

Question

There are two worksheets in same workbook that have the same structure-same field names.

for example :

Table 1

- Officer  name   mkt 
 - s15     peter  15
 - s17     mary   18
 - S32     tom    42
 - S32     tom    89

Table 2

- Officer  name   mkt 
 - s56     jason  55
 - s31     alex   34
 - S54     gee    45

Now I'm using ADO to run the sql in excel-vba.

select officer ,name ,sum(mkt) from [$table1]

Now I want to combine these 2 tables records and select later.

That means:

Table 3

- Officer  name   mkt 
 - s15     peter  15
 - s17     mary   18
 - S32     tom    42
 - S32     tom    89
 - s56     jason  55
 - s31     alex   34
 - S54     gee    45

Then later make the selection(SQL) .

  select officer ,name ,sum(mkt) from [$table3]

Is it able to perform it in SQL or VBA (i prefer to perform it in SQL Statement )?

*I prefer to use sth SQL technique to perform it . Something like join table ? But join table only join the columns in different tables.Now I want to join rows *

Answer 1

    select officer ,name ,sum(mkt) from table1
    union all
    select officer ,name ,sum(mkt) from table2

Answer 2

You can use a common table expression to union the tables into one and then perform the aggregate sum. I'm using SET NOCOUNT ON; because I had issues before in excel if I omitted this. A full outer join between the two tables would also work.

SET NOCOUNT ON;

WITH CTE AS
(
    SELECT *
    FROM [$table1]
    UNION ALL
    SELECT *
    FROM [$table2]
)

SELECT office, name, sum(mkt)
FROM CTE
GROUP BY office, name

You can also try without the CTE:

SELECT office, name, sum(mkt)
FROM(
    SELECT *
    FROM [$table1]
    UNION ALL
    SELECT *
    FROM [$table2]
)
GROUP BY office, name