UNION ALL

[Guest]

Hi,

I have written an SQL query as follows :

SELECT [Product_code], [Net], [Pstg_date]
FROM [REVENUE 2006]

UNION ALL SELECT [Product_code], [Net], [Pstg_date]
FROM [REVENUE 2007];

However, I also want to include information in the query which is referenced
from other tables. (e.g. the names of the products from the product table,
the period from the dates table) Is there anyway of doing this ?

Thanks in advance
Pete

 

[Douglas J. Steele]

The answer to your specific question is:

SELECT [REVENUE 2006].[Product_code],
[Product].[Product_Description]
[REVENUE 2006].[Net],
[REVENUE 2006].[Pstg_date]
FROM [REVENUE 2006] INNER JOIN [Product]
ON [REVENUE 2006].[Product_code] = [Product].[Product_code]
UNION ALL
SELECT [REVENUE 2007].[Product_code],
[Product].[Product_Description]
[REVENUE 2007].[Net],
[REVENUE 2007].[Pstg_date]
FROM [REVENUE 2007] INNER JOIN [Product]
ON [REVENUE 2007].[Product_code] = [Product].[Product_code]

However, having tables that are essentially identical, with information
hidden in the table name, like you have is seldom a recommended approach.
You should only have 1 Revenue table, with an additional field representing
the year.

 

[George Hepworth]

Assuming that the information is consistent across both "legs" of the union
query, yes.

This is air code, of course, because I can't see your tables:

SELECT [Product_code], [Product Name], [Net], [Pstg_date]
FROM [REVENUE 2006] INNER JOIN [PRODUCT] ON [PRODUCT].[Product_code]=
[REVENUE 2006].[Product_code]

UNION ALL SELECT [Product_code], [Product Name], [Net], [Pstg_date]
FROM [REVENUE 2007] INNER JOIN [PRODUCT] ON [PRODUCT].[Product_code]=
[REVENUE 2006].[Product_code]

Please note that two tables with similar names ([REVENUE 2006] and [REVENUE
2007] ) is a strong indicator of an non-normalized structure.

If you have separate tables for each year's revenue, you are making this
much harder than need be.

One revenue table for all years would be the more appropriate approach, and
it would eliminate the need for a union query to get combined results across
years.


George

 

[Guest]

Thanks George and Douglas.
The problem I have is that Revenue 2006 and 2007 both come from an Excel
file. 2006 is c48,000 rows and 2007 is 25,000 and growing...so the joint
file would exceed the maximum rows in a spreadsheet (65,536) So far, the
only solutions I have come up with are to either :

-Reimport 2006 and 2007 into one table each time I use the database
-Link both files in and use the "Union all" in SQL.

Any other suggestions would be gratefully received !
Cheers
Pete

Assuming that the information is consistent across both "legs" of the union
query, yes.

This is air code, of course, because I can't see your tables:

SELECT [Product_code], [Product Name], [Net], [Pstg_date]
FROM [REVENUE 2006] INNER JOIN [PRODUCT] ON [PRODUCT].[Product_code]=
[REVENUE 2006].[Product_code]

UNION ALL SELECT [Product_code], [Product Name], [Net], [Pstg_date]
FROM [REVENUE 2007] INNER JOIN [PRODUCT] ON [PRODUCT].[Product_code]=
[REVENUE 2006].[Product_code]

Please note that two tables with similar names ([REVENUE 2006] and [REVENUE
2007] ) is a strong indicator of an non-normalized structure.

If you have separate tables for each year's revenue, you are making this
much harder than need be.

One revenue table for all years would be the more appropriate approach, and
it would eliminate the need for a union query to get combined results across
years.


George

Hi,

I have written an SQL query as follows :

SELECT [Product_code], [Net], [Pstg_date]
FROM [REVENUE 2006]

UNION ALL SELECT [Product_code], [Net], [Pstg_date]
FROM [REVENUE 2007];

However, I also want to include information in the query which is
referenced
from other tables. (e.g. the names of the products from the product table,
the period from the dates table) Is there anyway of doing this ?

Thanks in advance
Pete