Results 1 to 5 of 5
  1. #1
    Girraffa is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    32

    Archiving mystery records and linking to current records

    I have been updating a database for an animal care facility and we have a peculiar problem in terms of historical records for some residents. The pigeons were originally identified by colored leg bands but the bands on the pigeons faded over time and are no longer identifiable to the original colors. The pigeons now have new bands with numbers put on, but there are five pigeons that cannot be definitively linked to the original colored-band records though we have been able to narrow it down to two or three old records for each one: e.g. pigeon 13 could have originally been pigeon “orange” or pigeon “red”. What I want to do is create new resident records using the number band identifiers so going forward we can enter data on the correct specific individuals, and somehow archive or link the color band records to the numbered individuals they might be. I’m having trouble conceptualizing how best to do this if it’s even possible. I’ve considered:
    -making a new table of “archived” pigeon records and linking new records to that
    -adding a field to the residents table to indicate archived records and just making a note in the new records to look at the old ones if needed


    -making a pdf report of all the data from the potential historical records and attaching those to the new records with the appropriate caveats noted
    I have attached a jpeg that shows the fields and relationships of the tables where animal information gets entered.
    I'm wondering if I'm thinking about this wrong.
    Thanks in advance for your thoughts!

    Click image for larger version. 

Name:	ResidentRelationships.JPG 
Views:	25 
Size:	50.0 KB 
ID:	31313

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Which field has the band number?

    If records link on autonumber PK to number FK, there is not any issue for relating records. There is no help for identifying which pigeon goes with the record if the band colors are indistinguishable. If the correct pigeon cannot be identified for the respective data then what purpose can historical data serve except for some gross statistical analysis? Of what use is "This record might be for pigeon 13 or pigeon 22."? Is that really any better than no data at all?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Girraffa is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    32
    It is not much better than no data at all, but I would like to preserve the information we do have as best as I can. The records link on an autonumber, and field "Identification" has the bands.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Still trying to wrap my head around the choice to ever use colors as identifiers. You have a field to record death - how many times have colors been re-used? Still not quite grasping the old and new data structures. Have you always used ResidentID (autonumber ?) as PK? Could you show the old and new?

    Date is a reserved word (it is an intrinsic function). Advise not to use reserved words as names for anything. Also advise not to use spaces nor punctuation/special characters (underscore only exception).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Girraffa is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    32
    Agreed, the choice to use colors as long-term identifiers was not the best, which is why everyone has numbered tags now. In addition the database was originally structured to use the band combinations as primary keys which meant they could not be updated when they were changed--I restructured the database to use autonumbers instead. The original developer named all the fields and I've been cautioned against changing those so I haven't tried to change things like "Date" or field names with spaces. What I ended up deciding to do here was add "Archived" as an option for Fate and created a query that will pull up archived records in case anyone wants to look at them--which is sort of unlikely as they will not help much since as we've identified the data is not good. Thank you for your input! I'm glad to know I'm not the only one who finds this confusing

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

Similar Threads

  1. Best practices for archiving old records
    By sjs94704 in forum Access
    Replies: 2
    Last Post: 04-17-2015, 11:51 AM
  2. Archiving updated records
    By Payner in forum Access
    Replies: 4
    Last Post: 10-12-2014, 02:24 AM
  3. Archiving Records
    By robsworld78 in forum Forms
    Replies: 5
    Last Post: 06-03-2011, 05:00 AM
  4. Archiving records
    By NOTLguy in forum Access
    Replies: 3
    Last Post: 11-21-2010, 03:15 PM
  5. Archiving old records
    By dean in forum Access
    Replies: 7
    Last Post: 09-14-2009, 02:54 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