How to populate sequential ID on an existing table?

Hi all,

I have an exsiting table with 1 million records without an unique
squential ID field and I would like add one to the table. How can I
do it?

Also, from the 1 million records, how can I write a query to extract
say 1 to 5000, 5001 to 10000, 10001 to 15000, and so on?

- Grasshopper -

Copy your table structure only.
add an auto number field
append records from old table to new table

Once you have done this you can reference the recodrs bt this auto nuber
field to ge t records 1 to 5000.....



"Grasshopper" wrote:

Hi all,

I have an exsiting table with 1 million records without an unique
squential ID field and I would like add one to the table. How can I
do it?

Also, from the 1 million records, how can I write a query to extract
say 1 to 5000, 5001 to 10000, 10001 to 15000, and so on?

- Grasshopper -

Thank you, Eric. I just tried that and I thought it was going to
work, but I got a message saying that only 65,000 records can be
copied at one time. Too bad the method does not work out. Any more
idea?

- Grasshopper -

Just to follow up with my question. I was able to populate unique ID
on a 100,000 records table, but not the 1 million records table.
Here is my method, I add an ID field to the existing table (with data
in it) and defined the data type as Autonumber, when I do that, the
IDs are populated.

Nevertheless, I still need to solution to my questions listed in
initial post, which are

1) to have the1 million records table populated with an unique
squential ID field.

2) from the 1 million records, write a query to extract
say 1 to 5000, 5001 to 10000, 10001 to 15000, and so on.

- Grasshopper -

Read the response again. Copy table structure only and the add the auto
number field.
Append the records to the table. I tried this and had no problem. The
65,000 records sounds like an Excel issue. If you are trying to create an
Excel spreadsheet 65,000 records is the limit.

"Grasshopper" wrote:

Just to follow up with my question. I was able to populate unique ID
on a 100,000 records table, but not the 1 million records table.
Here is my method, I add an ID field to the existing table (with data
in it) and defined the data type as Autonumber, when I do that, the
IDs are populated.

Nevertheless, I still need to solution to my questions listed in
initial post, which are

1) to have the1 million records table populated with an unique
squential ID field.

2) from the 1 million records, write a query to extract
say 1 to 5000, 5001 to 10000, 10001 to 15000, and so on.

- Grasshopper -

Eric,

I did exactly what you advise me to do. This is not an Excel issue
and has nothing to do with Excel spreadsheet.

This is the message I got:
------------------------------------------
You selected more records than can be copied onto the Clipboard at one
time.

Divide the records into two or more groups, and then copy and paste
one group at a time.
The maximum number of records you can paste at one time is
approximately 65,000.
--------------------------------------

I am using Access 2003. My PC has 1.5 GB of physical memory and 640MB
of system cache.

- Grasshopper -

Are you using and append query or trying to copy append.

"Grasshopper" wrote:

Eric,

I did exactly what you advise me to do. This is not an Excel issue
and has nothing to do with Excel spreadsheet.

This is the message I got:
------------------------------------------
You selected more records than can be copied onto the Clipboard at one
time.

Divide the records into two or more groups, and then copy and paste
one group at a time.
The maximum number of records you can paste at one time is
approximately 65,000.
--------------------------------------

I am using Access 2003. My PC has 1.5 GB of physical memory and 640MB
of system cache.

- Grasshopper -

No, no append query is used.


- Grasshopper -

Grasshopper wrote:
No, no append query is used.


- Grasshopper -

So use one. Copy and paste is not an appropriate thing to do in a database.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thank you guys. The append query works well. Case solved. Have a
great weekend!

- Grasshopper -