Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14

    Beginner Designing Genealogy Database

    Hi All,

    I'm a hobby genealogist with a lot of records on various Excel files. I want to pull these together into one Access file which I can then Query and add to. Basically I want help with designing the database so it does what I want it to do.

    I have four types of records (which I assume will be my four Tables):
    1. Directory. E.g. John Smith lived in England in 1890.
    2. Births. E.g. Eliza Smith born to John Smith in England in 1885.
    3. Marriages. E.g. John Smith married Sarah Jameson in England 1884.
    4. Deaths. E.g. John Smith died in England in 1900.

    None of these tables will be connected to each other, but they will have common fields (e.g. names, place names, dates). I want to be able to have a Query where I can search, for example, for "John Smith" which will return all of the above results. Alternatively I may want to search for anyone with the surname Jameson who lived in England - this would return only the 3rd record above. This is complicated by the fact that some records may have as many as 6 different people in them (e.g. a marriage with the fathers' names, and two witnesses).

    Firstly - does this sound possible?


    Secondly - how do I go about doing it?
    Thirdly - I intend to build this database by essentially copy-and-pasting data from my excel files. Does that sound nuts or should it be fairly straightforward?

    Thanks,
    Povo

  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
    Why reinvent the wheel? There are a number of economically priced OTS genealogy apps. I think a genealogy db is one of the more difficult types to build. So unless you really want to spend a lot of time learning db principles, Access functionality, programming concepts, macro and/or VBA coding, and then developing - buying a program is probably more cost effective.

    Several threads in forum on this topic. Search: genealogy family tree. Here is one https://www.accessforums.net/showthread.php?t=26006

    Sure, if your worksheets are very simple structure, direct import should be easy. Whether or not you will have a relational database is another question.
    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
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you determined what information you want to get out of this proposed database? Or said differently, do you have a list of questions/scenarios you want to be answered by using this database?
    I think that would be a good place to start with respect to database. It will help with design.

    Rarely would you use a relational database to store data in unrelated tables. The essence of database is tables and relationships between them that represent "business facts". Such as A Customer makes 1 or more Orders; An Order is for 1 or more Products...
    Access is quite different than Excel.

    Here is a link to info re database concepts and planning.



    Update: Just saw June's response -- I agree.

    If you are going to build something yourself, then this free, generic model may give you some ideas.

  4. #4
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14
    Thanks both for your replies. I think (hope) what I am looking for is actually slightly simpler than what you’re thinking.

    I am not trying to create a family tree (I have software for that) or a GEDCOM. I just want somewhere where I can store and search the data. I do not want to try to create links between different records (that’s what the family tree software is for).

    So, for example, using the examples in my OP, if I search for someone called John Smith in England I want all four of those records to show up. I am not saying (and do not want to say) that all four of those John Smiths are the same person – chances are they aren’t. I merely want to see what records I have so I can manually decide whether or not to add them to my family tree.

    At the moment I’ve got 100+ excel files which altogether probably have about 500k names. I do not currently have a satisfactory means of searching this data. I could probably find a way of bunching all the data into four rather clunky excel files, but then it still wouldn’t be very easy to search – and adding new records would be quite complicated (I’d like to be able to create forms for adding new data).

    By the way, is Access a “relational database” by definition?

    I can try to describe the sort of questions I’d like to be asking the database, if that will help?

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What format is required for loading your Family Tree software?
    What range of data manipulation/massaging do you anticipate doing prior to loading your data?
    What data - in general terms - do you currently have in Excel (100+ sheets)?

    I think those are the questions that will determine/suggest the use of Access.
    Since you just want to use Access and a holding tank/search/edit facility, that certainly can be done.
    I have used Access to massage data for input/use in other software (more like an editor utility).

    You can link Excel sheets to Access and manipulate same. Your needs/requirements should be the driving factor.

    Good luck.

  6. #6
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14
    Ok, I'll try to answer each of your questions in order.

    1. I don't want to load any data into my family tree software. I just want to be able to search the data and see what records I have.

    2. Once I’ve figured out what format the data will take in the Access file I will manipulate my excel sheets to match before copying in the data.

    3. The data I currently have in the excel sheets is pretty much as described in my first post, but a little bit more detail. For example, a typical marriage record may have the following headers:

    - Day
    - Month
    - Year
    - Type
    - County
    - Groom’s Forename
    - Groom’s Surname
    - Groom’s Age
    - Groom’s Condition
    - Groom’s Address
    - Groom’s Occupation
    - Groom’s Father’s Forename
    - Groom’s Father’s Surname
    - Bride’s Forename
    - Bride’s Surname
    - Bride’s Age
    - Bride’s Condition
    - Bride’s Address
    - Bride’s Occupation
    - Bride’s Father’s Forename
    - Bride’s Father’s Surname
    - Church/Parish
    - Witness #1 Forename
    - Witness #1 Surname
    - Witness #2 Forename
    - Witness #2 Surname
    - Notes

    Once I have imported my data from the excel files into Access I don’t intend to carry on using them.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Basically I want help with designing the database so it does what I want it to do.
    Which seems to be dynamic searching across tables for similar data...
    Take a look at this and see if it helps/has relevance.

    Access does not like field names with special characters or embedded spaces.

    Can you post some sample data (zip format)?

  8. #8
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14
    Thanks Orange, I had a play around with the file in the post you linked to. Is this thing a “Form”? Once I close the window how do I find it again? Is it searching just that database or is it searching all my databases (i.e. what does the “universal” refer to)?

    I couldn’t actually get any results to appear by searching things…? Do I need to add data to it or should it come with data to test?

    You can probably tell this is turning out to be a lot more complicated than I was expecting!

    Embedded spaces – as in in a sentence? That may be a problem then because most of my data has at least one column which is full of sentences.

    I’ve thrown together some data in an excel. I’ve not made any attempt to format it, but hopefully you can make some sense of it. How do I add an attachment to my post?

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.

  10. #10
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14
    Aha, thanks. Please see attached (hopefully).
    Attached Files Attached Files

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why is mother's name not in marriage record? I can understand including parent names in a birth record but not marriage record.
    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.

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Provo_V001.zip
    Here's your Excel data in 4 tables in a database.

  13. #13
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14
    Because the mother's name wasn't recorded in those days (still isn't in most marriage records). These records are generally copied from original sources like marriage registers in a church.

  14. #14
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14
    davegri - that looks pretty good to me. Thanks very much.

    Is this in a format which can be queried?

    I've noticed a lot of people put "tbl" in front of the name of tables - is that just to identify the fact that they are tables, or is there some other reason too?

  15. #15
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The tbl prefix is a convention. I also name queryies with a q prefix, forms with frm and reports with rpt.
    Any table in access can be queried.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help designing a database
    By Aviator in forum Database Design
    Replies: 2
    Last Post: 03-15-2016, 05:24 PM
  2. Replies: 2
    Last Post: 07-31-2014, 05:45 AM
  3. database designing
    By tommyried in forum Database Design
    Replies: 1
    Last Post: 02-27-2014, 12:41 PM
  4. Designing Form (Beginner)
    By ccordner in forum Forms
    Replies: 1
    Last Post: 01-17-2012, 12:02 PM
  5. Help with designing database
    By nimalp in forum Access
    Replies: 6
    Last Post: 09-15-2010, 10:34 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