Results 1 to 13 of 13
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Autonumber Merge (2000)

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autonumber Merge (2000)

    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.)

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber Merge (2000)

    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.

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Autonumber Merge (2000)

    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]<img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Autonumber Merge (2000)

    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!!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Autonumber Merge (2000)

    Hans,

    BTW, congrats on the promotion!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Did a "raise" go along with it? <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rtfm.gif border=0 alt=rtfm width=24 height=23>
    You definately deserve something - you da best <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber Merge (2000)

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autonumber Merge (2000)

    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.

  9. #9
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber Merge (2000)

    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.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonumber Merge (2000)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> The "round" figure in this case being zero! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> 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. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Charlotte

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Autonumber Merge (2000)

    LOL

    We all know he has "MSAccess", but apparently he was at a loss of "MSWord(s)". <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Sure is great to see him get the recognition. A kind and generous soul he is.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  12. #12
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber Merge (2000)

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

    Yep. Here it was.

    <!post=Tips and Tricks handout,185081>Tips and Tricks handout<!/post>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonumber Merge (2000)

    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
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •