• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2924
  • Last Modified:

I need access to automatically add a modifier to a cpt code listed in an excel spreadsheet if a certain cpt code is entered on the same line

Hi,

I need Access to automatically add a modifier to a cpt code listed in an excel spreadsheet if a certain cpt code is entered on the same line.

The spreadsheet will be imported into Access and if it meets the criteria listed below, a modifier will need to be added to the code in column 1

      
Column 1      Column 2
97001            97002, 97703, 97750, 97112
97018            97012, 97016, 97022, 97035
97022            97035

            
If a code from column 1 is listed on the same line with any of the codes from column 2, a modifier  of "59" needs to be attached to the cpt code in column 1.

Is there a way for me to code this in Access to capture the rows that have this information and some how flag them that they meet this criteria so the modifier of 59 can be added to the cpt code in column 1.  

Here is a sample of the excel spreadsheet that will be imported:  

Name             CPT 1      CPT 2      CPT 3
Test, Patient      97001      97002      
Smith, Jane      97018      97012      

Both these rows would qualify based on the column 1 and 2 info above, so I would like to somehow identify these rows based on the coding requirements above and either add the 59 automatically to the cpt code listed in the column 1 data.  (97001 should qualify to have a 59 attached to it because it's billed along with a 97002, also 97018 should qualify for the modifier because it's being billed on the same line with 97012 which appears on the list above)

Ideally, my output would look like this:

Name             CPT 1            CPT 2      CPT 3
Test, Patient      9700159            97002      
Smith, Jane      9701859            97012      

I'm not sure if this is even possible, but any help you can provide would be greatly appreciated.

Thanks!
Christine
0
moorecm
Asked:
moorecm
  • 11
  • 3
1 Solution
 
ssilvi84Commented:
Christine,

Well it certainly is possible.

How are you going to set yup your columns?

Your first example shows that column 2 has a whole lot of data

Your second example shows only 1 item in every column.

Clarify please, but yes that can be done.
0
 
moorecmAuthor Commented:
Hi Again,

My columns will be set up the same as all the columns in the spreadsheet that will be uploaded into Access.  The columns that will be imported from the spreadsheet are:  charge slip number, case type, patient acct #, patient name, Diagnosis Code, CPT1, CPT2, CPT3, CPT4, CPT5, Total Charge, Total Units

I didn't give you the full list - just enough to give you an example.  Here is the full list

Column 1      Column 2
97001            97002, 97004, 97703, 97750, 97112
97003            97002, 97004, 97703, 97750, 97112
97002            All 97000 series (any code that begins with 97xxx)
97004            All 97000 series (any code that begins with 97xxx)
97018            97012, 97016, 97022, 97035
97022            97035
97110            97113
97140            97012, 97124, 97750, 97530
97504            97110, 97112, 97116, 97124, 97140, 97703
97520            97110, 97112, 97116, 97124, 97140, 97703
97530            97113, 97116, 97542, 97750

If you use the code listed in column 1 with any of the codes in column 2, the modifier needs to be attached to the code that is in column 1


The spreadsheet will contain upto 5 CPT codes on any given row for a patient.  Any combination of those codes that match the above column 1 and column 2 data will need to have that modifier "59" added to the CPT code that is displayed in Column 1.  (i.e., I see the doctor, and my row in the spreadsheet list the following CPT Codes:  97002, 97703, 97001 - because 97001 is one of the codes in column 1 that will need a modifier if it's billed with any of those codes from column 2)

I'm not sure if I'm explaining myself cleary enough.  

Hope this helps...

Thanks,
:-)
Christine
0
 
ssilvi84Commented:
Christine~

In the interim, I created a short example that produces this:

Name                      CPT1      CPT2      CPT3      CPT4
John Smith                      97001      97047      97048      97049
Sally Stevens      9700359      97002      97003      97004
Jane Anderson      9700359      97088      97034      97003
Amanda Johnson      97977      97097      97099      97101
Bob Jones                      9700359      74420      74433      74403

You will notice that only Sally, Jane, and Bob have the 59 appended, based on what the CPT was.  I will need to modify this based on your request.  
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ssilvi84Commented:
Whoops, ran my update in the wrong spot.  My code now produces the desired 59 appendage (rather then just 9700359 in all 3 spots!)... I will change it to what you need and get back to you shortly
0
 
ssilvi84Commented:
Name      CPT1      CPT2      CPT3      CPT4
John Smith              97001      97047      97048      97049
Sally Stevens      9700359      97002      97003      97004
Jane Anderson      9703459      97088      97034      97065
Amanda Johnson      97977      97097      97099      97101
Bob Jones           7443359      74420      74433      74403

There is the updated result, in case you wanted to see it
0
 
moorecmAuthor Commented:
That's wonderful.  How do I code that in my Access DB to get that to work?  Also, I'm new to your site and I must tell you I absolutely love you guys.  Should I be selecting "Accept" next to each of your comments?  I want to make sure I'm following your policy correctly.

Thanks!
0
 
ssilvi84Commented:
Christine,

I also am relatively new to the site.  I've been posting here for over a year now, but I'm just starting to answer questions (last month was my first month I believe).  It is a fantastic site and has solved many a problem for me.  As far as selecting "Accept" .. only select one comment to accept, once someone ahs given you a solution.  For example... I may respond to you 5 more times before you truly get this working like you would like it to.  Once that happens, you select "accept" next to the comment that gave you your solution.  Then you award me a grade based on how well I did.  

Here is the code I used to return those values to that table.  The table though, has CPT1, CPT2, CPT3, CPT4 fields.  I am currently working on parsing data out of your 2nd column (97332, 97223, 91734, 90071, 97771) so that I can compare code to each one individually.  For whatever reason i'm running into a minor error, and I should have your complete solution presently.  

Private Sub cmdAppend_Click()

Dim strQry As String
Dim strMark As String
Dim strChange As String

Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
Dim rsresult As ADODB.Recordset

Set cnn = CurrentProject.Connection

rst.Open "tbl_CPT", cnn
If rst.EOF Then
    Exit Sub
Else
    'Do Until rst.EOF
     '   Debug.Print rst!CPT1
       
        strQry = "SELECT tbl_CPT.Name, tbl_CPT.CPT1, tbl_CPT.CPT2 FROM tbl_CPT WHERE (tbl_CPT.CPT1=tbl_CPT.CPT2);"
        Set rsresult = CurrentProject.Connection.Execute(strQry)
           
        Do Until rsresult.EOF
            strMark = rsresult!CPT1
            strChange = strMark & "59"
            strQry = "UPDATE tbl_CPT SET CPT1 = '" & strChange & "' WHERE CPT1 = '" & strMark & "'"
       
            CurrentProject.Connection.Execute (strQry)
            rsresult.MoveNext
        Loop
    'Loop
End If

End Sub

Like I said, I should have your solution shortly!
0
 
ssilvi84Commented:
Name      CPT1      CPT2
John Smith      97001      97047,97048,97049
Sally Stevens      9700359      97002,97003,97004
Jane Anderson      9703459      97088,97034,97065
Amanda Johnson      97977      97097,97099,97101
Bob Jones      7443359      74420,74433,74403

That about what you were looking for?
0
 
ssilvi84Commented:
I had 1 table with 3 columns: Name, CPT1 and CPT2 (all datatype: Text)

I created a form with a command button on the form.  In the On_Click event of the button, I added the following code:


Private Sub cmdAppend_Click()

Dim i, maxIndex As Integer
Dim strQry, strMark, strChange, scPT() As String

Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
Dim rsresult As ADODB.Recordset

Set cnn = CurrentProject.Connection

rst.Open "tbl_CPT", cnn

strQry = "SELECT * FROM tbl_CPT"
Set rsresult = CurrentProject.Connection.Execute(strQry)

If rst.EOF Then
    Exit Sub
Else
    Do Until rsresult.EOF
        strMark = rsresult!CPT2
        scPT = Split(strMark, ",")
        maxIndex = UBound(scPT)
       
        For i = 0 To maxIndex
            If rsresult!CPT1 = scPT(i) Then
                strChange = scPT(i) & "59"
                strQry = "UPDATE tbl_CPT SET CPT1 = '" & strChange & "' WHERE CPT1 = '" & scPT(i) & "'"
                CurrentProject.Connection.Execute (strQry)
            End If
        Next i
               
        rsresult.MoveNext
    Loop
End If

End sub

Now, all of this could be done in another event of another control, whatever you need to spec for your project.
0
 
ssilvi84Commented:
Sorry didn't mean to click submit.

Here is my table prior to clicking the button:

Name      CPT1      CPT2
John Smith      97001      97047,97048,97049,48333
Sally Stevens      97003      97002,97003,97004,32222
Jane Anderson      97034      97088,97034,97065,90000
Amanda Johnson      97977      97097,97099,97101,00000,33222
Bob Jones      74433      74420,74433,74403

Here is my code when it has been clicked:

Name      CPT1      CPT2
John Smith      97001      97047,97048,97049,48333
Sally Stevens      9700359      97002,97003,97004,32222
Jane Anderson      9703459      97088,97034,97065,90000
Amanda Johnson      97977      97097,97099,97101,00000,33222
Bob Jones      7443359      74420,74433,74403

I believe that is exactly what you asked for!
0
 
ssilvi84Commented:
Christina,

I'm going to the gym now, will be back in a couple hours... let me know how things went/are going.
0
 
ssilvi84Commented:
okay I'm back...

Any developments?
0
 
moorecmAuthor Commented:
Hi,

That looks perfect!  Thank you so much for the help.  Can I just mimic the code you listed above to get this to work for me? I'll open VB through Access and paste your code (if you think that will work)???

Thanks,
Christine
0
 
ssilvi84Commented:
you may have to modify the code to fit your tables/buttons/field names, etc.  However if you create a form called form1, a table called tbl_CPT with field names "Name" "CPT1" and "CPT2" where "CPT2" holds the multiple, comma seperated values (no space after the comma in my program) then it will work.

To let you know about proper etiquette here on EE... It's not considered proper to ask multiple questions in a thread.  The help files talk more about that, under "Big Mistakes."  In this situation, since your question was 100% answered, accepting the comment with the solution would be the proper thing to do.  If you were to need help modifying it to fit your project, you could start another question.  That being said, it shouldn't be too difficult for you to modify my code to fit your prog.  You simply need to do some basic field mapping to determine the names of the fields you are using, as well as putting the code in the correct location in your code.

You're quite welcome, I was glad to do it.  This is a learning experience for both of us =)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 11
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now