proper way to merge records from multiple tables

[delikedi]

Hello, I found a way to do this:
Candy Table 1
FlavorID Amount
lemon 1
orange 2

Candy Table 2
FlavorID Amount
orange 2
apple 1

I can generate Candy Table 3 now:
FlavorID Amount
lemon 1
orange 4
apple 1

I found a way to do this. It is an unsupported SQL statement that goes:

SELECT FlavorID, Sum(Amount) AS SumOfAmount
FROM

(SELECT Table1.FlavorID, Table1.Amount
FROM Table1
UNION ALL
SELECT Table2.FlavorID, Table2.Amount
FROM Table2)
​

GROUP BY FlavorID

I am sure this subject has been covered multiple times in the past, but I was unsuccessful to find the right thread. This solution was an improvisation on the many advice about the subject, and l consider myself to have amateur's luck to have figured it out myself.
My question is, is this the proper way to obtain Table 3? Or are there cleaner/more efficient (preferably supported by access query editor) ways to do it?

 

[vbaInet]

It's really not undocumented. Subqueries are quite well known.

So to answer your question, you could have just used UNION ALL and SUM. No need for the subquery.

 

[plog]

The real question is why do you have 2 Tables of the same data?

 

[delikedi]

@plog

Well the tables are actually temporary placeholders before the real deal. Each user of my planned access multiuser application will have such a table in his/her local frontend, and their input will accumulate in the master table. I was looking for a way to produce the update operations on that master table, and I am under the impression that I found it (ot really tried the update opeartion though)