where clause is not working in Union query

[MilaK]

I'm trying to add "All" option to a combobox and encountering difficulties with 'Where' clause of a UNION query.

Please suggest how to correct the following SQL:

SELECT Distinct tbl_Samples.sample_name, tbl_Samples.run_name FROM 

tbl_Samples UNION Select 0 as AllChoice, "(All)" as Bogus from tbl_Samples 

WHERE (((tbl_Samples.sample_name) Not Like "*QC*") AND

 ((tbl_Samples.run_name)=[Forms]![frm_First_Review_Comments]![frmReview_One].[Form]![txtCurrentRun].[Caption]));

The where clause is not working, however, this query worked before I converted it to a UNION query. Thanks, Mila

 

[pbaldy]

Each SELECT clause needs its own WHERE. The clause you have only applies to the second SELECT.

 

[plog]

Simple steps. A query object that uses a UNION query should only use 3 SQL keywords: SELECT, FROM and UNION. If you need any other keywords (JOIN, WHERE, GROUP BY, HAVING, ORDER BY), then those should go into another query object.

That means, if you want your current WHERE to apply to the entire dataset that results from the UNION, then it needs to go into a query object built upon the UNION query. So, save your UNION query (minus the WHERE) as a new query object--sub1. Then, build a new query and bring in sub1 and apply your criteria there.

 

[pbaldy]

Should...why?

 

[plog]

It's the same reason you should make sub-queries their own objects instead of writing them inline in just one object.--Query objects with more than 1 SELECT requires at least intermediate level knowledge of SQL to debug them, if you stick to 1 SELECT per query object you get to use the Query Designer.

So the only SELECT/FROM rule in UNION queries keeps that SQL simpler since you must hand write it. Moving all the other keywords to another query object lets you use the Designer to view and edit that object.

Take this for example:

SELECT EmpID, Year(SalesDate) AS SalesYear, SUM(Revenue) AS TotalRevenue
FROM TableA
WHERE EmpID<100 AND Year(SalesDate)>2010
GROUP BY EmpID, Year(SalesDate)
HAVING Sum(Revenue) > 1000
UNION
SELECT EmpID, SalesYear, Revenue AS TotalRevenue
FROM TableB
WHERE SalesYear>2010 AND TotalRevenue>1000

It's doing way too much. It should be 3 queries:

sub1 - essentially the first SELECT of the above UNION, but with the criteria in common with the 2nd SELECT removed.

sub2 - the UNION, we don't need a sub for the 2nd SELECT because once we remove the common criteria it conforms to the SELECT/FROM rule.

Final - the the final query, based on sub2 with the common criteria added back.


Now, as you build the 3 queries you get to spot check that they are returning the seemingly right data. Also, with the UNION if you do get unexpected results you are going to have to copy out the SQL of the individual SELECTS into a new query anyway to debug it and track down the unexpected results. When you start that way, it makes it easier to build to begin with and easier to track when things go bad.

 

[MilaK]

Each SELECT clause needs its own WHERE. The clause you have only applies to the second SELECT.

It worked when I added the same where clause to both statements, however, it shows "0" as a choice intead of "All". Any idea why? Thanks

 

[plog]

I don't know what you mean when you say:

"0" as a choice intead of "All"

What's a choice?

I mean in one of your SELECTs you explicitly tell it to return 0 for the column called [AllChoice]:

 Select 0 as AllChoice, "(All)" as Bogus from tbl_Samples

 

[MilaK]

Yes, but it should be displayed as (all) not 0.

 

[plog]

Then change the 0 to "(all)".

 

[MilaK]

Changing 0 to "(All)" worked. However, when I add more fields to select to the query I get and error: "The number of columns in the two selected tables or queries of a union query do not match." How can it be? I'm only using one table for this. Thanks

SELECT tbl_Samples.sample_id, tbl_Samples.sample_name, tbl_Samples.run_name, tbl_Samples.sample_type
 
FROM tbl_Samples
 
WHERE (((tbl_Samples.sample_name) Not Like "*QC*") AND ((tbl_Samples.run_name)=[Forms]![frm_First_Review_Comments]![frmReview_One].[Form]![txtCurrentRun].[Caption])) 
  
UNION Select "(All)" as AllChoice, "(All)" as Bogus from tbl_Samples
  
WHERE (((tbl_Samples.sample_name) Not Like "*QC*") AND ((tbl_Samples.run_name)=[Forms]![frm_First_Review_Comments]![frmReview_One].[Form]![txtCurrentRun].[Caption]));

 

[pbaldy]

The first SELECT has 4 fields, the second only 2. They must match.

 

[plog]

I really think its time for you to explain what you are trying to do as opposed just the issues you are having with individual steps.

Without using any database jargon (tables, queries, SQL, etc.) please describe the real world process this data represents. Then, using minimal database jargon, explain what it is you are trying to accomplish.

 

[MilaK]

Got it! Thanks