Creating Sequences in Access

[crozfader]

How do I add a unique ID/record count in Access after a d has already been created?

I want to do two things:
- For one table I would like to have a sequence starting a 1 and ending at the last record number
- For another table, I would like to add a sequence that starts at 2 and end at the last record number minus 1

Is there a way to do that?

 

[crozfader]

Also, how do I create complicated sequences such as:


ID ITEM_NUMBER TRANSACTION_DATE QTY

1 XYZ 03/2011 4
2 XYZ 04/2011 6
3 XYZ 05/2011 1
1 ZZZ 01/2011 7 ....



etc. the count would start at 1 every time there is a new item_number.

Thanks

 

[Norie]

For both you can use an AutoNumber field.

That wil start by default at 1, so to start it at 2 for the 2nd table you'll need to
try something like this http://www.databasedev.co.uk/reset_autonumber.html

 

[dk]

Also, how do I create complicated sequences such as:


ID ITEM_NUMBER TRANSACTION_DATE QTY

1 XYZ 03/2011 4
2 XYZ 04/2011 6
3 XYZ 05/2011 1
1 ZZZ 01/2011 7 ....



etc. the count would start at 1 every time there is a new item_number.

Thanks

There's no built in way of having an autonumber do that. If you really want it then you'd need to use VBA to generate the key for you, although I would not recommend this technique and would suggest that you use a regular autonumber (cleaner and less likely to go wrong).

HTH
DK