Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2020
    Posts
    8

    Exclamation Creating an Access DB for Enslaved Individuals -- how to add multiple rows for family members?

    Hello,

    I am trying to create an Access database that will have an individual record for each enslaved person at a former tobacco plantation. In addition to biographical information that will require only one entry (first name, last name, gender, birth date, etc...), I need to be able to add multiple family members (as they are discovered) and multiple events that occurred during that person's life.

    For example:
    Relationship (choice from a lookup list: mother, father, son...) Relative's First Name (this person will also have an individual record in the database) Relative's Last Name
    Relationship (choice from a lookup list: mother, father, son...) Relative's First Name (this person will also have an individual record in the database) Relative's Last Name
    Relationship (choice from a lookup list: mother, father, son...) Relative's First Name (this person will also have an individual record in the database) Relative's Last Name



    Event (choice from lookup list: manumitted, escaped, childbirth...) Event Date Event Description (text field)
    Event (choice from lookup list: manumitted, escaped, childbirth...) Event Date Event Description (text field)
    Event (choice from lookup list: manumitted, escaped, childbirth...) Event Date Event Description (text field)

    Wise ones, can you help me with this? There will be multiple relatives and events for some people and my questions are:


    1. How do I avoid having to create a bunch of fields like Relationship1, Relative First Name1, Relative Last Name1, Relationship2... and Event1, Event2, etc... ? I could have the Relationship and Event choices be multi-selection and then have one text field for Description, but that's so messy.
    2. How can I set these up so rows would be added as relatives and events are added?
    3. What relationships do I create between the main table of biographical info and separate "Relatives" and "Events" tables or subforms?


    Sorry for the long post! I hope you can help and really appreciate any input.

  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
    Hi
    This is a variation on a family tree database which can be tricky to set up due to recursion issues.
    Hopefully yours will avoid such complexity.

    Anyway I suggest you look at the family tree database by forum member @accesstos in this thread: https://www.accessforums.net/showthr...ht=family+tree
    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
    Join Date
    Oct 2020
    Posts
    8
    Hey, Colin! Thanks for the response. I hope you're doing well!

    There are similarities to the Family Tree example and I'm trying to avoid that level of complication. Especially since a lot of this information is currently unknown and I don't want to create something that an archivist 10 years down the road won't be able to add to or figure out. I'm obviously not well-versed in this and it's a volunteer project, so I suppose I'm going to have to go bare bones if I can't easily figure it out.

    Just to throw this out there in case you or anyone else has further ideas. These are the metadata fields I'm trying to construct the database around:

    Biographical Info (the easy part!)

    Enslaved Individual First Name (short text)
    Enslaved Individual Last Name (short text)
    Other Names or Derivative Spellings (short text)
    Gender (drop-down list: male, female, unknown)
    Date of Birth (date)
    Place of Birth (short text)
    Date of Death (date)
    Place of Death (short text)
    Religion (short text)
    Occupation / Skills (multiple selection drop-down list)
    Occupational Notes (short text)
    Physical Characteristics (short text)
    Biographical Narrative (long text)
    Enslaver (multiple selection drop-down list --- we know 5 enslaver names, but might want options to key in other names)

    Event Info (need to add separate events as many times as necessary, and do not need to connect to any other individual's record)
    Event (one selection from a drop-down list: manumission, freed, escaped, hired in, hired out)
    Event Date (date pertaining to the one item selected above)
    Event Description (quoting the source or explaining details of the event)

    Relationship Info
    (if I ditch the idea of connecting to the relationships "family tree style," is there a way to add as many as necessary like the Event Info above?)

    Relation (drop-down list: mother, father, aunt, uncle...)
    Relation First Name
    Relation Last Name

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I very much doubt you can avoid the complexities of the relationships info.
    I could have given a link to a far more complex example than that one!

    BTW what does 'manumission' mean?
    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

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to Colin's comments, there are some recent youtube videos by Richard Rost (599cd) re Genealogy that may give some insight.
    Overview
    There are other related videos.

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Well just a comment on your easy Biographical Info Table.

    You have stated that the following 2 fields would be Multi-Select:-

    Occupation / Skills (multiple selection drop-down list)

    Enslaver (multiple selection drop-down list --- we know 5 enslaver names, but might want options to key in other names)

    Myself, and I am sure that the majority, recommend that these 2 fields become records in a separate related table

    You will need a table name tblOccupations and another table named tblEnslavers

    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  7. #7
    Join Date
    Oct 2020
    Posts
    8
    Hi, Colin!

    So, accept the complexities, is what you're saying. I will do my best and study the Family Tree example you provided in greater detail.

    Manumission means the enslaver gives an enslaved person their freedom, rather than being freed through the abolition of slavery or a court case. Thanks for asking!

    Kat

  8. #8
    Join Date
    Oct 2020
    Posts
    8
    Hi, Orange!

    Thanks so much for the genealogy database video. That will be helpful!

    Kat

  9. #9
    Join Date
    Oct 2020
    Posts
    8
    Hello, Mike60smart,

    Thanks for the advice! I will try the separate tables for those two categories. Quick question though - what if someone has two occupations or more over their lifetime? That was my reasoning for the multi-select dropdown. I never want to substitute the old occupation for the new occupation, I need to keep both.


    Also, I'll review the data normalization description link and appreciate you including that.

    Kudos to everyone for their help on this worthy project :-)

    Kat

    PS - for all the UK helpers, sorry for my American habit of exclamation points.

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Kat

    The reason for a separate table for Occupations is that you can add multiple occupations/skills with Each Occupation/Skill being a new Record in the table

    You would normally have a Main Form for your Biographical information and then a related Subform with the Many Occupations/Skills
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    Join Date
    Oct 2020
    Posts
    8
    Ah! Thank you for the subform explanation. That helped.

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

Similar Threads

  1. Replies: 2
    Last Post: 10-21-2019, 08:10 AM
  2. Replies: 1
    Last Post: 05-06-2018, 08:47 AM
  3. Replies: 5
    Last Post: 01-31-2015, 02:44 PM
  4. All family members with one address
    By Grams in forum Access
    Replies: 5
    Last Post: 08-25-2014, 05:51 PM
  5. Replies: 24
    Last Post: 02-04-2014, 07:26 PM

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