looping through records to determine a total

[dixie617]

I have this report that is run from a cmd button. The click event contains code that is suppose to determine whether or not the sentence is concurrent or consecutive. If concurrent sets the TotalYears to the largest number of years. If there are also consecutive sentences it needs to the years to the largest concurrent sentence. It works fine for concurrent sentences but not for consecutive. Any help would be greately appreciated. Here is the portion of the code that is suppose to do the calculation:

Private Sub cmdUCD_Click()

Dim TotalMonths As Long
Dim SubTotal As Long
Dim TotalDays As Long
Dim Sent As Long
Dim rstDefCharge As Recordset
Dim rstConCons As Recordset
Dim db As DAO.Database
Dim TotalYears As Integer

Set db = DBEngine(0)(0)
Set rstConCons = db.OpenRecordset("Select * from tblConcurentConsecutiveTable where PrimaryCaseNo = '" & Replace(Me!CaseNo, "'", "''") & "' and ((DefendantId) = " & Str(Me!DefendantId) & ")", dbOpenSnapshot)
Set rstDefCharge = db.OpenRecordset("Select * from tblDefChargesSentence where CaseNo = '" & Replace(Me!CaseNo, "'", "''") & "' and ((DefendantId) = " & Str(Me!DefendantId) & ")", dbOpenSnapshot)

SubTotal = 0
With rstConCons
Do Until .EOF

If rstConCons![ConConsecCaseNo] = rstDefCharge![CaseNo] Then
If rstDefCharge!ConcurrentSentence = True Then

TotalMonths = rstDefCharge!SentYrs * 12
TotalDays = rstDefCharge!SentDays / 30
If TotalMonths > rstDefCharge!SentMos Then
If TotalMonths > TotalDays Then

Sent = TotalMonths / 12
If Sent > SubTotal Then
SubTotal = Sent
End If
End If
End If
End If
End If

.MoveNext
Loop

Do Until .EOF

If rstConCons!ConConsecCaseNo = rstDefCharge!CaseNo Then
If rstDefCharge!ConsecutiveSentence = True Then

SubTotal = rstDefCharge!SentYrs + SubTotal

End If
End If

.MoveNext
Loop
End With
Me!TotalYears = SubTotal

 

[dixie617]

Forgot to tell you, I tried to put the consecutive question in with the original loop, then it would not work at all. Any help would be greatly appreciated.

 

[markphsd]

I think this question hasn't been answered because the logic is difficult to understand. Could you please further explain what it is you are trying accomplish.

What i understand is this concurrent years of a sentance would be years sentanced and being served at the same time, consectutive would be years servered one group after the next.

So, in one instance, concurrent, someone is charged with 25 years and 15 years. Their totals years to be served is 25.

Next instance would be Consectutive. 25 years and a 15 year sentance would eqaual 40 years.




Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[TheAceMan1]

How are ya dixie617 . . . . .

[blue]The click event contains code that is suppose to determine whether or not the [purple]sentence is concurrent or consecutive.[/purple][/blue]

Be more specific about this . . .

See Ya! . . . . . .

 

[markphsd]

I think this field determines whether it's concurrent or consecutive.


rstDefCharge!ConcurrentSentence = True

that line was out of the code above.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[dixie617]

Mark,

You have the right idea, if concurrent is true then 25 years, if consecutive 40 years. I got some of it working and added a move.first prior to the second do until statement. Now it gives double the sentence.

 

[markphsd]

Is that a good thing?

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[dixie617]

Sorry it took me so long to respond, I am on vacation in New York this week. No not a good thing, What I need it to do is loop through all the records pertaining to the sentence on one case, suppose a Case No 222345 has 10 charges and each charge has a sentence of 3 years each for charges 1 - 3 and 4-5 is 6 years which are running concurrrently to each other so the subtotal would be 6 years (which is the highest sentence for the 5 charges) and charges 6-10 have a sentence of 5 years each and are consecutive to 1-5, which means once the 6 year sentence is complete then he has to serve 5, 5, 5, 5, 5 which is a total of 25 additional years to serve, the results for TotalYears needs to be 31 years. so you see I need to loop and find the concurrent sentences and set the subtotal to the highest number of years, then loop again and find the consecutive years which needs to be added to the subtotal.

Hope this clears it up a bit

Thanks for all the help

 

[markphsd]

Don't loop the concurrent sentences, filter out for concurrent sentences within the Case. Then get the Max Count.

select max(tblSetences.Sentence), tblSentences.IsConcurrent, tblsetntences.CaseNo WHERE(((CaseNo)=222345) AND ((IsConcurrent)=true))

For the consecutive sentences, you can either use a crosstab query or a loop.




Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.