I have a table containing the following fields:
rep numbers, year, month, qty, revenue, credit apps, new accounts.
The data in this table reflects the rep's daily numbers. What I need to do
is sum the columns and rank each column totals against all the records in
the table. For example - assume that the figure below are the monthly
totals for four reps.
Rep Number Year Month Qty Revenue Credit Apps
New Accounts
100 2004 1 5 120,000.00
15 7
101 2004 1 15 90,000.00
4 0
102 2004 1 7 30,000.00
22 3
103 2004 1 4 60,000.00
11 17
Now I must rank the reps accordingly and display the following on a
datagrid!!!:
Rep Number Year Month QtyRank Qty RevenueRank
Revenue CreditAppsRank Credit Apps NewAccounts Rank New
Accounts
100 2004 1 3 5 1
120,000.00 4 5 2
10
101 2004 1 1 15 4
30,000.00 2 14 3
15
102 2004 1 2 7 3
60,000.00 1 22 4
3
103 2004 1 4 4 2
90,000.00 3 11 1
17
"
To get the rankings I ran four query that returned the rep number and the
sum from each column by descending sum order (1 column per query - "select
repnum, sum(qty) from table" - "select repnum, sum(revenue) from table"...)
After retrieving all the records, I call a routine the goes through every
record in each dataset.table(x) and added a column ranking to each table
with the dataset.
My problem and question come from this part. How do I display a datagrid
where I combine the four dataset.tables() into one datagrid??? I thought of
data relations, but as you can see, the four tables do not have a parent -
child relationship. Can anyone see a way for me to do this??? If so,
please give me a brief sample. I have spent a better part of the week
researching this but have yet to find an easy solution!!! There may not be
an easy solution, however, I thought of everything from a stored procedure
to creating a data table in asp.net (VB) code. The stored procedure was a
bit complicated and the data table was not very cost efficient when it came
to speed.
Help!!!!
Thanks,
Julio
