Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-13-2019, 09:27 PM   #1
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 240
Thanks: 403
Thanked 19 Times in 18 Posts
Tera is on a distinguished road
How to filter a Union query?

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 is offline   Reply With Quote
Old 09-13-2019, 09:58 PM   #2
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 240
Thanks: 403
Thanked 19 Times in 18 Posts
Tera is on a distinguished road
Re: How to filter a Union query?

I think I found the solution.

This one works:
Quote:
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
Tera is offline   Reply With Quote
Old 09-13-2019, 11:34 PM   #3
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,082
Thanks: 421
Thanked 751 Times in 729 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: How to filter a Union query?

Do you even need the second WHERE?

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 09-13-2019, 11:54 PM   #4
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 240
Thanks: 403
Thanked 19 Times in 18 Posts
Tera is on a distinguished road
Re: How to filter a Union query?

Quote:
Originally Posted by Gasman View Post
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.
Tera is offline   Reply With Quote
Old 09-14-2019, 01:22 AM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,356
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How to filter a Union query?

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Tera (09-14-2019)
Old 09-14-2019, 05:30 AM   #6
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 240
Thanks: 403
Thanked 19 Times in 18 Posts
Tera is on a distinguished road
Re: How to filter a Union query?

Quote:
Originally Posted by isladogs View Post
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..
Tera is offline   Reply With Quote
Old 09-14-2019, 05:54 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,608
Thanks: 50
Thanked 1,052 Times in 1,033 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: How to filter a Union query?

Quote:
Originally Posted by Tera View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Tera (09-14-2019)
Old 09-14-2019, 06:08 AM   #8
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 240
Thanks: 403
Thanked 19 Times in 18 Posts
Tera is on a distinguished road
Re: How to filter a Union query?

Quote:
Originally Posted by theDBguy View Post
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?
Tera is offline   Reply With Quote
Old 09-14-2019, 06:14 AM   #9
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,608
Thanks: 50
Thanked 1,052 Times in 1,033 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: How to filter a Union query?

Quote:
Originally Posted by Tera View Post
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...
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 09-14-2019, 06:34 AM   #10
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 240
Thanks: 403
Thanked 19 Times in 18 Posts
Tera is on a distinguished road
Re: How to filter a Union query?

Quote:
Originally Posted by theDBguy View Post
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.

Quote:
Originally Posted by theDBguy View Post
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. )
Tera is offline   Reply With Quote
Old 09-14-2019, 08:11 AM   #11
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 930
Thanks: 64
Thanked 48 Times in 41 Posts
MickJav will become famous soon enough
Re: How to filter a Union query?

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
__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

MickJav is offline   Reply With Quote
The Following User Says Thank You to MickJav For This Useful Post:
Tera (09-14-2019)
Old 09-14-2019, 09:17 AM   #12
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 240
Thanks: 403
Thanked 19 Times in 18 Posts
Tera is on a distinguished road
Re: How to filter a Union query?

Quote:
Originally Posted by MickJav View Post
hope it helps
Sure it does.
Million thanks
Tera is offline   Reply With Quote
Old 09-15-2019, 07:32 AM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,149
Thanks: 15
Thanked 1,572 Times in 1,494 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: How to filter a Union query?

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;
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-15-2019, 08:40 AM   #14
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,356
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How to filter a Union query?

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

Attached Images
File Type: png unionquerysort.PNG (15.9 KB, 55 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Tera (09-15-2019)
Old 09-15-2019, 03:49 PM   #15
Tera
Newly Registered User
 
Join Date: Feb 2019
Posts: 240
Thanks: 403
Thanked 19 Times in 18 Posts
Tera is on a distinguished road
Re: How to filter a Union query?

Quote:
Originally Posted by isladogs View Post
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.

Tera is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using a multi-select list box to filter a union query (Access 2010) LikeADuck Queries 1 06-22-2016 02:30 AM
Using a union query in conjunction with a filter jjake Queries 1 12-16-2015 05:53 PM
Union query works but will not filter report. Stang70Fastback Queries 3 02-28-2013 06:45 AM
Union query report, parameter prompt? Report filter? Lifeseeker Queries 1 05-12-2011 08:26 PM
filter which queries are used in union query shenty Queries 22 06-29-2009 05:16 AM




All times are GMT -8. The time now is 07:07 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World