Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Unanswered: resetting DB control numbers

    I resently got assigned an Access database. It allows me to post new claims and then post receipts under that. So I create a contol number such as 2011_043, this means for year 2011 this is the 43rd claim. I then have a sub record under that, that tracks receipts so this could be 2011_145, this means that it is receipt number 145 for the year 2011. The concern that I have is that I have to reset these for the year 2012. I need to restart at 2012_001 for both. Can anyone tell me how to do this. I have copied the 2011 data base but I do not know how to reset these to start the new year. Any suggestions would greatly be appreciated. At present when I try to enter a record into the 2012 database that I copied it continues on with the last record from 2011.
    Thanks
    rcbme

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How is the control number computed and allocated?
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    3

    resetting DB control numbers

    It is set up as below in design view and the primary key is showing beside of the ClaimNumber

    Field name Data Type Description
    ClaimNumber AutoNumber Claim#
    CtrlNum Text Contol#

    Does this help?

    rcbme

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can always use a DDL SQL instruction to re-seed the counter, but if ClaimNumber is used in any relationship in your database (or worse, if it's indexed) there is a risk that you'll make a mess if you don't know what you're doing. Try:
    Code:
    ALTER Table TableName ALTER COLUMN ClaimNumber  COUNTER(1,1)
    Replace Tablename by the actual name of the table that contains the column Claimnumber.
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no, no, no
    write a function which finds the next available receipt number
    you will probably need to use the dmax function
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    @healdem: Not necessarily. If rcbme actually uses a new database for each new year, what he wants to do should be riskless:
    Quote Originally Posted by rcbme View Post
    I have copied the 2011 data base but I do not know how to reset these to start the new year. Any suggestions would greatly be appreciated. At present when I try to enter a record into the 2012 database that I copied it continues on with the last record from 2011.
    Anyway, make a copy of the database (File --> Back Up Database...) BEFORE trying anything, and extensively test the database after modifying it.
    Have a nice day!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    new table/database new year is as you and I both know a crappy solution.
    especially when a better solution already exists
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    @healdem: I totally agree with you, but if the database was "inherited" as he seems to explain, he can probably not rebuild the whole system and has to deal with what he gets the best he can.
    Have a nice day!

  9. #9
    Join Date
    Sep 2011
    Posts
    3

    resetting DB primary key control numbers

    What I was able to do was to delete the primary key and disable the relationship tables from the previous year. Then I added back the row and the relationship table in the new 2012 table and it seemed to reset the first row to 2012-001 for the primary key which would be the claim number field and the receipt field. i have to experiment with this further, to make sure that it will work. Both of you are correct in that I inherited this mess and I have been unable to find alot of direction with help at where I work to fix this. I very much appreciate both of you and the advice that you have provided. Any other thoughts are again very much appreciated.

    rcbme

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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