Autonumber Merge (2000)

Topic

New Reply

I’m not sure the “subject” accurately describes this – we’ll see.

I have a table with data from last year (a real mess; ie naming conventions, unused fields, etc.). I also have a new table that is far better (‘Woody-ized’). Both tables have an Autonumber field that is also used for invoice numbers (a different sequence for all orders this year). What I would like to do is append the old table into the new one, but I’m more than a little concerned as to what happens with the “clashing” autonumbers. I seem to recall reading a post about this somewhere along the line, but with our “search” function suspended I can’t locate it.

Bottom line: I’m currently having to open a separate form (based on the old table) to view historical (2002) data, and another for current year orders.

As always, any help, input, assistance, advise, much appreciated.

Reply | Quote

Replies

The main question is: do you have other tables that refer to the AutoNumber field in the old table?

If not, it’s easy: create a query that appends the data from the old table to the new table, but don’t include the AutoNumber field in the query. That way, Access will assign new values for the AutoNumber field automatically.

If you do, it’s still possible, but it’s going to be tricky, and a lot of work. Ask yourself if it’s worth the trouble.
(One idea would be to create a calculated field in a query that generates unique values, and use this to replace the AutoNumber field in the old table and all its dependents. For example, say that the AutoNumber field in the new table currently goes up to 5000. In the query based on the old table, calculate AutoNumberField+10000. The values of this calculated field will not overlap with the new table when you append the results of the query to the new table. You will have to create queries for all dependent tables too. Note: if you want to do this, create backup copies of the database at each stage; it is easy to mess up things.)

Reply | Quote

Hans:

Boy, do I know how to pick ’em, or what?

If it weren’t for the fact that the Autonumber field is the “Invoice” number, it wouldn’t be a problem. I can’t change those numbers after the fact (historical); and, yes, there are other references to that field elsewhere. Might best leave things alone.

Thanks a bunch for your assist (again)!
(PS: believe it or not, I’m still working on that “auto-rating” portion of this db – looking at integrating the mileage program [stay tuned]

Reply | Quote

Hans,

BTW, congrats on the promotion!!
Did a “raise” go along with it?
You definately deserve something – you da best

Reply | Quote

Thanks for the congratulations.

>> Did a “raise” go along with it?

Yes, of course! Woody is very generous. My pay rose from a nice round figure to twice that round figure.

Reply | Quote

The “round” figure in this case being zero! Believe it or not, this is an all volunteer operation … and Hans couldn’t think of a way to decline when the other mods “volunteered” him.

Reply | Quote

LOL

We all know he has “MSAccess”, but apparently he was at a loss of “MSWord(s)”.
Sure is great to see him get the recognition. A kind and generous soul he is.

Reply | Quote

Charlotte helped me (maybe 18-24 months ago) with a problem similar to yours. Her suggestion was something like this: (1) Create a third “master” table that has its own autonumber field (and maybe nothing else). (2) In each of your existing tables, add a field (long integer, not autonumber) that links (with referential integrity) to the autonumber field in the master table. (3) When you create a new record in your “active” table, create a new master table record (with a new autonumber), and assign that autonumber to the link field of your new “active” table record. The old autonumber fields in your existing two tables become superfluous and can be deleted (unless you’re using them for something else).

She (or someone else, I can’t remember) also suggested, as a simpler (but not foolproof) alternative, using the “random” option (rather than “increment”) for the “New Values” property of the autonumber field. Then, the chances of an autonumber “clash” will be something akin to your chances of winning the Powerball lottery.

Hope this helps.

Reply | Quote

Tom:

Thanks for the input!

As mentioned, the autonumber is also the invoice number, so I can’t go about changing them (see above). Appreciate your input though!!

Reply | Quote

Hmmm… That does make things a little more challenging…. I think I’ve seen advice here in the Lounge recommending that Autonumber fields (primary keys, etc.) not be used for real-world information. Not easy to undo now, but maybe for your next database…

The random autonumber option may still work for you. Maybe try something like this: (1) Convert your existing autonumber fields to non-autonumber (just long integers). (2) Create a new random autonumber field that will be used for uniquely identifying the record should it be needed for future relationships between tables (as long as you use the random option, then newly generated autonumbers should all be unique between the two tables). (3) Whenever you create a new record, “hand” generate the new invoice number (no longer relying on autonumber) that’s based on an aggregate max()+1 of the union of your active and historical tables’ existing invoice numbers. This way you retain your old invoice numbers and ensure that new ones are unique between the two tables. You may have to do some “repairs” downstream where your invoice numbers provide the links between tables (depending on how you have things set up) but since you won’t be changing the existing invoice numbers, maybe those repairs will be minimal.

Reply | Quote

[indent]

---

I think I’ve seen advice here in the Lounge recommending that Autonumber fields (primary keys, etc.) not be used for real-world information.

---

[/indent]

Yep. Here it was.

Tips and Tricks handout

Reply | Quote

In addition to that link, the advice is scattered all over this forum. Most of us who are experienced developers and use Autonumbers in our applications know that the purpose of autonumbers is to provide a key, not to convey information, so they don’t need to ever be seen by ordinary users anyhow. Used properly, they are a lifesaver when the company decides to go from 6-digit to 9-digit employee numbers or from 3-character to 7-character account codes, etc. With an autonumber as the key, you simply make the “visible” field wider and convert the values. You lose none of the relations and don’t have to worry about whether anything broke in the conversion

Reply | Quote

As long as you don’t have the same Autonumber’s in either table, you should be able to just write an append query to add the records from the historical table.

Reply | Quote