Requesting Help Ranking Sales Values.....

[CBender]

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!!

 

[vanderghast]

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

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!!

 

[CBender]

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

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

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!!

 

[John Spencer]

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

 

[KARL DEWEY]

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.

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

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

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!!

 

[CBender]

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

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.

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

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



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!!

 

[CBender]

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

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.

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

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



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!!