Add Sequential Numbering in Queries

[xyz0036]

I'm trying to add a column called "RecordID" to my table based on the unique field "AccountNum". The "RecordID" would be a sequential number starting from 1. Hopefully this can be done in a query.

i.e. original table:

AccountNum
242
256
298
308

output table:

RecordID AccountNum
1 242
2 256
3 298
4 308

I've been scratching my head off trying to solve this, any suggestions would be much appreciated!!

 

[Endre]

Doing something like this in Excel is so obvious, but not so in Access, but neverthless can be done using DFunctions:

http://www.access-programmers.co.uk/forums/showthread.php?t=177837

There is a sample db to look at. It will give you pointers and you can use it to create an Update Query for your table. Please remember that if you insert new Account Numbers in your table e.g. 245, then your RecordID sequence is out of kilter and what was 2 will now be 3, what was 3 will now be 4 etc.

1 242
2 245 <--- New Account
3 256
4 298
5 308

Request a reply if you still need more assistance.

 

[RuralGuy]

I usually use Stephan Lebans' stuff: http://www.lebans.com/rownumber.htm

 

[xyz0036]

Thanks for the quick reply. It works!!