-1

I'm a new analyst for a small collection of cardiology practices (to give some context). I am looking for a way to group combinations of CPT codes (IDs used for a specific procedures) and use them to count the number of tests (tests are often a combination of CPT codes) that occurred during a patients visit.

I have found some related material on multi value fields here, but am trying to understand if MS access is able to look at grouping (patient visit in this case) of CPT codes and determine if one or more tests occurred.

Here is simplified data:

ID; OfficeVisit; CPT; CPTdesc; PrcVolume; PrcType;

1; Alex, Doctor - 12345-15-09-2017 12:00:00; 93320; DOPPLER ECHO HEART,COMPLETE; 1; Echo;

2; Alex, Doctor - 12345-15-09-2017 12:00:00; 93325; DOPPLER COLOR FLOW VELOCITY MAP; 1; Echo;

3; Alex, Doctor - 12345-15-09-2017 12:00:00; 93351; ECHO HEART XTHORACIC, STRESS/REST, W CONTIN ECG; 1; Other;

4; Alex, Doctor - 12345-15-09-2017 12:00:00; 93880; DUPLEX SCAN EXTRACRANIAL,BILAT; 1; Echo;

5; Alex, Doctor - 12345-15-09-2017 12:00:00; A4550; SURGICAL TRAYS; 1; Other

Where CPT 93320, 93325, and 93880 = Test A and 93351 and A4550 = Test B, I would like the count for that visit of Alex Doctor's to = 2.

Is this even possible with MS Access? Can it pick out predefined groups of records in a field but ignore others?

I like to use the multi value field to sort through 100K records.

Any help would be greatly appreciated!!!

H_Pearson
  • 1
  • 1

1 Answers1

0

I made the following table as shown below. The reason is that I think multi-valued is not the way to go for speed and large amounts of data.

tblPatientVisits

  • ID, Office Visit, fpkCPTCode, PrcVolume, PrcType

tblCPTCode

  • apkCPTCode, fpkProcedureType, CPTCode, CPTDescription

tblProcedureType

  • apkProcedureType, ProcedureType

I then made a query qryOfficeVisits with the following code:

SELECT tblPatientVisit.ID, tblPatientVisit.OfficeVisit, tblCPTCodes.CPTCode, tblCPTCodes.CPTDescription, tblProcedureType.ProcedureType, tblPatientVisit.PrcVolume, tblPatientVisit.PrcType FROM tblPatientVisit INNER JOIN (tblCPTCodes INNER JOIN tblProcedureType ON tblCPTCodes.fpkProcedureType = tblProcedureType.apkProcedureType) ON tblPatientVisit.fpkCPTCode = tblCPTCodes.apkCPTCode ORDER BY tblPatientVisit.ID;

and then used that query to do another query for sums and groupings such as:

SELECT qryOfficeVisits.OfficeVisit, qryOfficeVisits.ProcedureType, Count(qryOfficeVisits.CPTCode) AS CountOfCPTCode FROM qryOfficeVisits GROUP BY qryOfficeVisits.OfficeVisit, qryOfficeVisits.ProcedureType ORDER BY qryOfficeVisits.ProcedureType;

which gives 3 for ECHO and 2 for OTHER.

Hope this helps

Community
  • 1
  • 1
Charles_D
  • 11
  • 4