Can anyone help me????
I have a table that I need to get the sum of four individual fields and then
rank them from highest to lowest total. After summing up the four fields
individually and ranking them, I have to place them in a single datagrid.
Example:
Table: contains fields for Revenue, Quantity, Credit Apps, Accounts by
rep (not my table!!!)
SQL Statement: Select sum(Revenue), Sum(Quantity), Sum(CreditApps),
Sum(Accounts)...
Rank each column individual from highest Revenue to Lowest, from highest
quantity to lowest etc...
My solution was to query the tables four time (1 for each field) hence
creating four datasets. I then have a sub routine that adds a field titled
Rank to each dataset and adds the sequence number for the ranking.
NOW, I have to join the four tables but not as parent - child but as
additional fields for the same table
Outcome: Total Revenue, Ranking, Total Quantity, Ranking, Total Credit
Apps, Ranking, Total Accounts Ranking...
HOW DO I DO THAT???? HELP???
Deadline looming near!!!
Thanks,
Julio 3 2547
Julio, you can use a DataRelation and an expression column to do
this...Expression Columns support SUM which looks like the only aggregate
you need (they support a bunch of other ones, but that's irrelevant) You'll
can use a DataRelation which will allow you to get the Group By
Functionality http://www.knowdotnet.com/articles/datarelation.html. This is
important b/c if you only have one table and you use SUM(Price) for instance
as the expression every row will have the same value in its SUM(Price)
column
"Julio Sarmiento" <jl*****@bellsouth.net> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl... Can anyone help me????
I have a table that I need to get the sum of four individual fields and
then rank them from highest to lowest total. After summing up the four fields individually and ranking them, I have to place them in a single datagrid.
Example: Table: contains fields for Revenue, Quantity, Credit Apps, Accounts by rep (not my table!!!)
SQL Statement: Select sum(Revenue), Sum(Quantity), Sum(CreditApps), Sum(Accounts)...
Rank each column individual from highest Revenue to Lowest, from
highest quantity to lowest etc...
My solution was to query the tables four time (1 for each field) hence creating four datasets. I then have a sub routine that adds a field
titled Rank to each dataset and adds the sequence number for the ranking.
NOW, I have to join the four tables but not as parent - child but as additional fields for the same table
Outcome: Total Revenue, Ranking, Total Quantity, Ranking, Total Credit Apps, Ranking, Total Accounts Ranking...
HOW DO I DO THAT???? HELP???
Deadline looming near!!!
Thanks, Julio
Ryan, thanks for the input and link. I am going to read your article to
learn more about it.Hopefully, it will help. If not, you will be hearing
(reading) from me again...
Thanks!
"William Ryan" <do********@nospam.comcast.net> wrote in message
news:ee**************@TK2MSFTNGP12.phx.gbl... Julio, you can use a DataRelation and an expression column to do this...Expression Columns support SUM which looks like the only aggregate you need (they support a bunch of other ones, but that's irrelevant)
You'll can use a DataRelation which will allow you to get the Group By Functionality http://www.knowdotnet.com/articles/datarelation.html. This
is important b/c if you only have one table and you use SUM(Price) for
instance as the expression every row will have the same value in its SUM(Price) column "Julio Sarmiento" <jl*****@bellsouth.net> wrote in message news:%2****************@TK2MSFTNGP10.phx.gbl... Can anyone help me????
I have a table that I need to get the sum of four individual fields and then rank them from highest to lowest total. After summing up the four
fields individually and ranking them, I have to place them in a single
datagrid. Example: Table: contains fields for Revenue, Quantity, Credit Apps, Accounts
by rep (not my table!!!)
SQL Statement: Select sum(Revenue), Sum(Quantity), Sum(CreditApps), Sum(Accounts)...
Rank each column individual from highest Revenue to Lowest, from highest quantity to lowest etc...
My solution was to query the tables four time (1 for each field) hence creating four datasets. I then have a sub routine that adds a field titled Rank to each dataset and adds the sequence number for the ranking.
NOW, I have to join the four tables but not as parent - child but as additional fields for the same table
Outcome: Total Revenue, Ranking, Total Quantity, Ranking, Total Credit Apps, Ranking, Total Accounts Ranking...
HOW DO I DO THAT???? HELP???
Deadline looming near!!!
Thanks, Julio
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim cnn As New SqlConnection("data source=test;user id=sa;initial
catalog=test")
Dim da As SqlDataAdapter
Dim strSQL As String
Dim ds As New DataSet()
strSQL = "select id, quantitysold from testRelations"
da = New SqlDataAdapter(strSQL, cnn)
da.Fill(ds, "quantity")
cnn.Open()
strSQL = "select id, dollarsSold from testRelations"
da = New SqlDataAdapter(strSQL, cnn)
da.Fill(ds, "dollarsSold")
'For simple single column relation
Dim sample As New DataRelation("hope", ds.Tables("quantity").Columns("ID"),
ds.Tables("dollarsSold").Columns("ID"))
ds.Relations.Add(sample )
Me.DataGrid1.DataSource = ds.Tables(0).DefaultView
Me.DataGrid2.DataSource = ds.Tables(1).DefaultView
Me.DataGrid3.DataSource = ds.Tables(2).DefaultView ' How do I veiw the
relation???
Me.DataGrid1.DataBind()
Me.DataGrid2.DataBind()
Me.DataGrid3.DataBind()
cnn.Close()
End Sub
"William Ryan" <do********@nospam.comcast.net> wrote in message
news:ee**************@TK2MSFTNGP12.phx.gbl... Julio, you can use a DataRelation and an expression column to do this...Expression Columns support SUM which looks like the only aggregate you need (they support a bunch of other ones, but that's irrelevant)
You'll can use a DataRelation which will allow you to get the Group By Functionality http://www.knowdotnet.com/articles/datarelation.html. This
is important b/c if you only have one table and you use SUM(Price) for
instance as the expression every row will have the same value in its SUM(Price) column "Julio Sarmiento" <jl*****@bellsouth.net> wrote in message news:%2****************@TK2MSFTNGP10.phx.gbl... Can anyone help me????
I have a table that I need to get the sum of four individual fields and then rank them from highest to lowest total. After summing up the four
fields individually and ranking them, I have to place them in a single
datagrid. Example: Table: contains fields for Revenue, Quantity, Credit Apps, Accounts
by rep (not my table!!!)
SQL Statement: Select sum(Revenue), Sum(Quantity), Sum(CreditApps), Sum(Accounts)...
Rank each column individual from highest Revenue to Lowest, from highest quantity to lowest etc...
My solution was to query the tables four time (1 for each field) hence creating four datasets. I then have a sub routine that adds a field titled Rank to each dataset and adds the sequence number for the ranking.
NOW, I have to join the four tables but not as parent - child but as additional fields for the same table
Outcome: Total Revenue, Ranking, Total Quantity, Ranking, Total Credit Apps, Ranking, Total Accounts Ranking...
HOW DO I DO THAT???? HELP???
Deadline looming near!!!
Thanks, Julio
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Walt Borders |
last post by:
Hi,
My problem: Merging two datasets deletes parent elements, preserves
all children.
I've created two dataSets. Each use the same schema, parent-child
nested tables.
The first dataSet is...
|
by: lee_mcknight |
last post by:
I am using Crystal Enterprise V9. I have a report that needs to have
multiple datasets as the datasource(s) on a single subreport (some
common lookup data I need to join to is stored in a seperate...
|
by: Yul |
last post by:
Hi,
We are in the process of designing an ASP.NET app, where a user will
enter some 'Customer ID' to be queried in the database. If the ID is
valid, several stored procedures will be called to...
|
by: AMD Desktop |
last post by:
Hi,
I have one page with four reports in separate datagrids. I need to generate
one report in excel format.
I wrote a script that generate reports for every datagrid separately.
Now, what I...
|
by: Maverick |
last post by:
Assume i have 2 tables stored in a single dataSet (ds) and the relationship
is well added
ds.Tables("product")
ds.Tables("descriptors")
what I want to do is show the data from 2 of the tables,...
|
by: Chumley Walrus |
last post by:
If I have as many as 7 different datasets I want to use, is it possible
to merge 7 different datasets with the Merge method for just one
datagrid?
thanx
chumley
|
by: Dave |
last post by:
(My apologies for posting this on two forums. I have just found out the other
one was the incorrect location)
I am writing a VB.NET 2003 web application to operate on my company's
intranet. It...
|
by: Julio Sarmiento |
last post by:
Can anyone help me????
I have a table that I need to get the sum of four individual fields and then
rank them from highest to lowest total. After summing up the four fields
individually and...
|
by: TomH |
last post by:
I am using VB within VS 2005. How can I have multiple datagridviews on the
same form pulling from the same table? e.g. I want to see all clients from
New York in one view and all clients from LA in...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |