|
 |
access_asp thread: two-dimensional table display
Message #1 by Ping Li <pli@l...> on Fri, 14 Dec 2001 15:48:02 -0600
|
|
hi, all,
It's Friday already. Hope everybody enjoys it. Today, I ran across the SUM
function described in this website.
http://www.programmersresource.com/articles/aggregates.asp While the site
only gives a simple example, I thought it would be fun if I can make a
dimensional one as the following chart. But I only got the first part: the
totals for the company. I feel the challenge of this scenario and have done
research via google.com. (maybe it's the issue of output 2-dimensional
table?) But it would be great if some of you have encounter this kind of
problem before and can tell me where the best resource is. Have a great
weekend. Ping
Total # purchased Total $ purchased
Company A 10 $1,000
Division B 4 $40
Group 1 5 $50
Here is my code:
sSQL = "select SUM(Bought) AS Total_Purchased, COUNT(Bought) AS
Total_Number " &_
" from Data; "
set RS = objConn.Execute(sSQL)
Total_Purchased = RS(0)
Total_Number = RS(1)
response.write "The total value of the purchase is: " & Total_Purchased &
" The number purchased is: " & Total_Number
Message #2 by "Zee Computer Consulting" <zee@t...> on Fri, 14 Dec 2001 17:55:41 -0800
|
|
Ping,
I think you are looking for the GROUP keyword in SQL. For example, if
Company is a field name, this SQL string will retrieve a sub-total and a
sub-sum for each different value of company, creating a two-dimensional
recordset:
sSQL = "SELECT Company, " _
& " SUM(Bought) AS Total_Purchased, " _
& " COUNT(Bought) AS Total_Number " _
& " FROM Data " _
& " GROUP BY Company; "
Is this what you meant?
-- Z
----- Original Message -----
From: "Ping Li" <pli@l...>
To: "Access ASP" <access_asp@p...>
Sent: Friday, December 14, 2001 1:48 PM
Subject: [access_asp] two-dimensional table display
> hi, all,
>
> It's Friday already. Hope everybody enjoys it. Today, I ran across the SUM
> function described in this website.
> http://www.programmersresource.com/articles/aggregates.asp While the site
> only gives a simple example, I thought it would be fun if I can make a
> dimensional one as the following chart. But I only got the first part: the
> totals for the company. I feel the challenge of this scenario and have
done
> research via google.com. (maybe it's the issue of output 2-dimensional
> table?) But it would be great if some of you have encounter this kind of
> problem before and can tell me where the best resource is. Have a great
> weekend. Ping
>
>
> Total # purchased Total $ purchased
> Company A 10 $1,000
> Division B 4 $40
> Group 1 5 $50
>
>
> Here is my code:
>
> sSQL = "select SUM(Bought) AS Total_Purchased, COUNT(Bought) AS
> Total_Number " &_
> " from Data; "
>
> set RS = objConn.Execute(sSQL)
> Total_Purchased = RS(0)
> Total_Number = RS(1)
>
> response.write "The total value of the purchase is: " & Total_Purchased
&
> " The number purchased is: " & Total_Number
>
Message #3 by Ping Li <pli@l...> on Tue, 18 Dec 2001 09:23:41 -0600
|
|
Not exactly,Zee,
The Access table has fields named Item #, value, company name, division1,
division2,division3 (the company and its divisions split the items and
costs.) I need to return a summary cost table showing company, division1,
division2 and division3 as column, and total # purchased and total value
purchased as row heading. So you can compare the costs of each of the
divisions in the same company. Do you know what I mean? I thought of using a
set of SQL statements such as:
1)sSQL = "SELECT Company, " _
& " SUM(Bought) AS Total_Purchased, " _
& " COUNT(Bought) AS Total_Number " _
& " FROM Data " _
& " GROUP BY Company;
2)sSQL = "SELECT Division1, " _
& " SUM(Bought) AS Total_Purchased, " _
& " COUNT(Bought) AS Total_Number " _
& " FROM Data " _
& " GROUP BY Division1;
3)sSQL = "SELECT Division2, " _
& " SUM(Bought) AS Total_Purchased, " _
& " COUNT(Bought) AS Total_Number " _
& " FROM Data " _
& " GROUP BY Division2;
Do you think I can do that? Then how can I execute these statements and
output into a 2-dimentional table? Thanks for your help. Ping
-----Original Message-----
From: Zee Computer Consulting [mailto:zee@t...]
Sent: Friday, December 14, 2001 7:56 PM
To: Access ASP
Subject: [access_asp] Re: two-dimensional table display
Ping,
I think you are looking for the GROUP keyword in SQL. For example, if
Company is a field name, this SQL string will retrieve a sub-total and a
sub-sum for each different value of company, creating a two-dimensional
recordset:
sSQL = "SELECT Company, " _
& " SUM(Bought) AS Total_Purchased, " _
& " COUNT(Bought) AS Total_Number " _
& " FROM Data " _
& " GROUP BY Company; "
Is this what you meant?
-- Z
----- Original Message -----
From: "Ping Li" <pli@l...>
To: "Access ASP" <access_asp@p...>
Sent: Friday, December 14, 2001 1:48 PM
Subject: [access_asp] two-dimensional table display
> hi, all,
>
> It's Friday already. Hope everybody enjoys it. Today, I ran across the SUM
> function described in this website.
> http://www.programmersresource.com/articles/aggregates.asp While the site
> only gives a simple example, I thought it would be fun if I can make a
> dimensional one as the following chart. But I only got the first part: the
> totals for the company. I feel the challenge of this scenario and have
done
> research via google.com. (maybe it's the issue of output 2-dimensional
> table?) But it would be great if some of you have encounter this kind of
> problem before and can tell me where the best resource is. Have a great
> weekend. Ping
>
>
> Total # purchased Total $ purchased
> Company A 10 $1,000
> Division B 4 $40
> Group 1 5 $50
>
>
> Here is my code:
>
> sSQL = "select SUM(Bought) AS Total_Purchased, COUNT(Bought) AS
> Total_Number " &_
> " from Data; "
>
> set RS = objConn.Execute(sSQL)
> Total_Purchased = RS(0)
> Total_Number = RS(1)
>
> response.write "The total value of the purchase is: " & Total_Purchased
&
> " The number purchased is: " & Total_Number
>
Message #4 by "Ken Schaefer" <ken@a...> on Wed, 19 Dec 2001 11:16:47 +1100
|
|
This looks like bad table design, hence the problems you are having with
writing the queries.
What if you created a 2nd table which stored companies and divisions (call
this Units). Then you used a JOIN table to link the Item to the Unit, and
store their portion of the cost:
Table Items
Table Units
Table ItemsUnits (ItemID, UnitID, Cost)
Then it'd be easy to write a query joining these three tables together.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Ping Li" <pli@l...>
Subject: [access_asp] Re: two-dimensional table display
: Not exactly,Zee,
: The Access table has fields named Item #, value, company name, division1,
: division2,division3 (the company and its divisions split the items and
: costs.) I need to return a summary cost table showing company, division1,
: division2 and division3 as column, and total # purchased and total value
: purchased as row heading. So you can compare the costs of each of the
: divisions in the same company. Do you know what I mean? I thought of using
a
: set of SQL statements such as:
: 1)sSQL = "SELECT Company, " _
: & " SUM(Bought) AS Total_Purchased, " _
: & " COUNT(Bought) AS Total_Number " _
: & " FROM Data " _
: & " GROUP BY Company;
: 2)sSQL = "SELECT Division1, " _
: & " SUM(Bought) AS Total_Purchased, " _
: & " COUNT(Bought) AS Total_Number " _
: & " FROM Data " _
: & " GROUP BY Division1;
: 3)sSQL = "SELECT Division2, " _
: & " SUM(Bought) AS Total_Purchased, " _
: & " COUNT(Bought) AS Total_Number " _
: & " FROM Data " _
: & " GROUP BY Division2;
: Do you think I can do that? Then how can I execute these statements and
: output into a 2-dimentional table? Thanks for your help. Ping
:
|
|
|
View Cart
