How to filter a Union query? (1 Viewer)

Tera

Registered User.
Local time
Tomorrow, 02:24
Joined
Feb 2, 2019
Messages
1,176
More than 15 years in MS Access and my first Union query.

I have this union query:
Code:
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.
 

Tera

Registered User.
Local time
Tomorrow, 02:24
Joined
Feb 2, 2019
Messages
1,176
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

Enthusiastic Amateur
Local time
Today, 18:24
Joined
Sep 21, 2011
Messages
6,376
Do you even need the second WHERE?
 

Tera

Registered User.
Local time
Tomorrow, 02:24
Joined
Feb 2, 2019
Messages
1,176
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

CID VIP
Local time
Today, 18:24
Joined
Jan 14, 2017
Messages
13,623
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
 

Tera

Registered User.
Local time
Tomorrow, 02:24
Joined
Feb 2, 2019
Messages
1,176
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

I’m here to help
Staff member
Local time
Today, 10:24
Joined
Oct 29, 2018
Messages
11,219
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.
 

Tera

Registered User.
Local time
Tomorrow, 02:24
Joined
Feb 2, 2019
Messages
1,176
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:
Code:
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

I’m here to help
Staff member
Local time
Today, 10:24
Joined
Oct 29, 2018
Messages
11,219
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...
 

Tera

Registered User.
Local time
Tomorrow, 02:24
Joined
Feb 2, 2019
Messages
1,176
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. :) )
 

MickJav

It wasn't me
Local time
Today, 18:24
Joined
Nov 28, 2005
Messages
2,097
This is one I use in my employees example
Code:
SELECT * From QryYearlyEventsAll WHERE [EventMonth]=" & F!Cmonth & " UNION SELECT * FROM QryYearlyEvents WHERE (([EventMonth]=" & F!Cmonth & " AND [EmployeeID]=" & E & "))

hope it helps


mick
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:24
Joined
Feb 19, 2002
Messages
29,197
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

CID VIP
Local time
Today, 18:24
Joined
Jan 14, 2017
Messages
13,623
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
Code:
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

 

Attachments

Tera

Registered User.
Local time
Tomorrow, 02:24
Joined
Feb 2, 2019
Messages
1,176
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

Registered User.
Local time
Today, 10:24
Joined
Mar 2, 2010
Messages
668
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:

Code:
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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom