Rank Order Number

[mattc66 via AccessMonster.com]

I have a table of Records called Order Detail.

The line Number feild was left out. Now I need add it in to the exsisting
data.

I thought I could Use a query and Rank the Order number to figure out the
line# for each record.

OrderNum LineNum
1000 1
1000 2
1000 3
1005 1
1005 2
1005 3
1004 1
etc

What would be the best way to do this. I only need to do it this one time.
The data will have the Line# in the future.

 

[Tom Ellison]

Dear Matt:

How do you want the LineNum assigned? Is there some set of columns within
the detail of each order that uniquely designate the order of ranking? If
so, a query can be designed that does this.

Otherwise, you might do well to consider coding to iterate through the
recordset and, rather arbitrarily, assign line numbers.

Tom Ellison

 

[Michel Walsh]

Hi,


If you have a table Iotas, one field, iota, with values from 1 to 1000 (as
example), then

SELECT a.OrderNum, b.iota

FROM
(SELECT OrderNum, COUNT(*) As myCount
FROM myTable
GROUP BY OrderNum) As a

INNER JOIN

Iotas As b


ON a.myCount >= b.iota




Hoping it may help,
Vanderghast, Access MVP