Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Archiving old records (2000)

    I have a database of condo owners and related information in multiple tables. When the owner sells I would like to archive their information. I was thinking possibly of creating a query bringing all their information together from the multiple tables and then setting up a macro where this information was deleted and placed in a archive table. Am I on the right track or is there some other way of doing this? Thanks for any suggestions.

    Dorothy

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Archiving old records (2000)

    That is a way to solve the problem, but often people what to know who the previous owner was, or want a complete ownership history. Another solution is to use an ownership table which links condos to owners, and in the ownership table have fields for start and end of ownership. If you want the current owner, you simply look for the record that has no end date. Even if you are dealing with a 1000 unit complex, the tables won't get very large, and with proper indexing you will find the performance is excellent.
    Wendell

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

    Re: Archiving old records (2000)

    Unless the number of records becomes very large, I would keep everything in the original tables. You can add a Yes/No field Archived to the main table (or to all tables, as needed). When a record needs to be "archived", set the Archived field to Yes (tick the check box).

    Create a query (or queries) that selects the records for which Archived is False, and use this as record source for your form. The user will see only the non-archived records. For maintenance purposes you can also create a form that displays all records, or only the archived ones.

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archiving old records (2000)

    One thing I should have mentioned. The reason I would like to move them out of the table is that the primary key is related to the condo unit and my client has to keep this same key for the new owners (coming down from upper management.) I cannot have a duplicate key in the same table, which is why I need to move the record. What do you think?

    Dorothy

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

    Re: Archiving old records (2000)

    What exactly do you mean by "the primary key is related to the condo unit"? The condo unit PK should be a foreign key in the owner record, not the primary key. If upper management has decreed that structure, they need some lessons in relational design and a good thumping to boot! <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>
    Charlotte

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

    Re: Archiving old records (2000)

    You should have separate tables for condos (in which each condo is unique), owners, and an intermediate table that lists condos and owners, with start and end dates, as indicated by Wendell.

  7. #7
    Star Lounger
    Join Date
    Mar 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Archiving old records (2000)

    Thanks all. It worked out well.

Posting Permissions

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