Results 1 to 5 of 5
  1. #1
    sonntagc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    13

    How do i loop thru multiple fields with similar names in a single table to determine if not empty?

    i am trying to process raw, csv data i imported into a table. The data contains medical info, a list of 1 to 12 diagnosis codes, and a list of 1 to 12 procedure codes. There always must be a primary diagnosis and procedure, so those fields are named PriDiag and PriProc. the other field lists respectively have the same base field name, with a numeric suffix. So additional diagnoses are named ihdgc2 thru ihdgc12 and additional procedures are named ihpdi2 thru ihpdi12. I wand to loop thru all of those fields and count fields with values so that i can update the record with counters of the total diagnoses and total procedures on a claim. I need to test each field name to see if it is null/empty. If not empty, append the code values together, so they can be displayed as a single field as a list on a form, and in excel. i had trouble getting the code to work correctly using variable strings, so i went with the code below. Horrible i know, but it allowed me to get the data in quick. now i'm trying to optimize the code.

    Also, because this file is passed from an outside source to me, sometimes, the data "skips" some fields, which doesn't always make sense to me, but it's what i get. For example, i will get the primary diagnosis code. then there will be values in the fields for diag2, diag3, diag4 and diag5 will be empty, then a value in diag6 and diag7.

    Code:
    Private Sub UpdtQLCilDataCombined()
        Dim myDB As Database
        Dim myRS As Recordset
        Dim strSQL As String
        
        Dim strDiagFldNm As String
        Dim strFld As String
        Dim intDiagCnt As Integer
        Dim intDiagProcCnt As Integer
        Dim strDiags As String
        
        Dim lngRecCnt As Long
        
        strDiagFldNm = "ihdgc"      'base field name is ihdgcd; for primary diag;  rest are labeled 2 thru 12
        intDiagCnt = 1
        
        
        strSQL = "SELECT * FROM QL_CIL_Data_Combined"
        Set myRS = CurrentDb.OpenRecordset(strSQL)
        
        Me.txtTeamLog = ""
        Me.txtRecCnt = 0
        lngRecCnt = 0
        
        With myRS
            
            If Not (.EOF And .BOF) Then
                .MoveFirst
                Do While Not .EOF
                    intDiagCnt = 1
                    intDiagProcCnt = 0
                    strDiags = Trim(![ihdgcd])
                    
                    .Edit
                    
                    lngRecCnt = lngRecCnt + 1
                    Me.txtTeamLog = ![SystemID]
                    
                    If (Nz(![ihdgc2]) <> "") And IsNumeric(![ihdgc2]) Then
                        intDiagCnt = intDiagCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihdgc2])
                    End If
                    If (Nz(![ihdgc3]) <> "") And IsNumeric(![ihdgc3]) Then
                        intDiagCnt = intDiagCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihdgc3])
                    End If
                    If (Nz(![ihdgc4]) <> "") And IsNumeric(![ihdgc4]) Then
                        intDiagCnt = intDiagCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihdgc4])
                    End If
                    If (Nz(![ihdgc5]) <> "") And IsNumeric(![ihdgc5]) Then
                        intDiagCnt = intDiagCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihdgc5])
                    End If
                    If (Nz(![ihdgc6]) <> "") And IsNumeric(![ihdgc6]) Then
                        intDiagCnt = intDiagCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihdgc6])
                    End If
                    If (Nz(![ihdgc7]) <> "") And IsNumeric(![ihdgc7]) Then
                        intDiagCnt = intDiagCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihdgc7])
                    End If
                    If (Nz(![ihdgc8]) <> "") And IsNumeric(![ihdgc8]) Then
                        intDiagCnt = intDiagCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihdgc8])
                    End If
                    If (Nz(![ihdgc9]) <> "") And IsNumeric(![ihdgc9]) Then
                        intDiagCnt = intDiagCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihdgc9])
                    End If
                    If (Nz(![ihdgc10]) <> "") And IsNumeric(![ihdgc10]) Then
                        intDiagCnt = intDiagCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihdgc10])
                    End If
                    If (Nz(![ihdgc11]) <> "") And IsNumeric(![ihdgc11]) Then
                        intDiagCnt = intDiagCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihdgc11])
                    End If
                    If (Nz(![ihdgc12]) <> "") And IsNumeric(![ihdgc12]) Then
                        intDiagCnt = intDiagCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihdgc12])
                    End If
                    ![Diags_Sub] = strDiags
                    
                    
                    If (Nz(![ihpdi2]) <> "") And IsNumeric(![ihpdi2]) Then
                        intDiagProcCnt = intDiagProcCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihpdi2])
                    End If
                    If (Nz(![ihpdi3]) <> "") And IsNumeric(![ihpdi3]) Then
                        intDiagProcCnt = intDiagProcCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihpdi3])
                    End If
                    If (Nz(![ihpdi4]) <> "") And IsNumeric(![ihpdi4]) Then
                        intDiagProcCnt = intDiagProcCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihpdi4])
                    End If
                    If (Nz(![ihpdi5]) <> "") And IsNumeric(![ihpdi5]) Then
                        intDiagProcCnt = intDiagProcCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihpdi5])
                    End If
                    If (Nz(![ihpdi6]) <> "") And IsNumeric(![ihpdi6]) Then
                        intDiagProcCnt = intDiagProcCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihpdi6])
                    End If
                    If (Nz(![ihpdi7]) <> "") And IsNumeric(![ihpdi7]) Then
                        intDiagProcCnt = intDiagProcCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihpdi7])
                    End If
                    If (Nz(![ihpdi8]) <> "") And IsNumeric(![ihpdi8]) Then
                        intDiagProcCnt = intDiagProcCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihpdi8])
                    End If
                    If (Nz(![ihpdi9]) <> "") And IsNumeric(![ihpdi9]) Then
                        intDiagProcCnt = intDiagProcCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihpdi9])
                    End If
                    If (Nz(![ihpdi10]) <> "") And IsNumeric(![ihpdi10]) Then
                        intDiagProcCnt = intDiagProcCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihpdi10])
                    End If
                    If (Nz(![ihpdi11]) <> "") And IsNumeric(![ihpdi11]) Then
                        intDiagProcCnt = intDiagProcCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihpdi11])
                    End If
                    If (Nz(![ihpdi12]) <> "") And IsNumeric(![ihpdi12]) Then
                        intDiagProcCnt = intDiagProcCnt + 1
                        strDiags = strDiags & STRDELIM & Trim(![ihpdi12])
                    End If
                    
                    ![Diags_Proc] = strDiags
                    ![ClmCnt_Diags_Sub] = intDiagCnt
                    ![ClmCnt_Diags_Proc] = intDiagProcCnt
                    
                    .Update
                    
                    'For ix = 2 To 12
                    '    strFld = strDiagFldNm & ix
                    '    intDiagCnt = intDiagCnt + 1
                    '    If Nz(myRS!Fields!strFld, "") <> "" Then
                    '        Me.txtTeamLog = 8
                    '    Else
                    '        Me.txtTeamLog = 999
                    '    End If
                    '    'Me.txtTeamLog = Me.txtTeamLog & vbCrLf & strFld
                    'Next ix
                    
                    Me.txtRecCnt = lngRecCnt
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        myRS.close
    
    
    End Sub
    thanks for any help in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you shouldn't.
    you don't scan across fields on a record, you scan down the column for these items. Normalized data.
    (your table may be designed wrong)

  3. #3
    sonntagc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    13

    Thanks for reply, but that is not how i receive the data.

    Quote Originally Posted by ranman256 View Post
    you shouldn't.
    you don't scan across fields on a record, you scan down the column for these items. Normalized data.
    (your table may be designed wrong)
    I understand normalized tables, but that is NOT how the data is being provided to me. i am receiving a txt/csv file, where each row of data represents a single medical claim. so within each ROW, there are 12 COLUMNS for diagnosis codes and 12 COLUMNS for procedure codes. Claim #1 may have 1 diagnosis and 1 procedure. Claim #2 might have 5 diagnoses and 3 procedures, etc., etc. etc. So i am trying to count the "non-empty" or "non-null" fields per ROW. That way i can set a total diagnosis count field and total procedure count field for each claim (i.e., row). The total counts are then used individually and in combination for analysis for possible fraud and/or other claim anomalies.

    below are the column names received in the txt/csv file. i cannot provide data because of privacy and HIPAA. the file is either comma or tab delimited. it varies sometimes depending on who the client is.

    FIELD NAME MEANING/USE
    PatID patient ID
    PatName patient name
    SvcDt date of service
    FacName place of service (name)
    PrvName provider/doctor name
    TotChg total charge
    DscAmt discount amount
    Copay copay amount
    TotPd total paid
    PdDt date paid
    RcvDt date received
    PrcDt date processed
    PrimDgc primary diagnosis
    ihdgc2 diagnosis #2
    ihdgc3 diagnosis #3
    ihdgc4 diagnosis #4
    ihdgc5 diagnosis #5
    ihdgc6 diagnosis #6
    ihdgc7 diagnosis #7
    ihdgc8 diagnosis #8
    ihdgc9 diagnosis #9
    ihdgc10 diagnosis #10
    ihdgc11 diagnosis #11
    ihdgc12 diagnosis #12
    PrimPdi Primary procedure
    ihpdi2 procedure #2
    ihpdi3 procedure #3
    ihpdi4 procedure #4
    ihpdi5 procedure #5
    ihpdi6 procedure #6
    ihpdi7 procedure #7
    ihpdi8 procedure #8
    ihpdi9 procedure #9
    ihpdi10 procedure #10
    ihpdi11 procedure #11
    ihpdi12 procedure #12

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It would have helped is there were examples of data. Maybe changed the sensitive data like PatName to "Donald Duck" , PatID to "ABCD", FacName to "BigVet" and PrvName to "Dr Who".

    Anyway, you seem to know what you are doing in VBA, so try the following code. You will have to some editing - I didn't know where/what "STRDELIM" is and the same with [ihdgcd] & [SystemID]. but the looping is (seems to be) there.

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub UpdtQLCilDataCombined_click()
        Dim myRS As DAO.Recordset
        Dim strSQL As String
    
        'for diagnosis codes
        Dim strDiagFld As String
        Dim strDiagsSub As String
        Dim intDiagCnt As Integer
    
        'for procedure codes
        Dim strProcFld As String
        Dim strDiagsProc As String
        Dim intDiagProcCnt As Integer
    
        'misc variables
        Dim lngRecCnt As Long
        Dim ix As Long
    
        'initalize variables
        intDiagCnt = 1
    
        Me.txtTeamLog = ""
        Me.txtRecCnt = 0
        lngRecCnt = 0
    
    
        'recordset stuff
        strSQL = "SELECT * FROM QL_CIL_Data_Combined"
        Set myRS = CurrentDb.OpenRecordset(strSQL)
    
        With myRS
    
            If Not (.EOF And .BOF) Then
                .MoveLast
                .MoveFirst
    
                Do While Not .EOF
                    intDiagCnt = 1
                    intDiagProcCnt = 0
                    strDiags = Trim(![ihdgcd])
    
                    lngRecCnt = lngRecCnt + 1
                    Me.txtTeamLog = ![SystemID]
    
                    For ix = 2 To 12
                        strDiagFld = "ihdgc" & ix
                        strProcFld = "ihpdi" & ix
                        intDiagCnt = intDiagCnt + 1
    
                        'diagnosis codes
                        If (Nz(.Fields(strDiagFld), "") <> "") And IsNumeric(.Fields(strDiagFld)) Then
                            intDiagCnt = intDiagCnt + 1
                            strDiagsSub = strDiagsSub & STRDELIM & Trim(.Fields(strDiagFld))
                        End If
    
                        'procedure codes
                        If (Nz(.Fields(strProcFld), "") <> "") And IsNumeric(.Fields(strProcFld)) Then
                            intDiagProcCnt = intDiagProcCnt + 1
                            strDiagsProc = strDiagsProc & STRDELIM & Trim(.Fields(strProcFld))
                        End If
    
                        '  :confused::confused::confused:
                        '    If Nz(myRS!Fields!strFld, "") <> "" Then
                        '        Me.txtTeamLog = 8
                        '    Else
                        '        Me.txtTeamLog = 999
                        '    End If
                        '    'Me.txtTeamLog = Me.txtTeamLog & vbCrLf & strFld
                    Next ix
    
    
                    'save values
                    .Edit
    
                    ![Diags_Sub] = strDiagsSub
                    ![ClmCnt_Diags_Sub] = intDiagCnt
    
                    ![Diags_Proc] = strDiagsProc
                    ![ClmCnt_Diags_Proc] = intDiagProcCnt
    
                    .Update
    
                    Me.txtRecCnt = lngRecCnt
                    DoEvents
                    .MoveNext
                Loop
            End If
        End With
        myRS.Close
        Set myRS = Nothing
    
    End Sub

    Good luck with your project.....

  5. #5
    sonntagc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    13
    Quote Originally Posted by ssanfu View Post
    It would have helped is there were examples of data. Maybe changed the sensitive data like PatName to "Donald Duck" , PatID to "ABCD", FacName to "BigVet" and PrvName to "Dr Who".

    Anyway, you seem to know what you are doing in VBA, so try the following code. You will have to some editing - I didn't know where/what "STRDELIM" is and the same with [ihdgcd] & [SystemID]. but the looping is (seems to be) there.

    Good luck with your project.....
    Hey there. i appreciate the feedback and the code tweak. i haven't tried it yet, but i'm pretty sure that's what i'm looking for. i'll let you know the results.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Assign multiple names to a single record
    By JGrots in forum Access
    Replies: 1
    Last Post: 07-18-2013, 02:28 PM
  2. Can you place Multiple Field Names to a Single Index?
    By VanillaAwesome in forum Access
    Replies: 2
    Last Post: 08-05-2012, 04:40 PM
  3. Replies: 1
    Last Post: 08-30-2011, 07:35 AM
  4. Loop through a list of table names and compare
    By mikneus in forum Programming
    Replies: 1
    Last Post: 05-21-2010, 10:36 AM
  5. Replies: 1
    Last Post: 07-07-2009, 01:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums