Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-13-2017, 11:29 AM   #1
reggiete
Newly Registered User
 
Join Date: Nov 2015
Posts: 56
Thanks: 13
Thanked 0 Times in 0 Posts
reggiete is on a distinguished road
Using query results as a where condition?

I have a select query that pull records based on certain where criteria. I want the recordIDs that are returned to be used in another query to see if the ID exists?

For example Select Query 1 pulls all records with a open status.
I want to have another query run taking the IDs found open in Query 1 to see if that ID exists with a closed status

reggiete is offline   Reply With Quote
Old 04-13-2017, 11:51 AM   #2
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: Using query results as a where condition?

The way you describe it the second query will have no records. Or am I being dense?
__________________
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
Old 04-13-2017, 12:00 PM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,350
Thanks: 10
Thanked 2,271 Times in 2,223 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Using query results as a where condition?

JOINS are essentially criteria:

Code:
SELECT tblClosed.ID
FROM tblClosed
INNER JOIN qryOpen ON qryOpen.ID = TableClosed.ID
With that said, if the open and closed records are coming from the same table, then it might be able to handle this with an aggregate query:

Code:
SELECT ID
FROM Table
WHERE Status='Open' OR Status='Closed'
GROUP BY ID
HAVING MAX(Status) <> MIN(Status)

plog is offline   Reply With Quote
Old 04-13-2017, 12:48 PM   #4
reggiete
Newly Registered User
 
Join Date: Nov 2015
Posts: 56
Thanks: 13
Thanked 0 Times in 0 Posts
reggiete is on a distinguished road
Re: Using query results as a where condition?

ok Plog. I create to temp tables.

T1 has all the IDs with open ( Query 1 inserted into)
and T2 has all the IDs with closed ( query 2 Inserted into)

Now I want to join the two tables and give me a list of IDs from T1 where the ID was not found in T2

CreateTable #T1(
TicketHistoryID varchar(255), TicketID varchar(255), MonthYear varchar(255), ActionTaken varchar(255),Statusvarchar(255), Mailbox varchar(255), TicketBucket varchar(255),DateTimeDateTime)
CreateTable #T2(
TicketHistoryID varchar(255), TicketID varchar(255), MonthYear varchar(255), ActionTaken varchar(255),Statusvarchar(255), Mailbox varchar(255), TicketBucket varchar(255),DateTimeDateTime)
reggiete is offline   Reply With Quote
Old 04-13-2017, 12:54 PM   #5
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,350
Thanks: 10
Thanked 2,271 Times in 2,223 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Using query results as a where condition?

Quote:
ok Plog. I create to temp tables.
Why? No reason to create temp tables, this can all be done via SQL. Also, you have changed what you want:

Quote:
Now I want to join the two tables and give me a list of IDs from T1 where the ID was not found in T2
That is entirely different from your initial post. Its still doable via a simple query, but this is not the same as what you initially posted. For this you would use a LEFT JOIN:

Code:
SELECT ID 
FROM Table1
LEFT JOIN Table2 ON Table2.ID = Table1.ID
WHERE Table2.ID IS NULL
plog is offline   Reply With Quote
Old 04-14-2017, 01:59 PM   #6
HiTechCoach
Newly Registered User
 
HiTechCoach's Avatar
 
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 4,254
Thanks: 8
Thanked 156 Times in 144 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: Using query results as a where condition?

I agree with plog that this can all be done with just queries (SQL).
TIP: When you are in the query designer, you can add a (saved) select query and use it just like you would a table!

You can even use the Unmatched query wizard to create this by selecting a table and the saved query.

Anther option is to use a sub query.

__________________
Boyd Trimmell
Specializing in Accounting, CRM, and Business solutions
Microsoft Access MVP 2010-2015 , aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach 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
OpenReport with Where Condition Returns no Results (Access 2013) Rbfilter Macros 4 10-06-2015 02:18 PM
Set if condition in query ootkhopdi Modules & VBA 5 10-24-2013 07:03 AM
IIf condition in query jzacharias Queries 3 11-06-2012 01:44 AM
How set query condition from vba mikehershey Modules & VBA 0 06-17-2010 05:17 AM
How to use if(IFF) condition in a query bibek24 Queries 3 01-16-2009 07:49 AM




All times are GMT -8. The time now is 07:04 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