How do I number my rows ? 4

[Recce]

Good day,

I have a table in Access and would like to number the rows in a column. Can anyone suggest any way of doing this ?



"We know nothing but, that what we know is not the truth..." - Me

 

[HarleyQuinn]

One way would be to add a field with the datatype of AutoNumber.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[cmmrfrds]

Assuming the table has a unique id, then row number can be generated for the duration of the query by a self join.

Select count(*) as rownum,B.id
From WoTable A
inner join WoTable B
On A.id <> B.id
where A.id < B.id
group by B.id

 

[BoxHead]

cmmrfrds,

Thanks for that.

I had to change the <> to <=
and <B.id to <=B.id
because one of my unique id values is 1.

I'll get a lot of use out of this.



John

Use what you have,
Learn what you can,
Create what you need.

 

[PHV]

A ranking query may use a theta join:
Select count(*) as rownum,B.id
From WoTable A
inner join WoTable B
On A.id <= B.id
group by B.id

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Recce]

Thanks everyone. These are some ideas.

"We know nothing but, that what we know is not the truth..." - Me