Access 2010 query excluding records from another query

[PWD]

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

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:

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€$

 

[dhookom]

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

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

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

 

[PWD]

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

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

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:

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"))

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

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

Apologies if I'm being a thicky here.

Many thanks,
D€$

 

[dhookom]

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:

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

 

[PWD]

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

Many thanks,
D€$

 

[dhookom]

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