×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Access 2010 query excluding records from another query

Access 2010 query excluding records from another query

Access 2010 query excluding records from another query

(OP)
Good afternoon, I have a query that will tell me if patients are likely to breach their 2-week-wait target and can be brought forward. This is fine. Now what I would like to do is to have another query that will have the same output and basic criteria, i.e. are going to breach, but only to select those who aren't in the first query Qry_2wwBreachAvoidance_20170309.

This is the SQL from the second query

CODE

SELECT IIf(IsNull([dbo_tblMAIN_REFERRALS].[N2_9_FIRST_SEEN_DATE]),Int(Now()-([N2_6_RECEIPT_DATE]-[N2_14_ADJ_TIME])),[dbo_tblMAIN_REFERRALS].[N2_9_FIRST_SEEN_DATE]-([N2_6_RECEIPT_DATE]-[N2_14_ADJ_TIME])) AS [Estimated 1st Appt Wait], dbo_tblDEMOGRAPHICS.N1_2_HOSPITAL_NUMBER AS MRN, dbo_tblDEMOGRAPHICS.N1_5_SURNAME AS Surname, dbo_tblDEMOGRAPHICS.N1_6_FORENAME AS [Forename(s)], fType([dbo_tblMAIN_REFERRALS]![N2_12_CANCER_TYPE]) AS [Cancer Type], dbo_tblMAIN_REFERRALS.N2_6_RECEIPT_DATE AS [Ref Recd], dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE AS [1st Appointment], [N2_6_RECEIPT_DATE]+14-[N2_14_ADJ_TIME] AS [Save A Breach Date], dbo_tblMAIN_REFERRALS.N2_11_FIRST_SEEN_REASON AS [Breach Reason]


FROM dbo_tblMAIN_REFERRALS INNER JOIN dbo_tblDEMOGRAPHICS ON dbo_tblMAIN_REFERRALS.PATIENT_ID = dbo_tblDEMOGRAPHICS.PATIENT_ID


WHERE (((dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE)>([N2_6_RECEIPT_DATE]+14-[N2_14_ADJ_TIME]) And (dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE)>Now()) AND ((dbo_tblMAIN_REFERRALS.N2_4_PRIORITY_TYPE)="03" Or (dbo_tblMAIN_REFERRALS.N2_4_PRIORITY_TYPE)="02"))


ORDER BY [N2_6_RECEIPT_DATE]+14-[N2_14_ADJ_TIME]; 

So what I want to achieve is the equivalent of adding this within the WHERE clause:

CODE

And not 

(( Qry_2wwBreachAvoidance_20170309.dbo_tblDEMOGRAPHICS.[N1_2_HOSPITAL_NUMBER]) = dbo_tblDEMOGRAPHICS.[N1_2_HOSPITAL_NUMBER])) 

Without having to include all the code from the first query. Is this possible?

Many thanks,
D€$

RE: Access 2010 query excluding records from another query

I typically use a subquery in the criteria of the primary key field.

CODE --> SQL

AND [PrimaryKeyFielD] Not In (SELECT [PrimaryKeyField] from Qry_2wwBreachAvoidance_20170309) 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Access 2010 query excluding records from another query

(OP)
Good morning! That seems to make sense, but I've probably misunderstood your response. I have added this additional line, based on the above:

CODE


AND [N1_2_HOSPITAL_NUMBER] Not In (SELECT [N1_2_HOSPITAL_NUMBER] from Qry_2wwBreachAvoidance_20170309) 
 

This has has two side-effects. 1) it takes an age to run and 2) it returns no data. Without this line the following code returns ALL the potential breaches:

CODE

SELECT 
IIf(IsNull([dbo_tblMAIN_REFERRALS].[N2_9_FIRST_SEEN_DATE])
,Int(Now()-([N2_6_RECEIPT_DATE]-[N2_14_ADJ_TIME])),[dbo_tblMAIN_REFERRALS].[N2_9_FIRST_SEEN_DATE]-([N2_6_RECEIPT_DATE]-[N2_14_ADJ_TIME])) AS [Estimated 1st Appt Wait]
, dbo_tblDEMOGRAPHICS.N1_2_HOSPITAL_NUMBER AS MRN
, dbo_tblDEMOGRAPHICS.N1_5_SURNAME AS Surname
, dbo_tblDEMOGRAPHICS.N1_6_FORENAME AS [Forename(s)]
, fType([dbo_tblMAIN_REFERRALS]![N2_12_CANCER_TYPE]) AS [Cancer Type]
, dbo_tblMAIN_REFERRALS.N2_6_RECEIPT_DATE AS [Ref Recd]
, dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE AS [1st Appointment]
, [N2_6_RECEIPT_DATE]+14-[N2_14_ADJ_TIME] AS [Save A Breach Date]
, dbo_tblMAIN_REFERRALS.N2_11_FIRST_SEEN_REASON AS [Breach Reason]


FROM dbo_tblMAIN_REFERRALS INNER JOIN dbo_tblDEMOGRAPHICS ON dbo_tblMAIN_REFERRALS.PATIENT_ID = dbo_tblDEMOGRAPHICS.PATIENT_ID

WHERE (((dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE)>([N2_6_RECEIPT_DATE]+14-[N2_14_ADJ_TIME]) And (dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE)>Now()) AND ((dbo_tblMAIN_REFERRALS.N2_4_PRIORITY_TYPE)="03" Or (dbo_tblMAIN_REFERRALS.N2_4_PRIORITY_TYPE)="02"))

AND [N1_2_HOSPITAL_NUMBER] Not In (SELECT [N1_2_HOSPITAL_NUMBER] from Qry_2wwBreachAvoidance_20170309) 

ORDER BY [N2_6_RECEIPT_DATE]+14-[N2_14_ADJ_TIME]; 

Apologies if I'm being a thicky here.

Many thanks,
D€$

RE: Access 2010 query excluding records from another query

First, it looks like you are referencing SQL Server tables. If so, I would use a pass-through query for the best performance.

I typically break things into their smallest pieces to test. Does this also run slow:

CODE --> SQL

SELECT [N1_2_HOSPITAL_NUMBER]
FROM dbo_tblMAIN_REFERRALS INNER JOIN dbo_tblDEMOGRAPHICS ON dbo_tblMAIN_REFERRALS.PATIENT_ID = dbo_tblDEMOGRAPHICS.PATIENT_ID
WHERE dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE>[N2_6_RECEIPT_DATE]+14-[N2_14_ADJ_TIME] And 
      dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE>Now() AND 
      dbo_tblMAIN_REFERRALS.N2_4_PRIORITY_TYPE IN ("03", "02")
AND [N1_2_HOSPITAL_NUMBER] Not In (SELECT [N1_2_HOSPITAL_NUMBER] from Qry_2wwBreachAvoidance_20170309) 


How many records are in your tables?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Access 2010 query excluding records from another query

(OP)
Hi, yeah it's still running. There are around 130,000 records in the MAIN_REFERRALS table.

Many thanks,
D€$

RE: Access 2010 query excluding records from another query

You can also use a LEFT JOIN to Qry_2wwBreachAvoidance_20170309 with and Is Null in the criteria under the Qry_2wwBreachAvoidance_20170309.N1_2_HOSPITAL_NUMBER field.

However... I highly recommend using a pass-through.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close