To Combine all data from 2 tables

  • Thread starter Thread starter Sri
  • Start date Start date
S

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
 
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.
 
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
 
Use your UNION query to create a new query. Select the totals button and
figure out the rest of the settings.
 
Back
Top