Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    groupings logic needed (A2k3, SP1)

    Hi All,
    This is part of a set of billing calculations.
    The breakdown is as follows:
    I have clients who are part of a Case Number. There are a minimum of 3 clients associated with each Case Number. Each client has a designator assigned to them: i.e. mother, Jane Doe, would be "M"; child1, Mary Doe, would be CH1; Child2, Jack Doe, would be CH2; man1, John Doe, would be TM1; man2, Joe Doe, would be TM2; and so on. If there are more than 3 clients then the Case Number is broken down into sub Case Numbers. The clients have a certain billing fee which needs to be billed only once. My problem is limiting that charge to 1 time no matter how many times the client appears in the groupings.
    Examples (see attached .txt file)

    Once I know how to limit each client to be charged only once I think I can work out the rest of the Billing calculations.
    Thank you.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: groupings logic needed (A2k3, SP1)

    I'd say: don't use the groupings for billing, but that is probably naive...

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: groupings logic needed (A2k3, SP1)

    Hi Hans,
    Thanks for the response. Just before I fell asleep last night I had an idea that may work. Now that I'm able to have the time to see if it works I'll flesh it out and run it by you and see how my "logic" holds up. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: groupings logic needed (A2k3, SP1)

    Hi Hans,
    Well I think I've finally bulldozed through it. I broke the 1 larger query (Query2 in the previously attached text file) into an sql statement on a table and then an sql statement on a smaller version of that query. I was able to get more granular control over which CollectionFee I was trying to grab. Here's what I did.......
    <pre>strCaseRecord = "SELECT [DataDetailsPKID], [CaseNumberPKID], [GrpDesignators] "
    strCaseRecord = strCaseRecord & "FROM tblCaseNumberDataDetails "
    strCaseRecord = strCaseRecord & "WHERE [CaseNumberPKID] = " & intCaseNumberPKID

    'Begin rstCaseRecord loop
    'bunch of other stuff

    strDesignators = rstCaseRecord!GrpDesignators
    'ensure there have been groupings made
    If Len(strDesignators & vbNullString) > 0 Then
    'Get rid of spaces:
    strDesignators = Replace(strDesignators, Chr$(32), vbNullString, , , vbBinaryCompare)
    'Get array from comma-separated list:
    tmp = Split(strDesignators, ",")
    'Get count of items in list:
    lCount = UBound(tmp) + 1

    For n = 0 To lCount - 1
    'set strTmp to current designator being used
    strTmp = tmp(n)
    'if current designator has not been used (compare to processed designator list)
    'then use it, else skip it
    If InStr(strCompDesig, strTmp) = 0 Then
    strFee = "SELECT [SampleFeeCharged] "
    strFee = strFee & "FROM [qrySampleFee] "
    strFee = strFee & "WHERE DataDetailsPKID = " & rstCaseRecord!DataDetailsPKID
    strFee = strFee & " AND [BillToRequestingAgency] = -1"
    strFee = strFee & " AND PersonDesignator = '" & strTmp & "'"
    Set rstFee = db.OpenRecordset(strFee)
    'if there is a recordset, then there is an entry for the CollectionFee
    'so add it to the others for this sub Case Number...else bypass it
    If rstFee .Recordcount <> 0 Then
    dblSampleFee = dblSampleFee + Nz(rstFee!SampleFeeCharged, 0)
    Else
    'do nothing as there is not fee associated with this designator
    End If
    rstFee.Close
    Set rstFee = Nothing
    Else
    'there has already been a fee associated with this designator
    'so we just skip it on any subsequent iteration
    End If
    'Add latest designator to list that has already been processed
    strCompDesig = strCompDesig & Chr$(34) & strTmp & Chr$(34) & ","
    Next n
    'Clean up processed designator list
    strCompDesig = Left$(strCompDesig, Len(strCompDesig) - 2)
    strCompDesig = Right$(strCompDesig, Len(strCompDesig) - 1)
    Else
    MsgBox "No Client Groupings have been made.", vbOKOnly, "No Client Groupings"
    End If
    dblSampleFee = 0

    'bunch of other stuff
    'End rstCaseRecord Loop
    </pre>

    Do you think this will hold up, given the requirements in my first post?
    Thank you for your help.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •