Numbering Records

[Guest]

I have read all the threads on ranking and numbering, but either don't
understand or I use incorrectly. I have a query, ordered by a field, call
it f1. I would like to have each record given a sequential unique number.
It must ignore ties. I'm thinking of action just like the Data Filll command
in excel.

The microsoft sample query repeats the rank number if a tie exists.

Assuming that I have a query sourced from MyTable, ordered by field f1,
exactly what expression would I put into the grid to give me a column of
unique sequential numbers that ignored ties in f1.

Thanks in advance

Thanks in advance.

 

[Tom Ellison]

Dear gg:

The solution you desire requires the computer to arbitrarily break ties. It
won't do that. That is an indeterminate action. You must supply a set of
columns sufficient to create a unique ordering of the rows or it simply
cannot be done. If you cannot come up with a way to break the ties, then
they won't be broken. It's really that siimple.

Tom Ellison

 

[Guest]

Please expand on and give an example of "set of columns". I assume that it
means combining fields in such a way that we get a unique value. How many
other fields can be included in the expression and what does it look like? I
have a tie breaker, but it can include 20 plus fields.

Further, but related question: is there a way to modify the rank
expression ((Select Count(*) from qryProductCount Where [CountofProductID]

[Prod1].[CountofProductID])+1 ) to rank each group separately? Example:

RANK
Group Points DESIRED
A 14 1
A 14 1
A 20 3
B 12 1
B 15 2
B 16 3






Thanks again.

 

[Tom Ellison]

Dear gg:

Yes, I mean you can choose a set of columns (composite key) in order to get
a unique value. You can include many columns to get this. There is, to my
knowledge, no set limit. However, it can get complex to code, and Jet may
not handle a very large number of columns, since this makes the query get
large.

The query could look like this:

SELECT Column1, Column2, . . .,
(SELECT COUNT(*) FROM SomeTable T1
WHERE T1.Key1 < T.Key1
OR (T1.Key1 = T.Key1 AND T1.Key2 < T.Key2)
OR (T1.Key1 = T.Key1 AND T2.Key2 = T.Key2
AND T3.Key3 < T3.Key3)) AS Rank
FROM SomeTable T

The above is for just 3 column ranking. As you can see, as you test each
additional key, the logic gets longer and longer. By the time you get to
20, the query is a genuine monstrous mess. There is an ANSI standard for
comparing two lists of matching columns that makes this much nicer, but
almost no database engine has implemented this.

You can definitely rank within groups. For your example, a query would be:

SELECT [Group], Points,
(SELECT COUNT(*) FROM qryProductCount Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points > Q.Points) + 1 AS Rank
FROM qryProductCount Q

Tom Ellison

Please expand on and give an example of "set of columns". I assume that
it
means combining fields in such a way that we get a unique value. How many
other fields can be included in the expression and what does it look like?
I
have a tie breaker, but it can include 20 plus fields.

Further, but related question: is there a way to modify the rank
expression ((Select Count(*) from qryProductCount Where [CountofProductID]

[Prod1].[CountofProductID])+1 ) to rank each group separately? Example:

RANK
Group Points DESIRED
A 14 1
A 14 1
A 20 3
B 12 1
B 15 2
B 16 3






Thanks again.

Dear gg:

The solution you desire requires the computer to arbitrarily break ties.
It
won't do that. That is an indeterminate action. You must supply a set
of
columns sufficient to create a unique ordering of the rows or it simply
cannot be done. If you cannot come up with a way to break the ties, then
they won't be broken. It's really that siimple.

Tom Ellison

 

[Vincent Johns]

I suppose you've already considered this, but you've no doubt noticed
that Access really encourages you to specify a primary key for each
Table. That might be a good thing to do here.

Can you either modify the Table you're using, or copy its contents to a
Table you can modify, and add an Autonumber field to it? That would
likely make things a lot easier for you (and more efficient for Access,
if you care about efficiency). Drawback is that a copy wouldn't
automatically be synchronized with the original. Then you'd have just
one 4-byte value to fiddle with, and it would be guaranteed to be unique
within the Table.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Please expand on and give an example of "set of columns". I assume that it
means combining fields in such a way that we get a unique value. How many
other fields can be included in the expression and what does it look like? I
have a tie breaker, but it can include 20 plus fields.

[...]