Algorithm problem--Help

[Alan]

Try the following for starters. It's untested but should give you the idea.
It assumes that the records you want to group are already sorted by name -
i.e. all those starting with the same first letter are together.

Let us know how you go.

Alan


Dim Total
Dim Count
Dim CurrentLetter
Dim LastLetter
Dim Name
Dim Owing
Dim RS

CurrentLetter = ""
LastLetter = ""
Total = 0
Count = 0

' Get your RS here.
Set RS=GetValues()

Do While Not RS.EOF

Name = RS.Fields("Name").Value
Owing = RS.Fields("Owing").Value
CurrentLetter = Left (Name, 1)

If (CurrentLetter <> LastLetter) Then

If (LastLetter <> "") Then

' Display totals for the previous group.
Average = Total/Count
Response.write Total & "<br>" & Average

End If

' Initialise the new group.
Total = 0
Count = 0
LastLetter = CurrentLetter

End If

Total = Total + Owing
Count = Count + 1

RS.MoveNext

Loop



"ibsimneej" <ibsimneej@hotmail.com> wrote in message
news:f4e17b91.0308151334.5563f727@posting.google.c om...

> Hi I was wondering if you all Guru's can help me out. I ran into an
> algorithm problem that looks simple but I just cant figure it out.
>
> I have a recordset in ASP from a SQL quer at which I want to subtotal
> some columns as I run down the recordset and I need an algorithm to do
> it. The recordset is something like this:
>
> Name id $Owe
> John JN 5
> Judy JN 20
> Kenny KN 30
> Kevin KY 40
> Lon LO 10
> Lony LO 15
> Thomas TO 12
> Tom TO 3
> Tony TO 45
> Uel UE 34
>
> The recordset is already sorted by the id and or Name.
> now here is what the result should look like:
>
> Name id Owe
> John JN 5
> Judy JN 20
> total = 25 Average = 12.5
>
> Kenny KN 30
> Kevein KY 40
> total = 70 Average = 35
>
> Lon LO 10
> Lony LO 15
> total = 25 Average = 12.5
>
> Thomas TO 12
> Tom TO 3
> Tony TO 45
> total = 60 Average = 20
> Uel UE 34
> Total = 34 Average = 34
>
> It looks simple, but I just can't figure it out. Any help would be
> appreciated.
>
> Thanks,
> IB

[Bob Barrows]

You should let the database do this work for you:

Select [name],id,[Owe$],0 AvgOwed,1 SortBy
FROM table
UNION ALL
Select 'Subtotal',id,sum([$Owe]),avg([$Owe]),2
FROM table
Group By id
UNION ALL
Select 'Total','ZZ',sum([$Owe]),avg([$Owe]),3
FROM table
Order By id,SortBy,[name]


HTH,
Bob Barrows

"ibsimneej" <ibsimneej@hotmail.com> wrote in message
news:f4e17b91.0308151334.5563f727@posting.google.c om...

> Hi I was wondering if you all Guru's can help me out. I ran into an
> algorithm problem that looks simple but I just cant figure it out.
>
> I have a recordset in ASP from a SQL quer at which I want to subtotal
> some columns as I run down the recordset and I need an algorithm to do
> it. The recordset is something like this:
>
> Name id $Owe
> John JN 5
> Judy JN 20
> Kenny KN 30
> Kevin KY 40
> Lon LO 10
> Lony LO 15
> Thomas TO 12
> Tom TO 3
> Tony TO 45
> Uel UE 34
>
> The recordset is already sorted by the id and or Name.
> now here is what the result should look like:
>
> Name id Owe
> John JN 5
> Judy JN 20
> total = 25 Average = 12.5
>
> Kenny KN 30
> Kevein KY 40
> total = 70 Average = 35
>
> Lon LO 10
> Lony LO 15
> total = 25 Average = 12.5
>
> Thomas TO 12
> Tom TO 3
> Tony TO 45
> total = 60 Average = 20
> Uel UE 34
> Total = 34 Average = 34
>
> It looks simple, but I just can't figure it out. Any help would be
> appreciated.
>
> Thanks,
> IB