AutoNumber Start Value - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

AutoNumber Start Value

Kent Jamison

4 years ago

The more I take these lessons, the more I realize how much of a mess I inherited from previous users in my Personnel Database. The latest thing I've been doing is fixing the Primary keys and splitting my massively flat tables. I have two personnel tables for employees: 1) PersonnelT and 2) PersonnelT_archive. Most of the fields are the same, except for the mailing fields I've moved to other tables to clean up data.
Unfortunately, the original users/creators of my database did not use Autonumbers for their primary keys. They used an PersonnelID number, which is causing me other problems with new employees, who don't yet have one. (That's another story.) For now, I'd like to add a Primary Key with an Autonumber. Is there a way to start an Autonumber at a certain point? I'd like my first PersonnelID to be my oldest customer who has left (in my archive), run through all former employees (there are dozens), then start with my current employees at the next number. How can I do this?
P.S. I just watched the "Restore Delete AutoNumber" & extended version of the TechHelp video. Easy code, btw. I get that concept. An Append query would work for one or two records, but how do I add AutoNumbers/ID numbers to older records that never had an Autonumber--for hundreds of them?
--Kent J.

Adam Schwanz @Reply

4 years ago

You should just be able to add an autonumber field and be done. Don't need to do anything special with it. Use that for the Primary Keys, and don't worry about what they are, or what order they're in, only Access needs to care. See AutoNumbers Are NOT For You

Use the PersonnelID that you have as a way for you to differentiate them.

It doesn't matter if there are "duplicate auto numbers" across archive tables, you're going to refer to that table anyways anytime you try to open a record, not the "currrent" table.

Kent Jamison OP @Reply

4 years ago

Adam,
Got it. I don't need to differentiate between "duplicate AutoNumbers" across tables FOR NOW. No issues there. I was actually taking that approach UNTIL..... I had to archive someone from my current PersonnelT who has the same AutoNumber as is in my archive table. Yep. You guessed it. It seems if a record has the same ID number in both tables, Access won't let me archive that/those record(s) because it thinks that record already exists in the archive table. Make sense? I suppose I could rename the Primary key in my archive table, but not sure if that would mess things up at this point. Also, I'm hoping to automate this and just use a macro, so I don't have to fat-finger this every time I archive records.

	Kevin Robertson @Reply 4 years ago
Kent, If you change the PersonnelID in your archive table from an AutoNumber to a regular Number that will allow duplicate values in the PersonnelID field.

Kent Jamison OP @Reply

4 years ago

Fair enough, Kevin. I thought of that too. But I want to make sure all my archived records have a PersonnelID. Problem is occasionally I need to look them up and send them things after they leave the workforce, such as a paycheck, retirement award, etc. I can't have Access pulling up two sets of addresses or contact info for those who've retired. I need to make sure that info is _unique_ (You see that? I used a database term.) and tied to the correct person. Rost is rubbing off on me.
The only other way to achieve said uniqueness that I can see is to add an extra primary key, which would kind of defeat the purpose of the current PersonnelID in said archive table. Your thoughts?

Kent Jamison OP @Reply

4 years ago

I'm thinking the best way may be to add a Primary Key first to my archive table. Then Append a false record to my Current customer table to bump up my record number. Then add the PersonnelID to the current personnel table. After all are counted in both tables, I should never have the uniqueness issue again. All new employees would have a unique number and never conflict with the archived employees. Does that sound doable/viable? Am I tracking right?
--Kent J.

	Adam Schwanz @Reply 4 years ago
Yes that is an option Kent. I dont see how you have conflicting auto numbers though, you wouldnt need to import the auto number from the current table to the archive table, just exclude it from the import. The personelID is the identifying field you care about. It would create new autonumbers for the records in the archive table on its own and shouldnt matter what they are.

Kent Jamison OP @Reply

4 years ago

Adam, Brilliant!!!! Why didn't I think of that? Just exclude the PersonnelID from the Archive Append and Delete queries. Then the Archive table adds its own ID/Autonumber to all the Archive files. (I'll test it out on a backup copy first.) Brilliant!!! Absolutely brilliant!!!! I, of course, was thinking of exporting the entire record to Archive, which was causing the problem. Sometimes the simple answer (like yours) is the best and easiest answer. I'll let you know if I have any questions or issues. Thanks! --Kent J.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View