How to filter a Union query?

[deletedT]

More than 15 years in MS Access and my first Union query.

I have this union query:

SELECT 0 As Expr1,  RecID, Ordered_Part  FROM tblOrders 
Union ALL
SELECT Top 2 Expr1, '' , '' FROM tblDummy

How can I filter tblOrders with this filter: "RecID='K01-12-001D" without affecting the shown data from tblDummy?

It means that after filtering I need tblDummy shows its 2 record just like tblOrders is not filtered.

Thanks for any kind of advice.

 

[deletedT]

I think I found the solution.

This one works:

SELECT 0 As Expr1, RecID, Ordered_Part FROM tblOrders WEHERE RecID='K01-12-001D'
Union ALL
SELECT Top 2 Expr1, '' , '' FROM tblDummy WHERE Expr1>0

 

[Gasman]

Do you even need the second WHERE?

 

[deletedT]

Do you even need the second WHERE?

No. But without that Access gives me an error. and doesn't run the query.


Edit: I swear I was receiving an error with only one side Where clause.
Tried it once again. It seems this time the second one can be omitted.

thanks for the lesson.

 

[isladogs]

Each part of a union query is totally independent. Changes to the first part (tbOrders) have no effect on the tblDummy section.
You probably want UNION rather than UNION ALL. The latter can cause duplicate records if both sections contain the same data

 

[deletedT]

Each part of a union query is totally independent. Changes to the first part (tbOrders) have no effect on the tblDummy section.
You probably want UNION rather than UNION ALL. The latter can cause duplicate records if both sections contain the same data

thanks for additional info..

 

[theDBguy]

thanks for additional info..

Hi. I may not be remembering it correctly, but the exception to that rule is you can’t sort each individual query. I think you’ll have to sort the entire union query.

 

[deletedT]

Hi. I may not be remembering it correctly, but the exception to that rule is you can’t sort each individual query. I think you’ll have to sort the entire union query.

I was reading several online articles about union query and they have used something like this:

SELECT 0 As Expr1,fld1,  fld2, fld3 FROM tbl1 ORDER BY 1,2 
Union All 
SELECT Expr1, '', '', '' FROM tbl2  Order By 1, 2

What does Order By 1,2 means here?
Does it mean Order By fields 1 & 2?

 

[theDBguy]

Does it mean Order By fields 1 & 2?

Yes, that's what I believe it means as well. Did you actually try those queries where they sort each one in a union? Curious...


PS. The sample union query may be misleading since the sort are the same for both queries. If it works, try making them different or only sort the first one and remove the sort in the second one. Just a thought...

 

[deletedT]

Did you actually try those queries where they sort each one in a union? Curious...

No, I was too busy with filtering union queries. Solving 2 puzzles at a time is out of my brain's capability with only one core.

If it works, try making them different or only sort the first one and remove the sort in the second one. Just a thought...

Yes, it'll be in my first priority next Tuesday as soon as I'm back to my desk.
(Monday is The Respect Day for the Aged and as a respect to our elders we don't work. )

 

[Dreamweaver]

This is one I use in my employees example

SELECT * From QryYearlyEventsAll WHERE [EventMonth]=" & F!Cmonth & " UNION SELECT * FROM QryYearlyEvents WHERE (([EventMonth]=" & F!Cmonth & " AND [EmployeeID]=" & E & "))

hope it helps


mick

 

[deletedT]

hope it helps

Sure it does.
Million thanks

 

[Pat Hartman]

I don't know whether you can use arguments in a union query. I've never tried it. You probably can as long as you remember to put the criteria on all the select queries. However, I normally create a second query that uses the Union query as the source and put the criteria there:

Select * from qUnion where SomeField = Forms!myform!txtSomeField;

 

[isladogs]

Just clarify some comments in earlier responses

1. You can filter on fields in any or all parts of the union query
For example this works fine

SELECT Table1.ID, Table1.T, Table1.N, Table1.C
FROM Table1
WHERE TABLE1.T<>'a'
ORDER BY Table1.T DESC
UNION
SELECT Table2.ID, Table2.T, Table2.N, Table2.C
FROM Table2
WHERE Table2.N=20
UNION 
SELECT Table3.ID, Table3.T, Table3.N, Table3.C
FROM Table3
WHERE TABLE3.C>4

2. However you can only sort on fields in the first part of the union. Trying to sort on other tables in the union will cause an error

 

[deletedT]

2. However you can only sort on fields in the first part of the union. Trying to sort on other tables in the union will cause an error

You're a time saver.
Million thanks for clarifying the tips, specially this one.

 

[sxschech]

Another approach is to wrap in () and then the query can behave as a standard select query. It will even show up in the graphical Design View. Of course, you won't be able to edit any contents within a particular SQL making up the union, but the overall Union query can then be treated and edited as a normal select query.

NOTE: Aliasing the overall set of tables as U for Union

Using the example query from these posts:

SELECT U.ID, U.T, U.N, U.C
FROM (
SELECT Table1.ID, Table1.T, Table1.N, Table1.C
FROM Table1
WHERE TABLE1.T<>'a'
ORDER BY Table1.T DESC
UNION
SELECT Table2.ID, Table2.T, Table2.N, Table2.C
FROM Table2
WHERE Table2.N=20
UNION 
SELECT Table3.ID, Table3.T, Table3.N, Table3.C
FROM Table3) AS U
WHERE U.C > 4
ORDER BY U.C DESC