To Combine all data from 2 tables

[Sri]

Hi All,

I have 2 tables of Sales data , one for 2004 yr and the
second for 2005 yr(Jan & Feb)..

I want to combine these tables into one showing all the
data from 2 tables..but not just combining..but showing
the info for a particular product continuously instead of
splitting..

I can't use the relationships to match the data since from
2005, a product may be dropped or introduced..using
relationships, those products will be ignored..

Eg.

Table 1
product Jan04 feb04 Mar04...
A 15 14 16
B 10 12 13
C 9 8 7

Table 2
product Jan05 feb05
A 10 20
B 12 15
D 40 50

The output what i look for is...

Output
product Jan04 feb04 Mar04...Jan05 feb05
A 15 14 16 10 20
B 10 12 13 12 15
C 9 8 7
D 40 50

The output should combine together relevant information
and show it together and also show any information that
are not related..

I have used UNION ALL operator to do this, but it just
returns as a bunch,,duplicating the products per table..

Does anyone know how to do this...Its very urgent, i have
to finish asap..

Thanks in advance for your help..

Have a nice day..

Sri

 

[Duane Hookom]

You can create a union query like:
SELECT Product, Jan04, Feb04,...Dec04, 0 as Jan05, 0 as Feb05
FROM Table1
UNION ALL
SELECT 0,0,0,0,0,0,0,0,0,0,0,0,Jan05, Feb05
FROM Table2;

You can then create a totals query based on your union query that groups by
Product and sums the months.

You would not have had this issue if you had normalized your data.

 

[Sri]

Thats excellent idea! Thanks Dueane.

Could you also tell me how to write the totals query as
mentioned by..Ideally i want the Product Name and its
total for a month..

Thanks
Sri

 

[Duane Hookom]

Use your UNION query to create a new query. Select the totals button and
figure out the rest of the settings.