Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006
    Posts
    10

    Unanswered: Multiple tables using same AutoNumber???

    I have created 5 tables:ACTIVE DATABASE
    INACTIVE DATABASE
    1997 TO PRESENT
    1995 TO 1997
    1990 TO 1995.
    I need the AutoNumber to be unique in all. For example, there is a field in all the databases named EAR #, that number has to be unique to ALL of the tables, when I create a new record that number should come after the last number in the last created record. ACTIVE DATABASE has records 1 thru 10, INACTIVE DATABASE also has records 1 thru 10 just different info. I want INACTIVE DATABASE to have records 11 thru 20 and so on in every database.
    Any help would be GREATLY appreciated.

  2. #2
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Quote Originally Posted by JMASS16
    I have created 5 tables:ACTIVE DATABASE
    INACTIVE DATABASE
    1997 TO PRESENT
    1995 TO 1997
    1990 TO 1995.
    I need the AutoNumber to be unique in all. For example, there is a field in all the databases named EAR #, that number has to be unique to ALL of the tables, when I create a new record that number should come after the last number in the last created record. ACTIVE DATABASE has records 1 thru 10, INACTIVE DATABASE also has records 1 thru 10 just different info. I want INACTIVE DATABASE to have records 11 thru 20 and so on in every database.
    Any help would be GREATLY appreciated.














    something isnt right with your table structure if your going to do that. regardless, two solutions.


    In each table insert say 100,000 rows in one, 200K in the next, 300K in the third, etc, then deleted all the rows. the next insert you do on those tables will be 100,001, 200,001, 300,001 repectfully.

    The more elegant solution would be to write a custom function that scan's each table to check for the uniqueness of a number before using it as a non autonumber key.

Posting Permissions

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