Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    2

    Question Unanswered: MS Access Auto Numbering Delima

    I am trying to creat an archive table using an apend query, though that is not my problem, the problem is when I want to "un-archive" a record with the same automuber back into the original table all of my new record to the first table start to be numbered from the record that was re-inserted into the table. The first table is a registration database, and is pushing well over 10,000 records and goes weekly by several hundred. I have designed a few action queries that copy data from the first table to an "archive" table and delete the record freeing up space in the table, as long as I keep going with out re-inserting any of the "archived" information into my primary table the numbering continues perfectly (or as perfectly as any MS product can ). I am wnating to "un-archive" the data and have it re-issue the same registration number that they had before they archived and then add new records with number that have never been issued before. Is there anything else that can be done beside issue a new number apon the un-archive?

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You can create a UNION query that will allow you to see both tables at once.

    The SQL for this would be something like:

    SELECT Field1, Field2, Field3 FROM tblRegistration
    UNION ALL
    SELECT Field1, Field2, Field3 FROM tblArchive

    Then if you need to re-issue you can select from the temporary table (query) of all of the registrations.

    Also, 10,000 records won't break Access. I have tables with 10 to 100 times that many records. Keep in mind, you don't have to have all of your tables in one Access database.

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    And if your moving that many records around regularly then you should think about upgrading to a industrial database not a personal one, something like Oracle or SQL Server, your opperating close to the limit of what access can handle effciently
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    My dbs involve "un-archiving" records frequently, and I solved he problem by not actually deleting records. Instead, when I "delete" a record I append a copy to a "deleted" table (for historical purposes) then set an IsInActive Yes/No field to Yes. Records with IsInActive set to Yes aren't included in my main form, but can be "un-archive, with original Autonumber intact, by resetting the Yes/No field to No. As DCKunkle said, 10,000 records aren't all that many.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    I never said access couldn't do it, I merely said that it's efficiency starts to drop around that point, though I think I misread the post a little i though they said they were moving 10K+ records around the DB
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Mar 2005
    Posts
    2
    thank you, though I am not moving 10K records, to start I am moving like 7K, but anyway, I have been aware of the need to up-size to say SQL, but do have the $ resources I need to do that, ans as for having more than 10K records in a table it moves way to slow

Posting Permissions

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