Help with Grouping and Subtotaling

Results 1 to 2 of 2

Thread: Help with Grouping and Subtotaling

  1. #1
    Join Date
    Dec 1969

    Default Help with Grouping and Subtotaling

    I have a web based report system that allows an Insurance agency to login and view reports. One one specific report the agency logins and and can view a report that contains several employers that they sell insurance policies too. It also contains what employees have policies listed under that employer, and the amount that the agency has received from them.<BR>What I need is on one page to sort it by employer with a subtotal after the last record for that employer. Then it would go on to the next employer.<BR><BR>Example of what I need:<BR>Report For Agency X<BR><BR>EmployerName1 EmployeeName1 Policy AmountPaid<BR>EmployerName1 EmployeeName2 Policy AmountPaid<BR> Total Received: $xx.xx<BR><BR>EmployerName2 EmployeeName Policy AmountPaid<BR>EmployerName2 EmployeeName Policy AmountPaid<BR> Total Received: $xx.xx<BR><BR>I have been working on this for days and I can&#039;t seem to figure how to do it. I am also using SQL 7.0<BR><BR>Thank you so much for any help.

  2. #2
    Join Date
    Dec 1969

    Default ASP or SQL answer?

    The ASP answer is easy. The SQL answer depends so much upon the particular brand of SQL you are using (and you are using one that I know almost zero about), that I won&#039;t tackle it.<BR><BR>ASP Answer:<BR><BR>&#060;%<BR>SQL = "SELECT Employer, Employee, PolicyID, Amount FROM table ORDER BY Employer, Employee"<BR>Set RS = yourConnection.Execute( SQL )<BR><BR>employer = RS("Employer")<BR>total = 0.0<BR><BR>Do Until RS.EOF<BR>&nbsp; &nbsp; If employer &#060;&#062; RS("Employer") Then<BR>&nbsp; &nbsp; &nbsp; &nbsp; Response.Write "Total: " & FormatCurrency(total) & "&lt;BR&#062;"<BR>&nbsp; &nbsp; &nbsp; &nbsp; employer = RS("Employer")<BR>&nbsp; &nbsp; &nbsp; &nbsp; total = 0.0<BR>&nbsp; &nbsp; End If<BR>&nbsp; &nbsp; amt = RS("Amount")<BR>&nbsp; &nbsp; total = total + amt<BR>&nbsp; &nbsp; Response.Write employer & " -- " & RS("Employee") & " -- " & RS("PolicyID") & " -- " & amt & "&lt;BR&#062;"<BR>&nbsp; &nbsp; RS.MoveNext<BR>Loop<BR>&#039; must "clean up" for last employer:<BR>Response.Write "Total: " & FormatCurrency(total) & "&lt;BR&#062;"<BR>%&#062;<BR><BR>

Posting Permissions

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