Need helps on my union query

[henry]

hi all,

I have two tables define with the sample data as following:
Store_Sales_Report:
Store, Sales, Month
A, 10, 1/2007
B, 20, 2/2007
C, 30, 4/2007
D, 40, 12/2007
A, 50, 12/2007

Store_Activations_Report:
Store, Activations, Month
A, 5, 1/2007
B, 15, 3/2007
A, 5, 3/2007
C, 25, 12/2007

here is my query:
Select [Store_Sales_Report].[Store] AS Store, [Store_Sales_Report].Sales,
[Store_Activation_Report].Activations, [Store_Sales_Report].Month
From [Store_Sales_Report] inner join [Store_Activation_Report] On
[Store_Sales_Report].[Store]=[Store_Activation_Report].Store
Where [Store_Sales_Report].Month = [Store_Activation_Report].Month
Union All
SELECT [Store_Sales_Report].Store AS Store ,[Store_Sales_Report].Sales,
[Store_Activation_Report].Activations, [Store_Sales_Report].Month
From [Store_Sales_Report] Left join [Store_Activation_Report] on
[Store_Sales_Report].[Store]=[Store_Activation_Report].Store
Where [Store_SALES_Report].Store is NOT NULL AND
Store_Activation_report.Store is NULL
UNION ALL SELECT [Store_Activation_Report].[Store] AS Store,
[Store_Sales_Report].Sales, [Store_Activation_Report].Activations,
[Store_Activation_Report].Month
From [Store_Activation_Report] left JOIN [Store_Sales_Report] On
[Store_Activation_Report].[Store]=[Store_Sales_Report].Store
WHERE [Store_Activation_Report].Store is NOT NULL AND
Store_Sales_report.Store is NULL;

The result is missing some records that from right join. I couldn't figure
it out

I want creat a union query to combine all data from two tables above by
customer, and the month

Would someone helps please?

 

[Rob Parker]

Hi Henry,

You need the joins in all three select clauses to be based on both Store and
Month. This should do it for you:

Select [Store_Sales_Report].[Store] AS Store, [Store_Sales_Report].Sales,
[Store_Activation_Report].Activations, [Store_Sales_Report].Month
From [Store_Sales_Report] inner join [Store_Activation_Report] On
(Store_Sales_Report.Month = Store_Activation_Report.Month) AND
(Store_Sales_Report.Store = Store_Activation_Report.Store)
Where [Store_Sales_Report].Month = [Store_Activation_Report].Month
Union All
SELECT [Store_Sales_Report].Store AS Store ,[Store_Sales_Report].Sales,
[Store_Activation_Report].Activations, [Store_Sales_Report].Month
From [Store_Sales_Report] Left join [Store_Activation_Report] on
(Store_Sales_Report.Month = Store_Activation_Report.Month) AND
(Store_Sales_Report.Store = Store_Activation_Report.Store)
Where [Store_SALES_Report].Store is NOT NULL AND
Store_Activation_report.Store is NULL
UNION ALL
SELECT [Store_Activation_Report].[Store] AS Store,
[Store_Sales_Report].Sales, [Store_Activation_Report].Activations,
[Store_Activation_Report].Month
From [Store_Activation_Report] left JOIN [Store_Sales_Report] On
(Store_Activation_Report.Month = Store_Sales_Report.Month) AND
(Store_Activation_Report.Store = Store_Sales_Report.Store)
WHERE [Store_Activation_Report].Store is NOT NULL AND
Store_Sales_report.Store is NULL;

BTW, Month is a reserved word and should not be used as a field name in your
tables (or as the name of anything in your database).

HTH,

Rob

hi all,

I have two tables define with the sample data as following:
Store_Sales_Report:
Store, Sales, Month
A, 10, 1/2007
B, 20, 2/2007
C, 30, 4/2007
D, 40, 12/2007
A, 50, 12/2007

Store_Activations_Report:
Store, Activations, Month
A, 5, 1/2007
B, 15, 3/2007
A, 5, 3/2007
C, 25, 12/2007

here is my query:
Select [Store_Sales_Report].[Store] AS Store, [Store_Sales_Report].Sales,
[Store_Activation_Report].Activations, [Store_Sales_Report].Month
From [Store_Sales_Report] inner join [Store_Activation_Report] On
[Store_Sales_Report].[Store]=[Store_Activation_Report].Store
Where [Store_Sales_Report].Month = [Store_Activation_Report].Month
Union All
SELECT [Store_Sales_Report].Store AS Store ,[Store_Sales_Report].Sales,
[Store_Activation_Report].Activations, [Store_Sales_Report].Month
From [Store_Sales_Report] Left join [Store_Activation_Report] on
[Store_Sales_Report].[Store]=[Store_Activation_Report].Store
Where [Store_SALES_Report].Store is NOT NULL AND
Store_Activation_report.Store is NULL
UNION ALL SELECT [Store_Activation_Report].[Store] AS Store,
[Store_Sales_Report].Sales, [Store_Activation_Report].Activations,
[Store_Activation_Report].Month
From [Store_Activation_Report] left JOIN [Store_Sales_Report] On
[Store_Activation_Report].[Store]=[Store_Sales_Report].Store
WHERE [Store_Activation_Report].Store is NOT NULL AND
Store_Sales_report.Store is NULL;

The result is missing some records that from right join. I couldn't figure
it out

I want creat a union query to combine all data from two tables above by
customer, and the month

Would someone helps please?

 

[KARL DEWEY]

This is a union query of your tables --
Select [Store_Sales_Report].[Store], [Store_Sales_Report].Sales, Null as
Activations, [Store_Sales_Report].Month
From [Store_Sales_Report]
Union All
SELECT [Store_Activation_Report].Store, Null AS Sales,
[Store_Activation_Report].Activations, [Store_Activation_Report].Month
From [Store_Activation_Report];

 

[KARL DEWEY]

This is a union query of your tables --
Select [Store_Sales_Report].[Store], [Store_Sales_Report].Sales, Null as
Activations, [Store_Sales_Report].Month
From [Store_Sales_Report]
Union All
SELECT [Store_Activation_Report].Store, Null AS Sales,
[Store_Activation_Report].Activations, [Store_Activation_Report].Month
From [Store_Activation_Report];