Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    15

    Question Unanswered: Archiving Completed Projects

    Hello. I have been tasked with creating a DB that will allow us to track out upcoming projects. I currently have 2 tables set up (linked by project name); 1 for the general information like project name, dates, and status. I am using check boxes for the status so once a project is complete the 'completed' box is checked. The second table is where all the part numbers, quantities, and descriptions are being stored. Now the powers that be want the ability to "archive" completed projects. I understand an append and delete query will be needed but I am a tad confused on how to make this work with 2 tables. Does each table need an append/delete query? How can I keep the data integrity of the archived records? Thanks!

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    don't get too hung up in terminology. First - archiving generally means "data we don't want to throw away on the off chance we may need it - but don't want to see anymore"

    and Second - in the old days - memory was preciously expensive and one actually moved archived data to a cheap format - like disk or tape or something....

    and Third - in the old days - processing was preciously expensive - so you wanted to reduce the size of tables so processing was faster.

    Today for alot of real world apps items 2 & 3 are no longer an issue. So really alot of times I can just add a new check box "Archive" into the principle record - and then you control visibility of these records in the underlying query/comboboxes to exclude records marked Archive.........and they just stay in the database as is. That's it.

    Now - having said that - no database can grow to infinity - and so there is a need at some point to delete records older that date X in the working database ; (cause you have a copy of the BackEnd file that still has them - right?). So at some point you need a delete query that cascades properly (or a series of delete queries)to all related/joined tables. But this need may not be there for years depending on the volume of data going into your db.
    www CahabaData com

  3. #3
    Join Date
    Jun 2011
    Posts
    15
    Thanks! That was my logic as well, which is why I added the 'completed' button. However, how can I restrict the records on the data entry form so that only the ones that do not have completed checked show up? I do want them to remain in the tables just not visible from the entry screen. I am the most familiar person here with Access (SCARY!) so I want to keep it as simple as possible. I have attached a copy of the DB so you can see what I am working with. Thanks again!!
    Attached Files Attached Files

  4. #4
    Join Date
    Oct 2009
    Posts
    340
    sorry don't have the time to look at attachments; but all forms (and combo boxes in forms) are based on queries - - so you need to modify those queries so they do not return records that are archived - so the user simply does not see those records.
    www CahabaData com

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    In keeping with NTC's suggestion, in Design View for the Query, in the Criteria for the Field 'Completed' use

    <> -1


    and the Query should return only projects that haven't been marked as 'completed.'

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

Posting Permissions

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