Grouping totalling maybe an array?

Discussion in 'ASP General' started by Debbie Davis, Oct 19, 2004.

  1. Debbie Davis

    Debbie Davis Guest

    Hi there,

    I have an estimate file that gets printed for customers. The file
    consists of a verbiage line like ROOF then the estimated items follow
    under that "V" then another category like FIRST FLOOR and more estimated
    items, etc. I'd like to be able to get a subtotal between the verbiage
    lines but I have no idea how to accomplish this. The verbiage lines
    have a line code of "V" and the corresponding items have a line code of
    "N" under each "V". So I would want to add up all of the "N" item costs
    under each "V". Is this possible? Many thanks in advance. Here's an
    example...

    V FIRST FLOOR
    N NAILS 56.00
    N BOARDS 175.00

    (subtotal of FIRST FLOOR 231.00)
    V ROOF
    N SHINGLES 3000.00
    N PLYWOOD 500.00

    (subtotal of ROOF 3500.00)

    etc.
    P.S. Please be nice, I'm not a brain like you guys out there.



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Debbie Davis, Oct 19, 2004
    #1
    1. Advertising

  2. Where is the data coming from? A database? If so, what version, and what
    does your current query look like?

    Ray at work

    "Debbie Davis" <> wrote in message
    news:...
    > Hi there,
    >
    > I have an estimate file that gets printed for customers. The file
    > consists of a verbiage line like ROOF then the estimated items follow
    > under that "V" then another category like FIRST FLOOR and more estimated
    > items, etc. I'd like to be able to get a subtotal between the verbiage
    > lines but I have no idea how to accomplish this. The verbiage lines
    > have a line code of "V" and the corresponding items have a line code of
    > "N" under each "V". So I would want to add up all of the "N" item costs
    > under each "V". Is this possible? Many thanks in advance. Here's an
    > example...
    >
    > V FIRST FLOOR
    > N NAILS 56.00
    > N BOARDS 175.00
    >
    > (subtotal of FIRST FLOOR 231.00)
    > V ROOF
    > N SHINGLES 3000.00
    > N PLYWOOD 500.00
    >
    > (subtotal of ROOF 3500.00)
    >
    > etc.
    > P.S. Please be nice, I'm not a brain like you guys out there.
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
    Ray Costanzo [MVP], Oct 19, 2004
    #2
    1. Advertising

  3. Debbie Davis

    Debbie Davis Guest

    My apologies Ray. It's a SQL 2000 database, no queries yet!! I really
    don't know where to start. Thanks again.



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Debbie Davis, Oct 19, 2004
    #3
  4. Alright, what do your tables look like in your database? Can you mimic the
    layout with some sample data here, i.e.

    Table1:

    ID Title Price
    1 Nails 39
    2 Hammer 19
    3 Level 52

    Customers:
    ID......

    Help us "see" what you have to work with as a datasource.

    Ray at work

    "Debbie Davis" <> wrote in message
    news:%...
    > My apologies Ray. It's a SQL 2000 database, no queries yet!! I really
    > don't know where to start. Thanks again.
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
    Ray Costanzo [MVP], Oct 19, 2004
    #4
  5. Debbie Davis

    Jeff Dillon Guest

    Is this an existing application? Or are you designing this from scratch...

    Jeff
    "Debbie Davis" <> wrote in message
    news:#...
    > My apologies Ray. It's a SQL 2000 database, no queries yet!! I really
    > don't know where to start. Thanks again.
    >
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
    Jeff Dillon, Oct 20, 2004
    #5
  6. Debbie Davis

    Debbie Davis Guest

    Table 1 (there's only one):

    LN (identity) 1
    estno 2968
    lc V
    description FIRST FLOOR
    pcs 0
    price 0

    LN (identity) 2
    estno 2968
    lc N
    description nails
    pcs 1000
    price 50

    LN (identity) 3
    estno 2968
    lc N
    description 1 x 2 x 8 boards
    pcs 1000
    price 25000

    LN (identity) 4
    estno 2968
    lc V
    description SECOND FLOOR
    pcs 0
    price 0

    LN (identity) 5
    estno 2968
    lc N
    description plywood
    pcs 500
    price 13000

    LN (identity) 6
    estno 2968
    lc N
    description hardiplank
    pcs 100
    price 350

    and there could be as many as 20 entries (N lines) under each V code.
    This is an old unix system and my boss wants it converted as closely as
    possible. I've managed to convert the look and feel and database as
    closely as possible and developed a data entry page to enter the
    estimate, but the user will print a printer friendly estimate for the
    customer and I need to subtotal the N codes under the V headings. So
    the V lines are like headings and the N lines are items under each
    heading.

    FIRST FLOOR
    nails 100 50
    boards 1000 25000
    subtotal 25050

    SECOND FLOOR
    plywood 13000
    hardiplank 350
    subtotal 13350

    I hope that helps. Many thanks for your time.




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Debbie Davis, Oct 20, 2004
    #6
  7. Debbie Davis

    Debbie Davis Guest

    Hi Ray, I think I've got it. Here's what I've done and it's not very
    pretty but it's working for me:

    set rsPB=objConn.execute("SELECT SUM(extension) as ordertotal,SUM(pcs)
    as totalitems,item_no,lc,description FROM est2 where (estno=2968) GROUP
    BY item_no,LN,lc,description, line_no ORDER BY line_no")

    if not rsPB.eof then
    Response.Write "<hr>estimate for 2968"
    Response.Write "<table border=1
    width='100%'>"'<tr><th>Product<th>Cost"

    oldCat=""
    theCat=""
    catTotal=0

    do while not rsPB.eof
    theCat=rsPB("lc")
    theProduct=rsPB("item_no")
    totalSale=rsPB("ordertotal")

    if oldCat<>theCat then
    if catTotal>0 then
    response.write "<tr><td colspan=3 align=right><font
    color=red>Total for category:<td align=right>" &
    formatnumber(catTotal,2) & "<td align=right></font>" &
    formatnumber(catTotal-catCost,2)
    end if
    Response.Write "<tr><td colspan=5><strong><u>" &
    rsPB("description") & "</u></strong>"
    catTotal=totalSale
    catCost=totalCost
    oldCat=theCat
    else
    catTotal=catTotal + totalSale
    catCost=catCost + totalCost
    end if

    cname=rsPB("item_no")
    totalitems=rsPB("totalitems")
    if totalitems>0 then
    totalitems=totalitems
    else
    totalitems=0
    end if
    ordertotal=rsPB("ordertotal")
    if ordertotal>0 then
    ordertotal=ordertotal
    else
    ordertotal=0
    end if
    oldCat=theCat
    rsPB.movenext
    loop
    response.write "<tr><td colspan=3 align=right><font color=red>Total
    for category:<td align=right>" & formatnumber(catTotal,2) & "<td
    align=right></font>" & formatnumber(catTotal-catCost,2)
    Response.Write "</table>"
    end if

    I'm sure there's lots of room for improvement. Many thanks for your
    ideas!



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Debbie Davis, Oct 20, 2004
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Tobe

    Client-side Totalling

    Tobe, Jul 30, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    360
    Ray Dixon [MVP]
    Jul 30, 2003
  2. Mr Newbie

    Best Aopproach At Totalling DataGrids

    Mr Newbie, Nov 29, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    374
    Edwin Knoppert
    Dec 4, 2005
  3. Guest
    Replies:
    5
    Views:
    601
  4. John Pritchard-williams

    Most compact command for associate array 'totalling'?

    John Pritchard-williams, Aug 7, 2008, in forum: Ruby
    Replies:
    8
    Views:
    121
    John Pritchard-williams
    Aug 9, 2008
  5. charliefortune

    Simple totalling of a column with onChange()

    charliefortune, Apr 29, 2006, in forum: Javascript
    Replies:
    4
    Views:
    99
    charliefortune
    May 2, 2006
Loading...

Share This Page