Union Queries with WHERE clauses??

[StayAtHomeMom]

Hello all,

Can Union queries have WHERE clauses? I tried to insert one, and the "union" part doesn't work. The one query I use grabs data for the current year, and one query grabs data totals for the last year, and I merge, or union, them together. It works great until I tried adding a conditional where, and now I get one line for current year data, and one line with the last year data totals. Can anyone offer suggestions, or tell me if this is even possible?

MY UNION QUERY:

SELECT [Divn] , [RSM#], [RSMName], [TerrNo], [RepName], [DistName], [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [YTD Cost], [LY Cost] FROM [RPT115A-Cost by RSM-Terr-Dist] WHERE (([Divn])="ETUTL") UNION SELECT [Divn] , [RSM#], [RSMName], [TerrNo], [RepName], [DistName], [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec], [YTD Cost], [LY Cost] FROM [RPT115B-Cost by RSM-Terr-Dist] WHERE (([Divn])="ETUTL");

Again, it works great if I take out the WHERE clauses.

Thanks for your brilliant assistance!

 

[Golom]

Each SELECT within a union may have a WHERE clause.

The UNION operator eliminates duplicate records so you might try UNION ALL that retains them.

 

[StayAtHomeMom]

I'm sorry Golom,

I don't know what you mean when you say try UNION ALL. Does that mean the statement would read UNION ALL SELECT...?

 

[lespaul]

yes, that's what it means:

SELECT * FROM Table1
UNION
SELECT * FROM Table2

the above statement eliminates duplicates


SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2

will return all rows even if it's a duplicate.

Does each query work independently without the UNION?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[StayAtHomeMom]

STOP THE PRESSES!!

I found the reason it didn't work. I had an error in my Join Properties. I'm sooo embarrassed...

Thank you anyway lespaul and golom for answering my call for help.

 

[lespaul]

It's not all bad, you learned something new!

leslie