groupings logic needed (A2k3, SP1)

[gdrezek]

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

[HansV]

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

[gdrezek]

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

[gdrezek]

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