Number Increment Field in a Query

  • Thread starter Thread starter Ted Allen
  • Start date Start date
T

Ted Allen

In general, the approach to do this is to use a
correlated subquery or DCount() function to calculate the
rank. The key is to use the Amount field to limit the
domain being counted to just those greater than or equal
to the amount of the current line (in addition to the
fields necessary to correlate the records). If using a
subquery you have to alias the table name to refer to a
second instance of the same source table.

I can't really give any specific sql without knowing the
table structure and sample of the data contained, but
hopefully that helps.

-Ted Allen
-----Original Message-----
I'm trying to return a field in a Query that increments a number.
For example:

Color Amount Stack
------ --------- ------
Red 99% 1
Blue 93% 2
Orange 87% 3
Green 82% 4
Yellow 75% 5
Purple 21% 6

I'm trying to 'Stack Rank' the results but without
having to 'Append' the results to a new table. Does
anyone know how to do this in a Query?
 
I think the following should work for you:

SELECT Test1.Color, Test1.Amount, (SELECT Count(*) FROM
Test1 AS VT WHERE VT.Amount >= Test1.Amount) AS Stack
FROM Test1;

HTH

-Ted Allen
-----Original Message-----
Thanks for the direction Ted. Lets say the 'Source'
Table (titled 'Test1') contains two columns
(fields) 'Color' and 'Amount'.
 
WOW!!! Very Impressive Ted

Thank You so much...
(I've been struggling a day and a half on this one.

You Rock!!!!
 
I did notice one thing however
if the 'Source' table contains two 'Amounts' that are the same: (such as 'Orange' and 'Green' -- at 87%)

Color Amount
------ ---------
Red 99%
Blue 93%
Orange 87%
Green 87%
Yellow 75%
Purple 21

the result is
Color Amount Stac
Red 99%
Blue 93%
Orange 87%
Green 87%
Yellow 75%
Purple 21%

the value '3' is missing and there are two '4' values instead
would you know how to get the 'Stack' to read 1, 2, 3, 5, 6, etc... etc...
 
I did notice one thing however,
if the 'Source' table contains two 'Amounts' that are the same: (such as 'Orange' and 'Green' -- at 87%)

Color Amount
------ ---------
Red 99%
Blue 93%
Orange 87%
Green 87%
Yellow 75%
Purple 21%

the result is:
Color Amount Stack
Red 99% 1
Blue 93% 2
Orange 87% 4
Green 87% 4
Yellow 75% 5
Purple 21% 6

the value '3' is missing and there are two '4' values instead.
would you know how to get the 'Stack' to read 1, 2, 3, 4, 5, 6, etc... etc... ?
 
Hi,


Who would be 3 and who would be 4,since they are both at the same 87%
value.


SELECT a.Color, a.Amount, 1+COUNT(b.Color) As Stack
FROM myTable As a LEFT JOIN myTable As b
ON a.Amount > b.Amount



Will give you 1, 2, 3, 3, 5, 6 rather than 1, 2, 4, 4, 5, 6


Otherwise, you can use the primary key to broke any equality:


SELECT a.Color, a.Amount, COUNT(*) As Stack
FROM myTable As a INNER JOIN myTable As b
ON (a.amount>b.amount) OR (a.amount=b.amount AND a.color>= b.color)




Hoping it may help,
Vanderghast, Access MVP



coder23 said:
I did notice one thing however,
if the 'Source' table contains two 'Amounts' that are the same: (such as
'Orange' and 'Green' -- at 87%)
Color Amount
------ ---------
Red 99%
Blue 93%
Orange 87%
Green 87%
Yellow 75%
Purple 21%

the result is:
Color Amount Stack
Red 99% 1
Blue 93% 2
Orange 87% 4
Green 87% 4
Yellow 75% 5
Purple 21% 6

the value '3' is missing and there are two '4' values instead.
would you know how to get the 'Stack' to read 1, 2, 3, 4, 5, 6, etc...
etc... ?
 
Actually the last post was from Michel Walsh. I just got
your posts this morning. Sorry I forgot to suggest a
tiebreaker in my original post, but I'm glad that Michel
posted some good suggestions and I'm glad they worked for
you.

-Ted Allen
 
Back
Top