Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    apmitch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    17

    Post Genealogy Relationship Query

    This old man needs help please, pulling my hair out. My two interests in life are Access and Genealogy. I have now built my own Access Genealogy Database. It is not complete yet, I am continually updating and improving my database.
    My challenge now is to build a relationship table between individuals and the home person, the home person is static, set by the database administrator. So, each person’s form will be different in respect of the home person.
    For each person there is a relationship input, for example, 1st Cousin, Granddad, Father, Home Person etc etc.
    I want to generate a report for each person that shows that persons relationship to the home person, this report will be different for each person. I would like the report to be in the form of a list,
    For example,
    James Mitchell III 1769 – 1848
    3rd Great Grandfather
    V
    William James Mitchell 1803 – 1873
    Son of James Mitchell III
    V
    Adolphus Sharp Mitchell 1850 – 1929
    Son of William James Mitchell
    V
    Charles Mitchell I 1894 – 1967
    Son of Adolphus Sharp Mitchell
    V
    Billy Mitchell I 1925 – 2015


    Son of Charles Mitchell I
    V
    Anthony Paul Mitchell I 1947 –
    You are the Son of Billy Mitchell

    To be honest with you I do not know where to start, I started building a Query but could not complete because I just did not know how to proceed. Any help would be appreciated, thank you in advance.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    do you have these fields?
    tPerson tbl
    PersonID, Fname, Lname, FamID


    tFamily tbl
    FamID, FatherID, MotherID, MarriedDate, DivorceDate


    you would collect all members in the 1 family,
    then move up /dwn the tree using the Father/Mother id

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    apmitch,

    Richard Rost has many youtube videos on Access. He has a couple on Genealogy.
    You may also find some useful articles in the Database Planning and Design link in my signature.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    apmitch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    17
    Thanks for your help, and the answer to your question is yes I have all these fields but its not helping me, i need to show direct relatives in my relationship report, but how does the query know who my diret relatives are?

  6. #6
    apmitch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    17
    Richard Rost is excellent and I have been learning from Richard a long time, but I am stuck this time, my age I expect. I have been going over Richards post, but cannot get my brain around this issue.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you show us your tables and relationships. Even a copy of your database structure with a few sample records. Others will get involved if the issue/requirement is clear and they can see hat you are working with.

    Can you provide step by step description for:
    For each person there is a relationship input, for example, 1st Cousin, Granddad, Father, Home Person etc etc.
    and Your definition of Home Person.

  8. #8
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by apmitch View Post
    Richard Rost is excellent and I have been learning from Richard a long time, but I am stuck this time, my age I expect. I have been going over Richards post, but cannot get my brain around this issue.
    Human reproduction is a recursive procedure.

    A person –surely– has two parents (father and mother) and may be a parent of other persons (children).
    So, you need only one table to describe the genealogy relationships with queries – recursively.

    The query below, returns the parents of the parents of a person (4 persons); namely, its grandfathers and grandmothers. Note that, only the tblPersons is actually table in this scheme. All the others are queries on this table and queries on those queries linked on PersonID and Father or Mother.
    Click image for larger version. 

Name:	gryGrands.JPG 
Views:	27 
Size:	54.9 KB 
ID:	46483
    Brothers and sisters are also persons that have the same parent(s).

    So, the data here is the person; all the other properties of a person (Brother, Cousin, Uncle, Granddad, Father etc) are the info.

    I hope my point is clear enough.

    Cheers,
    John

  9. #9
    apmitch is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    17
    Hello John, thank you for this solution. I have started looking at this and its beginning to work I need a bit of time to get my head around this, and play with my query.

    Thank you, its much appreciated, I will get back to you when I have sorted this out.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  11. #11
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by orange View Post
    You may find this Allen Browne article interesting.
    Exactly the same case in better description!

    Thank you Orange!

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by accesstos View Post
    Exactly the same case in better description!

    Thank you Orange!
    I guess it doesn't pay to be the first one to post a good suggestion.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    micron,
    I guess I just reinforced your suggestion, but must admit I didn't see it until I just checked (POST #4). In any event, someone(s) reviewed the link.

    I recall the set up from an earlier post in this forum dealing with pedigree.

  14. #14
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by Micron View Post
    I guess it doesn't pay to be the first one to post a good suggestion.
    Oh! Sorry Micron!
    I confess that I didn't see the link that you posted. My apologies.
    But, seems that I'm not the only one.

  15. #15
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    All my life I was picked last for teams so I'm used to it (feeling REALLY sorry for me now?).
    OK, not really true, I'm just playing it up a bit. But it isn't the first time on this forum and I doubt it will be the last.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Query on Many to Many Relationship
    By Rendon115 in forum Access
    Replies: 4
    Last Post: 06-29-2020, 07:48 AM
  2. Genealogy Age Education
    By apmitch in forum Queries
    Replies: 12
    Last Post: 10-21-2019, 10:14 AM
  3. Beginner Designing Genealogy Database
    By Povo in forum Access
    Replies: 34
    Last Post: 10-21-2019, 09:48 AM
  4. MSAccess Genealogy DB - help!
    By PipSqueek in forum Access
    Replies: 13
    Last Post: 10-11-2018, 04:30 PM
  5. Replies: 3
    Last Post: 03-19-2015, 05: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