Need helps on my union query

  • Thread starter Thread starter henry
  • Start date Start date
H

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?
 
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


henry said:
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?
Click to expand...
 
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];
 
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];
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

BUILDING UNION QUERY 9
Need to assign Column Header Alias in MS Access Query 0
union query 4
Join on a UNION query 2
Access Database Union 3 tables 6
Include All Stores in Query 1
Union Query Edit 1
Union Query 6

Back
Top