assign line numbers

  • Thread starter Thread starter Randal
  • Start date Start date
R

Randal

I have a table where some of the values in the ID field are duplicated
because it is the many side of a 1 to many. What I need to do is run a
query that will assign a line number to each record. Is there a way to do
this? I don't really care about the order or which line is assigned the 1.

Example:

ID Line
101 1
101 2
101 3
102 1
103 1
103 2
 
I don't know how others would approach this, but what I am thinking is that
you
could create add a line number field to your table. Then create a query to
pull up the First record for each Id and
update the line number field with the number 1. Then run the query again
looking for the First record for each Id where the line number field is null
and update the line number field with the number 2. And just keep repeating
like that until there are no more nulls in line number field.

Ed G
 
I don't know how others would approach this, but what I am thinking is that
you
could add a line number field to your table. Then create a query to
pull up the First record for each Id and
update the line number field with the number 1. Then run the query again
looking for the First record for each Id where the line number field is null
and update the line number field with the number 2. And just keep repeating
like that until there are no more nulls in line number field.

Ed G
 
Back
Top