If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Requesting Help Ranking Sales Values.....
I have a field in a table named "Ext B/O" that lists the extended Backorder
Value for parts sold. There may be duplicate values and they should show the same rankings. I would like to rank these totals from highest to lowest dollar value. I have tried the following query coding but I only get a count of the total number of records in the query for each record (49435) and NOT the individual ranking of the value. Rank: (Select Count(*) from [tbl_WorkingData] Where [Ext B/O] = 0)+1 Can ANYONE please tell me what I am doing wrong and how to correct it? Thanks!! -- Chip |
#2
|
|||
|
|||
Requesting Help Ranking Sales Values.....
You have to compare with the actual [Ext B/O] (what an ugly field name)
value Rank: (SELECT Count(*) FROM [tbl_WorkingData] AS x WHERE x.[Ext B/O] = [Ext B/O]) + 1 Vanderghast, Access MVP "CBender" wrote in message news I have a field in a table named "Ext B/O" that lists the extended Backorder Value for parts sold. There may be duplicate values and they should show the same rankings. I would like to rank these totals from highest to lowest dollar value. I have tried the following query coding but I only get a count of the total number of records in the query for each record (49435) and NOT the individual ranking of the value. Rank: (Select Count(*) from [tbl_WorkingData] Where [Ext B/O] = 0)+1 Can ANYONE please tell me what I am doing wrong and how to correct it? Thanks!! -- Chip |
#3
|
|||
|
|||
Requesting Help Ranking Sales Values.....
Thanks for your VERY FAST reply!!!
I applied the changes you stated but received the SAME record count (49945) for each record instead of the value rankings. Did I do something else wrong?? Here is the SQL version of the query as it is typed: SELECT (SELECT Count([Ext B/O]) FROM [tbl_WorkingData] AS VT WHERE VT.[Ext B/O] = [Ext B/O])+1 AS Rank, tbl_WorkingData.[Ext B/O] FROM tbl_WorkingData GROUP BY tbl_WorkingData.[Ext B/O] ORDER BY tbl_WorkingData.[Ext B/O] DESC; Here is an example of the results using the above SQL query: Rank Ext B/O 49953 $422,987.50 49953 $322,806.25 49953 $287,116.00 49953 $247,113.75 49953 $238,476.60 49953 $203,750.01 49953 $202,109.60 49953 $201,932.00 49953 $175,674.00 49953 $162,625.38 49953 $146,988.80 -- Chip "vanderghast" wrote: You have to compare with the actual [Ext B/O] (what an ugly field name) value Rank: (SELECT Count(*) FROM [tbl_WorkingData] AS x WHERE x.[Ext B/O] = [Ext B/O]) + 1 Vanderghast, Access MVP "CBender" wrote in message news I have a field in a table named "Ext B/O" that lists the extended Backorder Value for parts sold. There may be duplicate values and they should show the same rankings. I would like to rank these totals from highest to lowest dollar value. I have tried the following query coding but I only get a count of the total number of records in the query for each record (49435) and NOT the individual ranking of the value. Rank: (Select Count(*) from [tbl_WorkingData] Where [Ext B/O] = 0)+1 Can ANYONE please tell me what I am doing wrong and how to correct it? Thanks!! -- Chip |
#4
|
|||
|
|||
Requesting Help Ranking Sales Values.....
You need to explicitly tell the query engine to use tblWorkingData.[Ext B/O]
in the comparison in the subquery. If you don't it will use [Ext B/O] from the VT aliased version of the table. SELECT (SELECT Count([Ext B/O]) FROM [tbl_WorkingData] AS VT WHERE VT.[Ext B/O] = tbl_WorkingData.[Ext B/O])+1 AS Rank, tbl_WorkingData.[Ext B/O] FROM tbl_WorkingData GROUP BY tbl_WorkingData.[Ext B/O] ORDER BY tbl_WorkingData.[Ext B/O] DESC; John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County CBender wrote: Thanks for your VERY FAST reply!!! I applied the changes you stated but received the SAME record count (49945) for each record instead of the value rankings. Did I do something else wrong?? Here is the SQL version of the query as it is typed: SELECT (SELECT Count([Ext B/O]) FROM [tbl_WorkingData] AS VT WHERE VT.[Ext B/O] = [Ext B/O])+1 AS Rank, tbl_WorkingData.[Ext B/O] FROM tbl_WorkingData GROUP BY tbl_WorkingData.[Ext B/O] ORDER BY tbl_WorkingData.[Ext B/O] DESC; Here is an example of the results using the above SQL query: Rank Ext B/O 49953 $422,987.50 49953 $322,806.25 49953 $287,116.00 49953 $247,113.75 49953 $238,476.60 49953 $203,750.01 49953 $202,109.60 49953 $201,932.00 49953 $175,674.00 49953 $162,625.38 49953 $146,988.80 |
#5
|
|||
|
|||
Requesting Help Ranking Sales Values.....
Try this --
SELECT (SELECT Count([VT].[Ext B/O]) FROM [tbl_WorkingData] AS VT WHERE VT.[Ext B/O] = tbl_WorkingData.[Ext B/O])+1 AS Rank, tbl_WorkingData.[Ext B/O] FROM tbl_WorkingData GROUP BY tbl_WorkingData.[Ext B/O] ORDER BY tbl_WorkingData.[Ext B/O] DESC; -- Build a little, test a little. "CBender" wrote: Thanks for your VERY FAST reply!!! I applied the changes you stated but received the SAME record count (49945) for each record instead of the value rankings. Did I do something else wrong?? Here is the SQL version of the query as it is typed: SELECT (SELECT Count([Ext B/O]) FROM [tbl_WorkingData] AS VT WHERE VT.[Ext B/O] = [Ext B/O])+1 AS Rank, tbl_WorkingData.[Ext B/O] FROM tbl_WorkingData GROUP BY tbl_WorkingData.[Ext B/O] ORDER BY tbl_WorkingData.[Ext B/O] DESC; Here is an example of the results using the above SQL query: Rank Ext B/O 49953 $422,987.50 49953 $322,806.25 49953 $287,116.00 49953 $247,113.75 49953 $238,476.60 49953 $203,750.01 49953 $202,109.60 49953 $201,932.00 49953 $175,674.00 49953 $162,625.38 49953 $146,988.80 -- Chip "vanderghast" wrote: You have to compare with the actual [Ext B/O] (what an ugly field name) value Rank: (SELECT Count(*) FROM [tbl_WorkingData] AS x WHERE x.[Ext B/O] = [Ext B/O]) + 1 Vanderghast, Access MVP "CBender" wrote in message news I have a field in a table named "Ext B/O" that lists the extended Backorder Value for parts sold. There may be duplicate values and they should show the same rankings. I would like to rank these totals from highest to lowest dollar value. I have tried the following query coding but I only get a count of the total number of records in the query for each record (49435) and NOT the individual ranking of the value. Rank: (Select Count(*) from [tbl_WorkingData] Where [Ext B/O] = 0)+1 Can ANYONE please tell me what I am doing wrong and how to correct it? Thanks!! -- Chip |
#6
|
|||
|
|||
Requesting Help Ranking Sales Values.....
Karl,
This worked GREAT.....with only one exception..... There is NO ranking listed for #1 even though the first value is the largest. Did I do something else wrong? I copied and pasted your query EXACTLY as you provided. Rank Ext B/O SUN Vendor Name 2 $6,544,470.08 3 $2,232,277.86 4 $1,475,655.94 5 $1,450,400.41 6 $854,059.50 7 $539,627.32 8 $250,943.44 9 $228,035.57 -- Chip "KARL DEWEY" wrote: Try this -- SELECT (SELECT Count([VT].[Ext B/O]) FROM [tbl_WorkingData] AS VT WHERE VT.[Ext B/O] = tbl_WorkingData.[Ext B/O])+1 AS Rank, tbl_WorkingData.[Ext B/O] FROM tbl_WorkingData GROUP BY tbl_WorkingData.[Ext B/O] ORDER BY tbl_WorkingData.[Ext B/O] DESC; -- Build a little, test a little. "CBender" wrote: Thanks for your VERY FAST reply!!! I applied the changes you stated but received the SAME record count (49945) for each record instead of the value rankings. Did I do something else wrong?? Here is the SQL version of the query as it is typed: SELECT (SELECT Count([Ext B/O]) FROM [tbl_WorkingData] AS VT WHERE VT.[Ext B/O] = [Ext B/O])+1 AS Rank, tbl_WorkingData.[Ext B/O] FROM tbl_WorkingData GROUP BY tbl_WorkingData.[Ext B/O] ORDER BY tbl_WorkingData.[Ext B/O] DESC; Here is an example of the results using the above SQL query: Rank Ext B/O 49953 $422,987.50 49953 $322,806.25 49953 $287,116.00 49953 $247,113.75 49953 $238,476.60 49953 $203,750.01 49953 $202,109.60 49953 $201,932.00 49953 $175,674.00 49953 $162,625.38 49953 $146,988.80 -- Chip "vanderghast" wrote: You have to compare with the actual [Ext B/O] (what an ugly field name) value Rank: (SELECT Count(*) FROM [tbl_WorkingData] AS x WHERE x.[Ext B/O] = [Ext B/O]) + 1 Vanderghast, Access MVP "CBender" wrote in message news I have a field in a table named "Ext B/O" that lists the extended Backorder Value for parts sold. There may be duplicate values and they should show the same rankings. I would like to rank these totals from highest to lowest dollar value. I have tried the following query coding but I only get a count of the total number of records in the query for each record (49435) and NOT the individual ranking of the value. Rank: (Select Count(*) from [tbl_WorkingData] Where [Ext B/O] = 0)+1 Can ANYONE please tell me what I am doing wrong and how to correct it? Thanks!! -- Chip |
#7
|
|||
|
|||
Requesting Help Ranking Sales Values.....
Karl,
Please disregard my earlier request for assistance. I did just a little work with the query you provided and was able to resolve the issue I ran into. I truly appreciate your support and expertise. -- Chip "KARL DEWEY" wrote: Try this -- SELECT (SELECT Count([VT].[Ext B/O]) FROM [tbl_WorkingData] AS VT WHERE VT.[Ext B/O] = tbl_WorkingData.[Ext B/O])+1 AS Rank, tbl_WorkingData.[Ext B/O] FROM tbl_WorkingData GROUP BY tbl_WorkingData.[Ext B/O] ORDER BY tbl_WorkingData.[Ext B/O] DESC; -- Build a little, test a little. "CBender" wrote: Thanks for your VERY FAST reply!!! I applied the changes you stated but received the SAME record count (49945) for each record instead of the value rankings. Did I do something else wrong?? Here is the SQL version of the query as it is typed: SELECT (SELECT Count([Ext B/O]) FROM [tbl_WorkingData] AS VT WHERE VT.[Ext B/O] = [Ext B/O])+1 AS Rank, tbl_WorkingData.[Ext B/O] FROM tbl_WorkingData GROUP BY tbl_WorkingData.[Ext B/O] ORDER BY tbl_WorkingData.[Ext B/O] DESC; Here is an example of the results using the above SQL query: Rank Ext B/O 49953 $422,987.50 49953 $322,806.25 49953 $287,116.00 49953 $247,113.75 49953 $238,476.60 49953 $203,750.01 49953 $202,109.60 49953 $201,932.00 49953 $175,674.00 49953 $162,625.38 49953 $146,988.80 -- Chip "vanderghast" wrote: You have to compare with the actual [Ext B/O] (what an ugly field name) value Rank: (SELECT Count(*) FROM [tbl_WorkingData] AS x WHERE x.[Ext B/O] = [Ext B/O]) + 1 Vanderghast, Access MVP "CBender" wrote in message news I have a field in a table named "Ext B/O" that lists the extended Backorder Value for parts sold. There may be duplicate values and they should show the same rankings. I would like to rank these totals from highest to lowest dollar value. I have tried the following query coding but I only get a count of the total number of records in the query for each record (49435) and NOT the individual ranking of the value. Rank: (Select Count(*) from [tbl_WorkingData] Where [Ext B/O] = 0)+1 Can ANYONE please tell me what I am doing wrong and how to correct it? Thanks!! -- Chip |
Thread Tools | |
Display Modes | |
|
|