Help with SQL in a union

[JustTrying9]

Hi all,

I am trying to get the WHERE clause in my SQL union query to work and haven't had any luck yet.

In my database, I have several tables with the exact same fields. I need an aggregated view, which the union does:

SELECT Table1.Question, Table1.Answer, Table1.Tags, Table1.CopyCount
FROM Table1;


UNION
SELECT Table2.Question, Table2.Answer, Table2.Tags, Table2.CopyCount
FROM Table2;
UNION
SELECT Table3.Question, Table3.Answer, Table3.Tags, Table3.CopyCount
FROM Table3;

However, for each Table, I have a query that has a search functionality built in. There is a form (Table1Home, Table2Home, etc.) for each of these with text/combo boxes so they can filter the results down to what they type in each.

The criteria is:

SELECT Table1.QuestionID, Table1.Question, Table1.Answer, Table1.Owner, Table1.Tags1, Table1.CopyCount
FROM Table1
WHERE (((Table1.Question) Is Null Or (Table1.Question) Like "*" & Forms!Table1Home!Text1 & "*") And ((Table1.Answer) Is Null Or (Table1.Answer) Like "*" & Forms!Table1Home!Text3 & "*") And ((Table1.Tags1) Is Null Or (Table1.Tags1) Like "*" & Forms!Table1Home!Combo25 & "*"))
ORDER BY Table1.CopyCount DESC;

The aggregated view will have it's own form (AllTablesHome). Any help on that WHERE clause so my union can behave like the query above would be great.

Thanks for taking a look!

[jwhite]

Add the WHERE clause to each SELECT statement:

SELECT *FROM x
WHERE DATE(y)='2012-01-01'
UNION
SELECT *FROM x
WHERE DATE(y)='2012-01-01'

[JustTrying9]

I tried to with this:

SELECT Table1.Question, Table1.Answer, Table1.Tags1, Table1.CopyCount
FROM Table1;
WHERE (((Table1.Question) Is Null Or (Table1.Question) Like "*" & Forms!AllTablesHome!Text1 & "*") And ((Table1.Answer) Is Null Or (Table1.Answer) Like "*" & Forms!AlltablesHome!Text3 & "*") And ((Table1.Tags1) Is Null Or (Table1.Tags1) Like "*" & Forms!AlltablesHome!Combo25 & "*"))
UNION
SELECT Table2.Question, Table2.Answer, Table2.Tags1, Table2.CopyCount
FROM Table2;
WHERE (((Table2.Question) Is Null Or (Table2.Question) Like "*" & Forms!AllTablesHome!Text1 & "*") And ((Table2.Answer) Is Null Or (Table2.Answer) Like "*" & Forms!AlltablesHome!Text3 & "*") And ((Table2.Tags1) Is Null Or (Table2.Tags1) Like "*" & Forms!AlltablesHome!Combo25 & "*"))
UNION
SELECT Table3.Question, Table3.Answer, Table3.Tags1, Table3.CopyCount
FROM Table3;
WHERE (((Table3.Question) Is Null Or (Table3.Question) Like "*" & Forms!AllTablesHome!Text1 & "*") And ((Table3.Answer) Is Null Or (Table3.Answer) Like "*" & Forms!AlltablesHome!Text3 & "*") And ((Table3.Tags1) Is Null Or (Table3.Tags1) Like "*" & Forms!AlltablesHome!Combo25 & "*"))

I also tried:

SELECT *
FROM (SELECT Table1.Question, Table1.Answer, Table1.Tags1, Table1.CopyCount
FROM Table1;
WHERE (((Table1.Question) Is Null Or (Table1.Question) Like "*" & Forms!AllTablesHome!Text1 & "*") And ((Table1.Answer) Is Null Or (Table1.Answer) Like "*" & Forms!AlltablesHome!Text3 & "*") And ((Table1.Tags1) Is Null Or (Table1.Tags1) Like "*" & Forms!AlltablesHome!Combo25 & "*"))
UNION
SELECT Table2.Question, Table2.Answer, Table2.Tags1, Table2.CopyCount
FROM Table2;
WHERE (((Table2.Question) Is Null Or (Table2.Question) Like "*" & Forms!AllTablesHome!Text1 & "*") And ((Table2.Answer) Is Null Or (Table2.Answer) Like "*" & Forms!AlltablesHome!Text3 & "*") And ((Table2.Tags1) Is Null Or (Table2.Tags1) Like "*" & Forms!AlltablesHome!Combo25 & "*"))
UNION
SELECT Table3.Question, Table3.Answer, Table3.Tags1, Table3.CopyCount
FROM Table3;
WHERE (((Table3.Question) Is Null Or (Table3.Question) Like "*" & Forms!AllTablesHome!Text1 & "*") And ((Table3.Answer) Is Null Or (Table3.Answer) Like "*" & Forms!AlltablesHome!Text3 & "*") And ((Table3.Tags1) Is Null Or (Table3.Tags1) Like "*" & Forms!AlltablesHome!Combo25 & "*"));

No luck either time

[ssanfu]

You do know that the semicolon ";" is the end-of-line indicator for SQL... ????
You have:

SELECT Table1.Question, Table1.Answer, Table1.Tags1, Table1.CopyCount
FROM Table1;   <<<---------
WHERE (((Table1.Question) Is Null Or (Table1.Question) Like "*" &  Forms!AllTablesHome!Text1 & "*") And ((Table1.Answer) Is Null Or  (Table1.Answer) Like "*" & Forms!AlltablesHome!Text3 & "*") And  ((Table1.Tags1) Is Null Or (Table1.Tags1) Like "*" &  Forms!AlltablesHome!Combo25 & "*"))
UNION
<snip>

Try deleting the ";" after each of the table names:

SELECT Table1.Question, Table1.Answer, Table1.Tags1, Table1.CopyCount
FROM Table1
WHERE (((Table1.Question) Is Null Or (Table1.Question) Like "*" & Forms!AllTablesHome!Text1 & "*") And ((Table1.Answer) Is Null Or (Table1.Answer) Like "*" & Forms!AlltablesHome!Text3 & "*") And ((Table1.Tags1) Is Null Or (Table1.Tags1) Like "*" & Forms!AlltablesHome!Combo25 & "*"))
UNION
SELECT Table2.Question, Table2.Answer, Table2.Tags1, Table2.CopyCount
FROM Table2
WHERE (((Table2.Question) Is Null Or (Table2.Question) Like "*" & Forms!AllTablesHome!Text1 & "*") And ((Table2.Answer) Is Null Or (Table2.Answer) Like "*" & Forms!AlltablesHome!Text3 & "*") And ((Table2.Tags1) Is Null Or (Table2.Tags1) Like "*" & Forms!AlltablesHome!Combo25 & "*"))
UNION
SELECT Table3.Question, Table3.Answer, Table3.Tags1, Table3.CopyCount
FROM Table3
WHERE (((Table3.Question) Is Null Or (Table3.Question) Like "*" & Forms!AllTablesHome!Text1 & "*") And ((Table3.Answer) Is Null Or (Table3.Answer) Like "*" & Forms!AlltablesHome!Text3 & "*") And ((Table3.Tags1) Is Null Or (Table3.Tags1) Like "*" & Forms!AlltablesHome!Combo25 & "*"))

[JustTrying9]

Simple enough, thank you!