Can't make a query compare and exclude...

[surgicalstrike]

Hi everyone;



I've been working with a data set that I need to analyze for a research project. I have some basic MS Access skills but am unable to figure out how to do the following. I have a table that includes the following columns:

AccessIDCode (Unique AccessID, autogenerated)
FacilityDA (Code for Facility where surgery was performed)
SurgeonID (anonymized Surgeon identification number)
FacilityLocationCode (Location code)
PatientLinkCode (unique patient code)
ServiceEndDate (date of procedure)
CCPXCode (diagnostic code)
ICD9XCode1
ICD9XCode2
ICD9XCode3
PatientAgeAtServiceDate (Age of patient when procedure was performed)
DateOfFirstYAG (Date of the first YAG procedure)
PatientSex (sex of patient)
Patient DA (location of patient)

These columns can have numerous entries based on repeated procedures for each unique patient (ie for each Patient LinkCode) For example:

For example, you can see that patient 115778 has multiple columns, each for a different CCPXCode corresponding to a procedure.

What I want to do is create a query that selects data as follows:

Select ALL patients that have CCPX Codes (“27.3 A” OR “26.98A” OR “26.52A”) [Call this CCPX1]
Select ALL patients that have CCPX Codes ("28.2 B" Or "28.2 C" Or "28.4 A" Or "28.4 B" Or "28.5 A" Or "28.71A" Or "28.72B" Or "28.74A" Or "28.74B") [Call this CCPX2]
Exclude ALL the same patients (PatientLinkCode) that have any [CCPX2] on the same date (ServiceEndDate) as [CCPX1] and display the rest that DO NOT FIT THIS CRITERIA.

I haven’t been able to make this work for some reason. What I did was create two queries (one for CCPX1 and one for CCPX2). I then want to combine the records from both of them that don’t have CCPX1 and CCPX2 on the same date. I also tried doing this with one query but can’t make that work either.

I think it’s because each row is a different 'record'. For example, in the case above there are 6 records for patient 115778. Logically I can go through it and see this:
26.98A on 9/28/2004
No other codes for first criteria (27.3 A OR 26.52A)
28.74B on 9/19/2000
28.5 A on 9/19/2000
28.5 A on 12/8/2000
28.74B on 12/8/2000
And that none of these codes occur on the same date as the first group of codes (that no CCPX1 occur on the same date as CCPX2).

For some reason I can’t make a select query that compares both of these groups of criteria and displays the ones that don’t meet it.

What am I doing wrong?

[June7]

Selecting a record based on data in another record in same table can be tricky. Maybe like:

SELECT * FROM tablename WHERE NOT PatientLinkCode IN (SELECT Query1.PatientLinkCode FROM Query1 INNER JOIN Query2 ON Query1.PatientLinkCode=Query2.PatientLinkCode AND Query1.ServiceEndDate = Query2.ServiceEndDate);

[surgicalstrike]

Hi June7;

THanks for the query. When I put that code into a new query and use the tablename as the table I need, query 1 as the CCPX1 and query 2 as the CCPX2, access crashes and will not recover. Any thoughts?

Surge

[June7]

Would have to test with data - if you want to provide, follow instructions at bottom of my post.

[CJ_London]

try this approach _ I'm assuming that AccessIDCode is your primary key - Ignore, see next post

SELECT *
FROM YAGPRUNED
WHERE CCPXCode IN ("27.3 A", "26.98A", "26.52A","28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B")
AND AccessIDCode NOT IN (SELECT AccessIDCode FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXCode NOT IN ("27.3 A", "26.98A", "26.52A") AND ServiceEndDate=YAGPRUNED.ServiceEndDate)

[CJ_London]

Actually - that is not quite right - it would exclude ones in the set where there is a record outside the set with the same date - so revised to

SELECT *
FROM YAGPRUNED
  WHERE 
    (CCPXCode IN ("27.3 A", "26.98A", "26.52A")
       AND not Exists(SELECT * FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4  B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") AND ServiceEndDate=YAGPRUNED.ServiceEndDate))
   OR 
    (CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B) 
       AND not Exists(SELECT * FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXCode IN ("27.3 A", "26.98A", "26.52A") AND ServiceEndDate=YAGPRUNED.ServiceEndDate)

[surgicalstrike]

Hi Ajax;

Thanks for the code. When I copy and paste from your code snippet above, I get an error when I try to go to datasheet mode:

Don't know where the syntax error is?

Surge.

[surgicalstrike]

Here is a sample of the database with only about 4000 records from it. The entire database is over 97,000 records. Hope this helps.

TESTYAG.zip

[CJ_London]

Always helps when you can run the query!

SELECT *
FROM YAGPRUNED
  WHERE 
    (CCPXCode IN ("27.3 A", "26.98A", "26.52A")
       AND not Exists(SELECT * FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4  B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") AND ServiceEndDate=YAGPRUNED.ServiceEndDate))
   OR 
    (CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") 
       AND not Exists(SELECT * FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXCode IN ("27.3 A", "26.98A", "26.52A") AND ServiceEndDate=YAGPRUNED.ServiceEndDate))

I missed a double quote and a bracket as indicated in red above (or just copy this instead)

I also noticed you have some duplicates in your data e.g. patientlinkcode=39919, ccpxcode=28.74B (different surgeon ID). These are taken out because there is a 27.3A for the same day per your exclusion rules. But if one 27.3A is only supposed to take out one 28.74B (leaving one 28.74B) then the query needs more work

[surgicalstrike]

Hi Ajax;

Thanks for this, sorry for taking so long to reply...long days at the hospital.

I am unable to find the patientlink ID of 39919 so I'm unsure of which patient you mean. I understand your point about the query as it sits. What I want to do is be able to exclude only the records that fall on the same day, not the entire patient ID. For example, if there is a patient (let's make up a code, call it 11111) that has 5 records, where 2 of them fit the criteria:
("27.3 A", "26.98A", "26.52A") where ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") occur on the same date
then
exclude both of those records from patient set of 5 records and leave the rest (3 of them) intact for subsequent analysis.

Also, is there anyway to get a report of the records that WERE excluded so I could check them manually (at least some of them?).

Thanks for all of your help with this. I might eventually need your real name to include you in the collaborators section of the paper when it is finished and ready for submission!

Surge.

[CJ_London]

sorry typo on my part should be 39918.

You haven't said whether the solution I suggested meets your requirements or not - please clarify

rather than making up examples, use examples from your dataset - identify them and explain why it should be excluded (if it is currently included) or included (and is not currently included) in the query

Also, is there anyway to get a report of the records that WERE excluded so I could check them manually (at least some of them?).

you need a left join between your original table and this query - this should do this

SELECT YAGPRUNED.* 
FROM YAGPRUNED LEFT JOIN (SELECT *
FROM YAGPRUNED
  WHERE 
    (CCPXCode IN ("27.3 A", "26.98A", "26.52A")
       AND not Exists(SELECT * FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4  B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") AND ServiceEndDate=YAGPRUNED.ServiceEndDate))
   OR 
    (CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") 
       AND not Exists(SELECT * FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXCode IN ("27.3 A", "26.98A", "26.52A") AND ServiceEndDate=YAGPRUNED.ServiceEndDate))) AS Qry
ON YAGPRUNED.AccessIDCode=Qry.AccessIDCode
WHERE Qry.AccessIDCode is Null

[surgicalstrike]

Hi Ajax;

You are right, it makes more sense to use data from the tables. I believe the code you specified after the correction you made to the syntax works exactly like what I was expecting. For example:

PatientIDCode=39918 (The fact there is two surgeonID's is not problematic at this point)
This code has 3 total row entries, with the following relevant data:
12/11/2008 27.3 A
12/11/2008 28.74B
12/11/2008 28.74B

From the query this would mean that since there is a 27.3 A on the same day as one of the other codes to delete all the codes with the same date. This is in fact what happens, since the new query does NOT have any of these records in it. Success!

PatientIDCode=975978
This patient code has 6 original records, with the following relevant data:
9/28/2001 27.3 A
9/28/2001 28.4 B
9/28/2001 28.74B
8/24/2001 28.4 A
8/24/2001 28.2 C
4/2/2002 26.98A

In this case, there are three total records on the same date where a 27.3 A has occurred as well as two other codes (28.4 B and 28.74B). The remaining 3 records DO NOT fit the exclusion criteria, so we would expect to see them in the query, but not the first 3 records (that occur on the same date).

What we seen in the new query is:
8/24/2001 28.4 A
8/24/2001 28.2 C
4/2/2002 26.98A

Which is a success. I think the query that you wrote for the criteria works perfectly so far. I wasn't expecting there to be a large number of these occurrences and from entire data-set of ~90000 records there are only about 2000 so that is good.

In running your new query to determine those that are excluded, ACCESS runs the query for an hour and never finishes. Is there any problem with the code that would cause this? I can't see this query taking over an hour to do (the one listed below). I have a quad core i7 system with 24 GB RAM. What are your thoughts on this?

Also, is there anyway to get a report of the records that WERE excluded so I could check them manually (at least some of them?).

You need a left join between your original table and this query - this should do this

SELECT YAGPRUNED.* 
FROM YAGPRUNED LEFT JOIN (SELECT *
FROM YAGPRUNED
  WHERE 
    (CCPXCode IN ("27.3 A", "26.98A", "26.52A")
       AND not Exists(SELECT * FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4  B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") AND ServiceEndDate=YAGPRUNED.ServiceEndDate))
   OR 
    (CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") 
       AND not Exists(SELECT * FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXCode IN ("27.3 A", "26.98A", "26.52A") AND ServiceEndDate=YAGPRUNED.ServiceEndDate))) AS Qry
ON YAGPRUNED.AccessIDCode=Qry.AccessIDCode
WHERE Qry.AccessIDCode is Null

[CJ_London]

I'm surprised it is taking that long - but it is a large dataset

Check that relevant columns are indexed AccessIDCode, PatientLinkCode, CCPXCode and ServiceEndDate

After that, two things to try.

1. I may have missed something so assuming the 'success' query is called query1 try this code

SELECT YAGPRUNED.* 
FROM YAGPRUNED LEFT JOIN Query1
ON YAGPRUNED.AccessIDCode=Query1.AccessIDCode
WHERE Query1.AccessIDCode is Null

2. If that doesn't work, change the 'success' query to a make table query and make a table called say 'success'. Ensure the AccessIDCode in this table is indexed. Then use the following code

SELECT YAGPRUNED.* 
FROM YAGPRUNED LEFT JOIN Success
ON YAGPRUNED.AccessIDCode=Success.AccessIDCode
WHERE Success.AccessIDCode is Null

The other thing that may be slowing it up is the CCPXCode IN component

it might be you can speed things up by creating a new column called say CCPXGroup (make sure it is type number and indexed) and run an update query to update this column with 1 for "27.3 A", "26.98A", "26.52A" and 2 for the others e.g.

UPDATE YAGPRUNED
SET CCPXGroup=choose(CCPXCode IN ("27.3 A", "26.98A", "26.52A"),1,CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B"),2)

and then your success query would become

SELECT *
FROM YAGPRUNED
  WHERE 
    (CCPXGroup=1 AND not Exists(SELECT * FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXGroup=2 AND ServiceEndDate=YAGPRUNED.ServiceEndDate))
   OR 
    (CCPXGroup=2 AND not Exists(SELECT * FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXGroup=1 AND ServiceEndDate=YAGPRUNED.ServiceEndDate))

Just had another though which could be quicker - (based on the CCPXGroup suggestion) which should work is

SELECT YAGPRUNED.*
FROM (YAGPRUNED LEFT JOIN (SELECT * FROM YAGPRUNED AS T WHERE CCPXGroup=1) AS Grp1 ON YAGPRUNED.PatientLinkCode=Grp1.PatientLinkCode AND YAGPRUNED.ServiceEndDate=Grp1.ServiceEndDate) LEFT JOIN (SELECT * FROM YAGPRUNED AS T WHERE CCPXGroup=2) AS Grp2 ON YAGPRUNED.PatientLinkCode=Grp2.PatientLinkCode AND YAGPRUNED.ServiceEndDate=Grp2.ServiceEndDate
  WHERE 
    (GRP1.AccessLinkCode is Null AND Grp2.AccessLinkCode is Null)

If you don't want to go the CCPXGroup route then replace

CCPXGroup=1 with CCPXCode IN ("27.3 A", "26.98A", "26.52A") and
CCPXGroup=2 with CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B")

Hope all that is not too confusing!

[surgicalstrike]

Hi Ajax;

It wasn't too confusing. I indexed the fields as you recommended. The first query did not work but the second one certainly did.

SELECT YAGPRUNED.*
FROM YAGPRUNED LEFT JOIN Success
ON YAGPRUNED.AccessIDCode=Success.AccessIDCode
WHERE Success.AccessIDCode is NullThe query took like a half-second to run! Where "Success" was a table that I had created from the working query before. The new query worked exactly as you suggested, with both tables now showing the total number of records so that I can spot check them.

The only thing that I think I need to do now is analyze the data from the master table (the 90,000 newly pruned records) by date interval. I have tried modifying the query you originally wrote (below) but I can't even get the syntax right:

SELECT *
FROM YAGPRUNED
WHERE (CCPXCode IN ("27.3 A", "26.98A", "26.52A")
AND not Exists(SELECT * FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B") AND ServiceEndDate=YAGPRUNED.ServiceEndDate))
OR
(CCPXCode IN ("28.2 B", "28.2 C", "28.4 A", "28.4 B", "28.5 A", "28.71A", "28.72B", "28.74A", "28.74B")
AND not Exists(SELECT * FROM YAGPRUNED AS T WHERE PatientLinkCode=YAGPRUNED.PatientLinkCode AND CCPXCode IN ("27.3 A", "26.98A", "26.52A") AND ServiceEndDate=YAGPRUNED.ServiceEndDate));

Can I use the queries (with modifications) you have given to detect between a specified range of dates?

For example, I want to have 4 separate queries that present the data as previously described (all fields) that fall between two criteria.

For example:

First instance of either ("27.3 A", "26.98A", "26.52A") and then either 14, 30, 90 or >90 days between the other codes ("28.2 B","28.2 C","28.4 A","28.4 B","28.5 A","28.71A","28.72B","28.74A","28.74B") and then return a new field as well with that number of days between.

I think it would make sense to first make a query/table that only has the records of a first criteria ("27.3 A", "26.98A", "26.52A") followed immediately by a second criteria code ("28.2 B","28.2 C","28.4 A","28.4 B","28.5 A","28.71A","28.72B","28.74A","28.74B") and exclude all the rest.

For example, on patientID 140218, the records are:

10/27/2000 28.74B
10/27/2000 28.4 B
1/31/2001 28.5 A
1/31/2001 28.74B
4/20/2001 26.98A
8/18/2004 28.72B

So, applying our criteria non-programmatically but manually, we would see that the first instance of a 26.98A code is 4/20/2001 and the NEXT applicable code is a 28.72B on 8/18/2004. The days between those are 1216 days. So, I would like a new table with all the fields and the two records that are applicable (4/20/2001) and (8/18/2004) with a new field titled "DaysBetween" that has the number 1216 on the same row as the 28.72B code. The other records in this query would be excluded since they occur before the first instance of the first criteria (that is "27.3 A", "26.98A", "26.52A").

So the new table (or query--I can just make it into a table later) would have the following data including all the fields:

4/20/2001 26.98A
8/18/2004 28.72B with a new field titled "DaysBetween" that has the number 1216 on this row.

Then, I would run the final queries on the newly created table for the 14, 30, 90 or >90 days so that only the patientID's and the corresponding DaysBetween would be listed.

So, in the case of the 14 day query the above 2 records would not be there.
Same with 30, 90.
But would fall in the category of >90 days.

Another example PatientID 875228, 6 records:

10/21/2010 26.98A
1/26/2011 28.5 A
3/24/2011 28.5 A
4/7/2011 28.4 A
1/18/2013 28.5 A
1/22/2013 28.4 A

So, applying same criteria, we would have:

10/21/2010 26.98A
1/26/2011 28.5 A with a new field titled "DaysBetween" that has the number 97 on this row.

Another example, Patient ID 1308418
5/22/2007 28.4 B
5/22/2007 28.72B
5/22/2008 28.74B
1/23/2009 28.4 A
1/30/2009 28.4 A
1/7/2011 27.3 A

There is no 28 code beyond the first 27.3 code (1/7/2011) so this PatientID would not show up in the new table.

Another example PatientID 1585418, 9 records

8/16/2001 28.4 A
9/5/2001 27.3 A
1/10/2002 26.98A
1/15/2002 28.4 A
3/4/2002 26.98A
5/14/2002 28.74B
5/14/2002 28.4 A
5/28/2002 28.72B
5/28/2002 28.4 A

The first instance of the first criteria is 9/5/2001 27.3 A. There is no corresponding 28.x code before the next first criteria code on 1/10/2002 26.98A. The very next 28.x code is on 1/15/2001 28.4 A which is 5 days apart. There is then another first criteria code on 3/4/2002 26.98A with a corresponding 28.x code thereafter on 5/14/2002 28.74B which is 71 days. There are more 28.x codes after that but they are not relevant since they do not have a first criteria code attached before them.

So the query would have the following data in it:

1/10/2002 26.98A
1/15/2001 28.4 A Then a new field titled "DaysBetween" with the number 5 in it on the same row as the 28.4 A code.
3/4/2002 26.98A
5/14/2001 28.74B Then a new field titled "DaysBetween" with the number 71 in it on the same row as the 28.74B code.

Is there a way to do this or is it too complicated for an SQL query?

Thanks so much for the help...I'm a student and all the help you are giving me is contributing loads to my education!

Surge

[CJ_London]

have a busy day today, so not sure I'll be able to help with this. Can I suggest you attach a small dataset (in excel will be fine and only require the relevant fields) of four or 5 patients who match the criteria so they will be reported.