Count Duplicate values that meet specific criteria

[xxx]

I want to count the number of patients that have duplicate records or entries that meet specific criteria.

More specifically, I have a listing of CVCs. There is an entry for each CVC for a given patient. I want to count how many patients had more than 1 jugular CVC, how many patients have more than 1 subclavian CVC, etc. Is there a way to do this using a function rather than creating separate queries for each type of CVC?

Please, let me know if the request is unclear.

 

[jdorste]

Hi, you should be able to do this in 2 queries.

Query 1: Create a field for each CPT code you want to check for duplicates.

Example CPT code 36555

Use this code for the field: (Assuming CPT Code is your field name)

36555: IIf([CPT Code]="36555",1,0)

This will generate a 1 for any record with that CPT code.

Query 2:

Add the fields that you want for patient name, ID etc... and also the field(s) you created in Query 1.

Hit the Totals Button to add the Group By to the fields associated with the patient. For the CPT code fields you created in Query 1, 36555 for example, change the Group By to SUM.

Run this query, and you will see the totals for those CPT codes for each patient. Throw in a sort if you want.

(Make sure that the fields in Query 2 that are not the CPT Code fields you created in Query 1 are specific to the patient, and not the claim. For example, Only use First Name, Last Name, SSN etc... Do not use something like TCN number. If the same person had the same CPT code on claims with different TCN numbers, they will show up in query 2 multiple times.)

 

[xxx]

Thank you, jdorste.

However, is there no way to do this with a function similar to:

[FONT=&quot]Val(DCountDistinct("SUBJECTNUMBERSTR","FCRD_CVC01_ACTIVE","[CVCSITE] Like 'Subclavian*'")) AS SUBSUBCLV_PLACED

[/FONT]​

There are so many conditions and combinations that I need to summarize. I'm trying to minimize the number of separate queries needed to summarize all of the information. I really like functions like the one above because you can summarize lots of different scenarios and information from multiple tables in ONE query. [FONT=&quot][/FONT]
[FONT=&quot][/FONT]

 

[plog]

Jd's overall method of 2 queries is good, but I think the individual fields per CPT is a bad idea. Your subquery should GROUP BY the PatientID and the Procedure and COUNT the Procedure. Get your data like this in the subquery:

PatientID, Procedure, ProcCount
123, Jugular CVC, 2
444, Jugular CVC, 1
123, subclavian CVC, 1
999, Jugular CVC, 2
777, Jugular CVC, 1
888, subclavian CVC, 3

Then in the main query, you GROUP BY the Procedure and COUNT the PatientID and use the criteria >1 on the ProcCount. Your final data will look like so:

Procedure, PatientsWithMultipleProcs
Jugular CVC, 2
sublcavian, 1
...



Also, you didn't say you had to use a definition on this (e.g. CPT 1234=Jugular, CPT 1235=Jugular, CPT 812=sublcavian,etc.). If that is the case the subquery gets a little more complicated, but not much.

 

[xxx]

Thank you, Plog. Your suggestion is closer to what I need. Plus, I can also just create the first query you suggested, name it "MULTI_CVC", and then use the following function in the other query I've already been working with to summarize all of the CVC conditions/information:

[FONT=&quot]Val(DCountDistinct("[FONT=&quot]PATIENTID[/FONT]","[FONT=&quot]MULTI_CVC[/FONT]","[CVCSITE] Like 'Subclavian*' And [P[FONT=&quot]rocCount][FONT=&quot] >1[/FONT][/FONT]")) AS [FONT=&quot]SUBJ_MU[FONT=&quot]L[FONT=&quot]TI_SUB[FONT=&quot]CLV

[FONT=&quot]Also - you are correct[FONT=&quot] that I am not us[FONT=&quot]ing[FONT=&quot] any sort of definition such as a CPT code[FONT=&quot]. The field is [FONT=&quot][CVCSITE] with Ju[FONT=&quot]gular Vein[FONT=&quot], Femoral Vein, Subclavian Vein, etc. as options[/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT][/FONT]