Results 1 to 5 of 5
  1. #1
    fret hack is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    13

    Append to Archive with specified Key ID

    Hello All,



    Im converting our records system from a series of Excel sheets to an Access database. The data Im working with is keyed by "Permit Number" in the excel sheets, but there are multiple instances of each Permit Number as permits have been renewed through time.

    In the ADB Ive appended the most recent record for each Permit Number to a main table with a new Key ID. I would now like to place certain fields from the larger repetitive data into an archive table that is keyed to the main table.

    How can I transfer the Key ID from the main table so that it matches its multiple corresponding Permit Numbers in the archive table?

    Hope this explanation helps, and thanks in advance.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I would suggest you don't.
    Add a boolean field to the main table called Active.
    Set the default = true
    Then use an update query to set all archived records Active = False

    From personal experience splitting data into main & archived tables many years ago for a large schools database was one of the biggest mistakes I made.
    The other was splitting current students and leavers into separate tables
    The additional coding & maintenance caused by those decisions was enormous and there were few if any benefits
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    fret hack is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    13
    Quote Originally Posted by ridders52 View Post
    I would suggest you don't.
    Add a boolean field to the main table called Active.
    Set the default = true
    Then use an update query to set all archived records Active = False

    From personal experience splitting data into main & archived tables many years ago for a large schools database was one of the biggest mistakes I made.
    The other was splitting current students and leavers into separate tables
    The additional coding & maintenance caused by those decisions was enormous and there were few if any benefits
    I thought this may be the case and Im happy that I asked without getting too far down that road.

  4. #4
    fret hack is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    13
    Just FYI...I found an easy solution to my problem. Just used an update query to match the Key ID from the main table to the data in the supporting tables.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Excellent. Easy solutions are usually the best
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help with archive database !
    By spider in forum Database Design
    Replies: 2
    Last Post: 04-30-2013, 02:46 AM
  2. Auto Archive to new BE file
    By aeiou137 in forum Programming
    Replies: 3
    Last Post: 06-29-2011, 03:49 PM
  3. Archive reports.
    By Desstro in forum Reports
    Replies: 6
    Last Post: 10-31-2010, 11:50 AM
  4. How do I archive a report?
    By Computer202 in forum Reports
    Replies: 7
    Last Post: 03-07-2010, 10:58 AM
  5. Archive data
    By markhook in forum Forms
    Replies: 0
    Last Post: 08-08-2006, 10:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums