Grouping totalling maybe an array?

[Debbie Davis]

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.

 

[Ray Costanzo [MVP]]

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]

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

 

[Ray Costanzo [MVP]]

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

 

[Jeff Dillon]

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

Jeff

 

[Debbie Davis]

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.

 

[Debbie Davis]

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!