Grouping totalling maybe an array?

**Ray Costanzo [MVP]** · Jul 21 '05, 07:38 AM

Re: Grouping totalling maybe an array?

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" <dd@clickfocal. com> wrote in message
news:OlEACOhtEH A.2196@TK2MSFTN GP14.phx.gbl...[color=blue]
> 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![/color]

**Debbie Davis** · Jul 21 '05, 07:38 AM

Re: Grouping totalling maybe an array?

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]** · Jul 21 '05, 07:38 AM

Re: Grouping totalling maybe an array?

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" <dd@clickfocal. com> wrote in message
news:%234GkIZht EHA.2072@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> 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![/color]

**Jeff Dillon** · Jul 21 '05, 07:38 AM

Re: Grouping totalling maybe an array?

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

Jeff
"Debbie Davis" <dd@clickfocal. com> wrote in message
news:#4GkIZhtEH A.2072@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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![/color]

**Debbie Davis** · Jul 21 '05, 07:38 AM

Re: Grouping totalling maybe an array?

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** · Jul 21 '05, 07:39 AM

Re: Grouping totalling maybe an array?

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.ex ecute("SELECT SUM(extension) as ordertotal,SUM( pcs)
as totalitems,item _no,lc,descript ion FROM est2 where (estno=2968) GROUP
BY item_no,LN,lc,d escription, line_no ORDER BY line_no")

if not rsPB.eof then
Response.Write "<hr>estima te 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><fo nt
color=red>Total for category:<td align=right>" &
formatnumber(ca tTotal,2) & "<td align=right></font>" &
formatnumber(ca tTotal-catCost,2)
end if
Response.Write "<tr><td colspan=5><stro ng><u>" &
rsPB("descripti on") & "</u></strong>"
catTotal=totalS ale
catCost=totalCo st
oldCat=theCat
else
catTotal=catTot al + totalSale
catCost=catCost + totalCost
end if

cname=rsPB("ite m_no")
totalitems=rsPB ("totalitems ")
if totalitems>0 then
totalitems=tota litems
else
totalitems=0
end if
ordertotal=rsPB ("ordertotal ")
if ordertotal>0 then
ordertotal=orde rtotal
else
ordertotal=0
end if
oldCat=theCat
rsPB.movenext
loop
response.write "<tr><td colspan=3 align=right><fo nt color=red>Total
for category:<td align=right>" & formatnumber(ca tTotal,2) & "<td
align=right></font>" & formatnumber(ca tTotal-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!

Grouping totalling maybe an array?

Grouping totalling maybe an array?

Comment

Comment

Comment

Comment

Comment

Comment