Add Auto/Sequential Numbers in a make table query

[TPH]

I need to add a number column to a table which sequentially counts my records for me. This table is one that gets created on a monthly basis from a larger table in our database.

So my question is how do I add a number column in my make table query? There are no unique fields in the table I'm referencing or making.

Thanks

[jo15765]

Can you add an field to your table and have the field be an auto number field, and just pull that into your query?

Also the auto number would be unique and never repeat so that you could use it for any record that is added.

[SteveF]

I need to add a number column to a table which sequentially counts my records for me. This table is one that gets created on a monthly basis from a larger table in our database.

So my question is how do I add a number column in my make table query? There are no unique fields in the table I'm referencing or making.

Thanks

Two possibilities:

After the table is made, you can manually add an auto-number column. IIRC it will automatically fill in numbers for you.

OR

Change the process from make-table to "delete the old data and APPEND the new data to my existing table". This would be my preferred method, since you can design the table to include the auto-number field, and it will fill itself during the append process.

JOOC, is there a particular reason you need the sequential counts of the records?

Steve

[TPH]

I can add a field to the table I create in Access, but not to the table I'm pulling my data from in the larger database. I have a table in Access (made from the make table query), how do I add a column to that table and make that column my auto number field?

Thanks

[jo15765]

Open your make Table in design view and add a field call it ID (or whatever floats your boat) and set that field to an autonumber.

[TPH]

It doesn't have to be a sequential count, just unique so I can sort/order the table.

I guess basically I'm trying to add a unique number column because I build more tables off this table and I need a way to be able to identify each record in each individual table I create off the original. There are 10 other tables I create.

[jo15765]

To me that would be your best bet. All you do is add a field, and change it to autonumber. 2 steps and it gives you countless sorting features. Or you could add a field set it as text and manually input any identifier you want to assign to the records.

[TPH]

I think I'm going to add an autonumber filed and instead of deleting that table monthly with my make table query, I'm going to clear it out and therefore when I append new data the autonumber field will popluate regardless of how many records I have.

Thanks

[TPH]

That seems to work, but when I'm testing it as if it was next month, the autonumber doesn't start at 1 when I clear the old data out.

How do I get the autonumber to start at 1 every month after I clear the table? I'm using a delete query to clear the table, if that matters.

Thanks

[jo15765]

If you are using the same table each time, the table will remember what # has been assigned and start with the next number each time.

For example, lets say you appended 200 records for this month, and then next month rolled around the table would begin with 201.

[TPH]

Is there a way to reset the number to zero or 1? Reason is I'm going to be appending/updating the table with over 15,000 lines each month.

Thanks

[SteveF]

Yes, it's pretty simple actually:

1. Empty the table of records.
2. Compact and Repair the database

With any empty table, Compact & Repair resets the AutoNumber counter for that table to 1.

Steve