• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Customert

Hi,
I need to buld report for many customers , and gives me total amount for every customer, then total for all of them,no worries about dataconnection, Thanks

CustomerName,Item, Amount

Total 'CustomerName'

Total all Customers
0
samprg
Asked:
samprg
  • 2
1 Solution
 
sammySeltzerCommented:
I can handle it 2 ways, one via sql query:

Select customer, Amount, Item, SUM(IsNull(Amount,0)) as IndividualTotal from yourtable where name ='SomeName'
UNION ALL
Select customer, Amount, Item, SUM(IsNull(Amount,0) as GrandTotal from your table
GROUP BY customer, AMount, Item


OR a simpler way since you are using asp:

SQLStr="Select customer, amount, item from your table"
set rs=conn.execute(SQLstr)

 EachTotal= 0.00
 GrandTotal = 0.00

 Do While Not rs.EOF

      custname = rs("customer")
                item        = rs("item")
                amount = rs("amount")
      if isnull(rs("amount")) then
            eachTotal = 0.00
      end if

EachTotal=EachTotal+amount 'This gives you individual total

GrandTotal= GrandTotal+ EachTotal 'This gives you Total for all customers

Then

Customer A's total = <%=formatcurrency(EachTotal,2)%>

All Customers' totals = <%=formatCurrency(GrandTotal,2)


0
 
sammySeltzerCommented:
Don't forget to include:
<%
rs.MoveNext
LOOP
%>

Open in new window

0
 
samprgAuthor Commented:
Awesome
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now