Re: ASP totals and sub-totals from an Access or SQL DB

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Jeff Cochran (jcochran.nospam_at_naplesgov.com)
Date: 04/01/04 

Date: Thu, 01 Apr 2004 20:48:01 GMT

On Thu, 1 Apr 2004 21:02:41 +0100, "Laphan" <news@DoNotEmailMe.co.uk>
wrote:

>Hi All
>
>I know I posted a similar topic a few days ago, but I may have gone on a bit
>of a rant with it.
>
>Basically I want to know if it is possible to generate ASP-coded sub-totals
>and totals on a basic recordset where the sub-totals are to break down say
>by stock categories in a standard oRSv, eg
>
>stock cat stock code qty price
>001 IVP 1 3.99
>001 STP 23 14.99
>001 BGT 6 3.99
>001 VET 3 14.99
>
><- insert sub-total for qty and price for 001 ->
>
>004 X4562 5 3.99
>004 X4566 11 14.99
>004 X4563 3 3.99
>004 X4567 2 14.99
>
><- insert sub-total for qty and price for 004 ->
>
><- insert grand total of qty and price for 001 and 004->
>
>I just couldn't fathom this in ASP, so I had to strip my SQL code, stick it
>in MS Excel as a VBA/Query and then use MS Excel's Sub-total function to do
>it for me.
>
>This resolved my immediate problems (ie pain in the neck from the end user),
>but doesn't solve my problem of doing it in ASP.
>
>Any help you can give would be much appreciated.

Assuming you're using a record set, you could loop through the record
set and update a total and subtotal field. Something like

SubtotalStockCat1 = 0
SubtotalStockCat4 = 0
GrandTotal = 0
Do Until rs.EOF
  If rs.Fields("StockCat") = 001 Then
    SubtotalStockCat1 = SubtotalStockCat1 + (rs.Fields("Qty") *
rs.Fields("Price"))
    GrandTotal = GrandTotal + (rs.Fields("Qty") * rs.Fields("Price"))
  End If
  If rs.Fields("StockCat") = 004 Then
    SubtotalStockCat4 = SubtotalStockCat4 + (rs.Fields("Qty") *
rs.Fields("Price"))
    GrandTotal = GrandTotal + (rs.Fields("Qty") * rs.Fields("Price"))
  End If
  rs.MoveNext
Loop
Response.Write "The total for Stock Cat 1 is: " & SubtotalStockCat1
Response.Write "The total for Stock Cat 4 is: " & SubtotalStockCat4
Response.Write "The grand total is: " & GrandTotal

Jeff

Relevant Pages

  • Re: Help needed with a FMP 7 Calculation question please
    ... >> Nick Talley ... > want a portal like the above, the "Total Stock" field is best made in the ... > Now, in Main, create a calculation field, and define it as: ... > Now, it is possible that you want to view different totals, ...
    (comp.databases.filemaker)
  • Re: Help needed with a FMP 7 Calculation question please
    ... > My database includes a table that holds 100's of inventory records. ... > So basically how to I make a calculation to add the totals of:Total Stock ... Now, in Main, create a calculation field, and define it as: ...
    (comp.databases.filemaker)
  • Re: sum and do calculation at each change for 900000 rows
    ... means there was a backorder) and stock number. ... with backorders for a stock number in the same month. ... A Totals query will do this. ...
    (microsoft.public.access.queries)
  • Re: Is this possible to SUM?
    ... I wanted to keep the stock in there showing which item was <0 but also to ... add a further column that summed the totals for each location. ... > That is probably a total query. ... > Vanderghast, Access MVP ...
    (microsoft.public.access.queries)