return code with maximum RVU

  • Thread starter Thread starter CuriousMark
  • Start date Start date
C

CuriousMark

I have a database that records encounters with patients, showing the
procedures done (CPT codes). Each CPT is associated with a value (RVU) in a
separate table. I want to extract the CPT for each encounter that has the
highest RVU. This database is protected within an HIPAA compliant network.
There are two tables involved:

tblEncCPT
EncCPTID - primary key
EncID - foreign key from tblEncounter
CPT - foreign key from tblRVU

tblRVU
CPT - primary key
WorkRVU - number

I can construct a query that gives me this:

EncID CPT RVU
4 32663 24.56
4 38746 4.88
4 31622 2.78
4 64421 1.68
6 32651 18.7
6 31622 2.78 ...

SELECT tblEncCPT.EncID, tblEncCPT.CPT, tblRVU.RVU
FROM tblRVU INNER JOIN tblEncCPT ON tblRVU.CPT=tblEncCPT.CPT
ORDER BY tblEncCPT.EncID, tblRVU.RVU DESC;

But I can't figure out how to get to this:

EncID CPT
4 32663
6 32651 ...

Thanks for your help.
 
You can try one of the four methods presented at
http://www.mvps.org/access/queries/qry0020.htm (which are about returning
the latest borrower of each book, in a library).
As example, you can make a total query :

SELECT cpt, MAX(RVU) AS mrvu
FROM x
GROUP BY cpt


to get the maximum RVU value for each cpt, and, to get other related fields,
make another query:


SELECT *
FROM previousQuery INNER JOIN x
ON previousQuery.cpt=x.cpt AND previousQuery.mrvu=x.rvu



I am not sure to have the table name relevant to the information we seek,
though, so I have used "x".


Vanderghast, Access MVP
 
thanks. will give it a try. But not sure....each CPT has one RVU, there are
not several RVUs for each CPT. So the trick is to return the CPT that has the
highest RVU, without returning the RVU, and several CPTs can have the same
RVU, so with the RVU you can't track back to the CPT.
 
Back
Top