Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Move records (2000)

    Help, I am trying to move records before deleting them from one table to a history table. Does anyone know how. I know I need to do an append and delete, however I only want to append items that are to be deleted into another table in the database as a means of archiving it.

    D

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

    Re: Move records (2000)

    You must create two queries - one append query and one delete query - with the same criteria (WHERE part). Execute the append query first, then the delete query. If you want to make it easy for the user, you can create a macro or VBA code that will execute them both in the correct order.

    Example 1: you want to move all records with OrderDate within the last ten days. Your queries would look like the following in SQL view:

    Append query:
    INSERT INTO tblHistory SELECT * FROM tblActive WHERE OrderDate Between Date()-10 And Date();

    Delete query:
    DELETE FROM tblActive WHERE OrderDate Between Date()-10 And Date();

    Example 2: you have a form frmMyForm that contains a text box txtAmount and a command button cmdMove. When the user enters an amount in the text box and clicks the command button, you want to move all records with OrderAmount at least the amount entered in the text box.

    Append query, named qryAppend:
    INSERT INTO tblHistory SELECT * FROM tblActive WHERE OrderAmount > [Forms]![frmMyForm]![txtAmount];

    Delete query, named qryDelete:
    DELETE FROM tblActive WHERE OrderAmount > [Forms]![frmMyForm]![txtAmount];

    The code behind the command button could look like:

    Private Sub cmdMove_Click
    DoCmd.OpenQuery "qryAppend"
    DoCmd.OpenQuery "qryDelete"
    End Sub

    This can be refined a bit (error handling, silent append/delete) but this will give you the general idea.

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

    Re: Move records (2000)

    Hans's instructions will get the job done. However, think carefully before you do this. What is the point of "archiving" the records in another table in the same database? Archiving is generally done to limit the size of the database and that doesn't apply if you keep the records in the same database.

    If you only want to see a limited number of records, put a yes/no field in the table and call it Deleted. When you want to "delete" a record, set that field to true. Then in your queries use the criteria [Deleted]=False. That also gets around the problem that occurs if you use Autonumber keys in the table. If you delete records from the end of the table after appending them to another table, a compact of the database will allow the autonumber to be reused and you will have conflicting keys between the "active" table and the "archive" table.
    Charlotte

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move records (2000)

    Hi Dorothy

    That

  5. #5
    New Lounger
    Join Date
    May 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move records (2000)

    Thanx all been a great help. I was also thinking about this auto number issue, and wondered if it is possible to create a table containing a sequence of numbers????

    Just one more question...If I don't relate the hostory table to the rest of the database will it still have an affect on the query performance of the database. I thought by keeping the old records seperate the systems performance would be less affected, rather than keeping deleted items in the same table and flagging them???

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

    Re: Move records (2000)

    Are you asking whether it's possible to create a table with numbers in it to use as keys or to create ta table that holds previously assigned keys or what? The answer depends on what you're trying to do. Autonumbers are the easiest key you can use, especially if you aren't too proficient in writing VBA code. However, they are NOT necessarily sequential numbers, so don't plan on that and they are NOT data. They are keys and nothing else.

    Save yoursef a lot of grief and give up on the "history" table. If you don't have a related table, you don't have a "history" table, you just have "stuff", which is meaningless as history. Furthermore, if you have properly related the :*rest* of the tables, you will probably have to delete a bunch of other information to avoid orphaned records in other tables. This may sound like a nifty idea but listen to the voice of experience and don't go there.

    The database's performance is not going to change if you move the records to another table, but doing so will require you to work a lot harder. If you are talking about tens of thousands of records, you need to handle the whole thing differently anyhow. Moving records around within the database does not improve performance, it just makes it harder to maintain.

    The only place where it might appear to affect performance is if you have a large (i.e., 50,000 records) table and you open a bound form on it directly without any filters. In that case, your design is bad and you need to change the way you open the form, not move the records. If you're trying to populate a combobox from a large table, you aren't doing it the most efficient way in the first place and you should look at alternatives rather than moving records.
    Charlotte

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move records (2000)

    Hi Dorothy

    Another system design opportunity is dealing with empty records sets, see Charlotte's post 13341 or do search on: empty record set.

    This is a new concept for most programmers, why deal all the cards when the user only wants to see the ace of spades? I know it helped me tame some very large databases (current and history) in one table and giving the operator access to all records from the same form, table, etc.

    Serial numbers, sequence numbers, invoice numbers, purchase order numbers, etc. is another system design concept that has to be defined by the user, challenged by the programmer before design/coding starts.

    Before you start, answer the questions. Does the sequence number have to assign before operator goes to the keyboard (serialized inventory number at POS counter) or at keyboard as the transaction is started or transaction is ended? Can there be any gaps in the sequence? What if I cancel or void a transaction in process?

    Don't associate autonumber with the above. From what I've learned on this forum the autonumber is only and always use to guarantee an absolutely unique numerical ID for a record. In most cases, Its purpose is for the programmer to properly normalize the database for processing and denormalize for reporting. The end user should never know/see the autonumber.

    Do a search on bubble tables; sequence numbers for previous discussions on dos, and autonumber for don't in regards to above.

    HTH

    John

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

    Re: Move records (2000)

    I think you mean <!post=Post 133341,133341>Post 133341<!/post>, John.
    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
  •