Adding data from one query to another query

[Guest]

LS,

I have the following problem. I have to tables containing data.

Table 1 contains sales data up to month 9, while table 2 contains forecast
data from 10 -12. As I do not want to append the data from table 2 to table 1
(as data forecast data changes every month), I tried to do this by a query
however I can not make it work.

For example I have the following:
Table 1
Product Month Value
x 3 10
y 1 11
z 2 12
....

Table2
Product Month Value
x 9 10
y 10 11
z 11 12
The result should be:
Product Month Value Acc.Sum
x 3 3
x 9 13
y 1 11
y 10 22
z 2 12
z 11 24

Is this possible and how?

TIA

 

[Tom Ellison]

Dear Lee:

I expect that a UNION query will do this for you.

SELECT * From [Table 1]
UNION ALL
SELECT * FROM [Table 2]

The tables must have the exact same number of columns, and in the same
order.

Tom Ellison

 

[Marshall Barton]

I have the following problem. I have to tables containing data.

Table 1 contains sales data up to month 9, while table 2 contains forecast
data from 10 -12. As I do not want to append the data from table 2 to table 1
(as data forecast data changes every month), I tried to do this by a query
however I can not make it work.

For example I have the following:
Table 1
Product Month Value
x 3 10
y 1 11
z 2 12
...

Table2
Product Month Value
x 9 10
y 10 11
z 11 12
The result should be:
Product Month Value Acc.Sum
x 3 3
x 9 13
y 1 11
y 10 22
z 2 12
z 11 24

You want a UNION query to get both sets of records. You can
not create a UNION query using the query design grid so you
have to switch to SQL view.

Since the first set of records is just a copy of the data in
table1, that part is simple. The second set contains the
sum of the two sets of value, so the second part of the
query need to join both tabl1 and table2:

SELECT Product,
Month,
Value As AccSum
FROM table1
UNION ALL
SELECT table2.Product,
table2.Month
table2.Value + table1.Value
FROM table1 INNER JOIN table2
ON table1.Product = table2.Product