Self Join Relationships
By Richard Rost
6 years ago
Build a Genealogy Database to Track Family Ancestry
If you have ever wanted to build a Genealogy database to track your ancestry or create a Family Tree, then this video is for you. Joyce from Fairbanks, Alaska (a Gold Member) asks, "I’ve been researching my family and tracking my ancestry using Excel sheets. As you can imagine, these are getting very crazy. Is there a good way to track genealogy using Access?"
Members
I'll show you how to double-click to jump to a parent or child's record. We'll create a "quick add" popup form so you can add another person without leaving the person you're on. Great for quick data entry! We'll also see how to prevent deleting someone if they have children related to them.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 More on Genealogy
Links
Intro
In this video, I will show you how to use self-join relationships to track genealogy and family ancestry in a Microsoft Access database. We will cover creating a table to store people, setting up mother and father relationships within the same table, using combo boxes to select parents, and building forms and subforms to display family members and their children. I will also demonstrate how to use queries to show the related children for each person in your database for a basic functional genealogy tracking system.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to track genealogy in your Microsoft Access Database. We're going to track our ancestry using something called a self-join relationship.
Today's question comes from Joyce in Fairbanks, Alaska, a gold member. Joyce asks, "I've been researching my family and tracking my ancestry using Excel sheets. As you can imagine, these are getting very crazy. Is there a good way to track genealogy using Access?"
Well yes, Joyce, there is. It is not simple because you have to use a unique kind of relationship called a self-join, which I'll talk about in a minute.
After you asked me this, I went online and tried to research other genealogy databases built into Access. I tried two or three different ones and I was not happy with any of them. So in a few minutes, I'm going to show you how to make a simple genealogy database using Access. I try to keep it as simple as possible because there are all kinds of cool advanced tricks I can do, but I wanted to make this something simple enough that the average person could build it.
I did have to use an F function though, so there is a little tiny bit of slightly more advanced stuff in here. But I think everyone, if you follow along with what I'm doing, will be able to build this database.
Now before watching this, you should be familiar with basic relationships in Microsoft Access. If not, I've got some free videos on YouTube and on my website. They cover basic one-to-many relationships. There's the perfect example right there. If you've got customers to vehicles, one customer can relate to multiple vehicles. That's a one-to-many relationship. I covered this also in my Access Expert Level 1 class.
The next type of join to be familiar with is a many-to-many relationship. Again, I've got videos for this as well. There are links below. I covered this in my Access Expert 7 class too. Many-to-many means that we're not just dealing with one customer and multiple vehicles. We've got multiple customers and multiple vehicles. This might be a situation, for example, where you've got drivers for a company and you've got fleet vehicles, and you want to track the trips. This particular employee drove this particular car on that particular date. So you'd have a many-to-many relationship involving a junction table. That's a little more advanced.
So what we're going to be covering today is a special kind of many-to-many relationship called a self-join. Now you can build a self-join as a one-to-many where you have, for example, just a particular employee with his supervisor. Then that becomes a table relating to itself. But here we've got a lot of different records that are going to be relating to a lot of different other records because each person has two parents and can have multiple children. You'll see how this works in just a minute.
Now I don't have any free self-join videos available on YouTube at the moment. I might do one later, but if you are familiar with the one-to-many stuff and the many-to-many relationships, watch those videos first before you watch this one and get familiar with those. I'll explain how the self-join works in just a minute. The Relationship Seminar covers just about everything you can ever want to know about relationships.
But let's get started building this genealogy database.
Here I have a blank Access database that I told you we're going to start from scratch. First, we're going to create a person table. Now, the table only cares about entities. What is our entity? It is a person. It could be a place. It could be a thing. It could be a customer. It could be a car. It could be whatever. The person is our entity. So we're going to create one table to store people.
So Create, and then Table Design. I like to build stuff from scratch. I don't like to use a lot of wizards, although I'm going to use a couple of wizards today to keep things simple. But I like building my tables from scratch.
The first thing we need for our person table is our autonumber, our person ID. That's our autonumber. That is a unique key field that is going to signify each person as a unique record. Access takes care of that autonumber for us. Again, if you are not familiar with autonumbers, take my Access Beginner class. I've also got an autonumber video. I'll put links below.
Now I'm going to put just the basic information in for right now for developing the database for class. We're just going to put in first name and last name. Yes, there are a lot of other names you could put in here. You've got your middle name. You've got your suffixes, which could be a separate table: Jr., III, Sr., and so on. You can track married name or given name at birth or all kinds of stuff. Again, just to keep this simple right now, we're just going to do first name and last name.
Now, I do need gender. That's going to be a yes or no field. This is going to be important for the structure of the database since we're tracking mothers and fathers. So this is simply biological gender for the purposes of tracking parenthood. I know today there are a bunch of different genders that people identify by and that's fine, but the database needs to know mother and father. So here, it's binary, yes or no. We'll use 0 for female and 1 for male.
Now we have to track the parents. Each one of these records is going to have to have two other records that it's related to: a mother record and a father record. So we're going to store those autonumbers, those IDs, as mother ID and that will be a number of type long integer, and father ID, again a number.
This is the primary key. In fact, we can make this the primary key. Usually I wait for Access to ask us when we save the table. Let's do it now. Control-S to save the table.
All right, this will be my personT. I like to keep everything singular in my database. It's bitten me a couple of times where I typed in peopleT and then I can't remember later on if it's person or people, or I've got something on my website that I saved years ago as commentsT with an S and now whenever I write code for it, I type in commentsT and it drives me nuts. So now I keep everything singular in my database as far as my code goes in my structure.
Now it's going to ask me about defining a primary key. It's an autonumber. Although it's not required, it's highly recommended. We already have one, so Access will find it and put that little key symbol there.
Let's close this table and let's put some sample data in. It's always easier to work with sample data and this will also allow me to show you what's going on at the table level so you get a better understanding for how these relationships work.
Let's put our first person in here. We're going to put in here Joe Sr. Again, you could put suffixes in a separate table or prefixes too, like Mr. and Mrs., whatever you want. But for the purposes of class, just to keep it simple, we're going to put the suffixes in the first name field. Last name will be Smith.
Now his gender is male, so I'll check that on as true. Zero will be female and not zero will be male. Now we don't have his mother and father in the system yet, so we'll leave those alone to zero. That's fine. That could also indicate we don't have that data, because you're going to go back far enough eventually and you're not going to have the parents.
Next, let's put in Joe Jr. Also Smith. Also male. His father is record one. See that? Record one up here. Person ID 1 is his father.
Now let's put in Joe III. Smith. Also male. His father is record two. See how this works?
Let's put in Sue Smith. Female. Don't have her mother or father. Let's say this is Joe Sr.'s wife.
Now we have Sally Smith. Female. We know Sally is the child of Joe Sr. and Sue. So the mother ID is four. The father ID is one. We also now have Sue in the system and that's mom. So now we have the mother IDs for Joe, mother ID for Joe Jr., so that can be a four.
See how this information fills in? Now we've got Joe and Sue. They are a couple and their children are Joe Jr. and Sally. Joe Jr. has a child, which is Joe III, and we don't have Joe III's mother in there yet, so that's the zero. But you can see how this information is filling in.
Let's put in another family: Bob Jones, male, we don't have his parents. We've got Grace Jones, female, don't have her parents. And we've got Chris Jones, male. Mother is Grace. Father is Bob.
The good thing is, don't get scared, you're not going to have to type in these numbers when our database is finished. We're going to pick from a combo box. You won't have to remember that someone is ID 4 and type all that in. The forms will handle that for us. I'm just showing you at the table level so you get a good understanding of what's happening here.
This is good. The next thing I'm going to do is build a query that has first name and last name together in one field because of the way combo boxes work. We're going to use a lot of combo boxes. If you don't know what combo boxes are, watch my combo box video. Again, that's another free video you can watch on YouTube or on my website. Combo boxes only show you one bit of data at a time. So you're going to have the ID in the combo box, you're going to have first name and last name, and when you drop the box down, you'll see all three. You'll see these two things that will be hidden. But I want to see Smith, Joe Sr. in my box. To do that, we're going to use a query.
So Create. I'm going to open this up so the menus always open. If you double click on this, it can minimize it, but I'm going to leave it open like that. Let's go to Query Design, pull in personT. And I'm going to bring in the star. That brings in all the records. I'm going to make, right over here, my own field called full name. Pull in: it's going to be last name & ", " & first name. That uses what's called string concatenation. Again, another free video on string concatenation. That's how you put multiple strings together with these ampersand symbols. Lots of stuff that you kind of have to know to do this, but it's not like super expert Access stuff. It's just, if you've never seen this before, you kind of need to know this before you build this combo box. So pause this video, go watch the concatenation video.
You'll find the links down below. I try to mention this in my videos toward the end because on YouTube that little 'show more' link is kind of hard to find sometimes. Down below the video, you'll see where it says PC Learning is on my little picture. You'll see 'show more' right underneath the description there. If you click on that, it opens it up and you'll see all kinds of other links.
This is from one of my previous videos, but you see how it's like the concatenation tip. Go watch that now if you don't know what concatenation is, and then come back here.
So what does this do? This is a person, person full name Q. All this is now, as we look at it, gives us a list of all of our people with their full name in one field. Now I can use this query to pull that and put it inside of combo boxes, and you'll see what I mean as soon as I build the person form, which is next.
Let's create Form Design. Again, I like to build my forms from scratch. First thing I'm going to do is set the record source for this. Open up the properties for it right there. Find the record source property, drop that down. We're going to pick personT, not that query. That query is going to be used for our combo boxes. We want personT.
I'm going to close the property sheet, and I'm going to resize this. The first thing I always do is give it a little splash of color. I can't work with white forms. I have to have some color in them before I really do much. Let's go with that color. That looks good.
We're going to save this off the bat. This will be our personF. If you've never made a form from scratch, by the way, this is how I prefer to do it. Once you get used to this, then the wizards and all that, no, it's better doing it this way. Trust me.
Now we're going to add fields to our table. Under Design, you can go to Add Existing Fields. What fields do we want on here? I want the ID, first name, last name, and gender. Don't bother bringing these over because these are just text boxes. We're going to make combo boxes for these. So let's bring these over, drop them, and close the field list.
Let's slide these up. Actually, I'm going to leave a little room at the top because we're going to put our parents at the top. There's person ID right there. Last name will go there. First name will go there. I just reversed those and I realized it as I was saying it. These are our labels over here. These can say whatever you want them to say. I'm just going to make them black so they're easier to read. I'm not going to spend too much time making everything pretty. I cover making stuff pretty in my other videos.
Today, we're just going to make this database functional. Otherwise, it will take me three hours. I'm going to make the background color kind of gray on this. I always do that with autonumber fields. It kind of signifies, hey, you can't change that.
Now, gender as a checkbox can be a little confusing. It can still be confusing even as a toggle button or if you put it in an option group. So I'm going to get rid of this. I'm going to make this into a combo box. Let's go up to Design. Let's find combo boxes right here.
Now again, I've got lessons on combo boxes if you've never done a combo box before. We're going to do the one where we type in the values that we want. Normally, if you've got a list of options you want to pick from like shipping options or vehicles or whatever, you'd make a table and then you'd say I want the combo box to get the values from a table. But this time we only have two values so we're just going to type them in.
Next. I want two columns. Column one is going to be the actual value. It's going to be either 0 or -1. That's how Access stores yes/no values internally. So 0 will be female and -1 will be male. That's just something you have to learn. That's a tip I'm teaching you.
Now we have to take this first column. This is the first column that you can see here as the visible column in the combo box. We want this to be zero width. So I'm just going to click this and drag it all the way to the left like that. It's another little trick for you.
Hit Next. Which column has the actual value in it? There's the value and then there's what you want to see. The value is stored in column one. Hit Next. Where do you want to store that value? I want to store that in the gender field in the table. The table that's bound to this form.
Next. Again, there's a lot of information here but it's not crazy stuff. This is not super advanced like Visual Basic programming stuff. This is all stuff that you should know if you are an Access developer and I've got free lessons to teach you all the stuff that I just covered.
What label would you like to go with it? We're going to delete it anyway so it doesn't matter. Finish. There's the label. Let's get rid of it. Delete, and I'm going to slide this little gender box right there.
Let's save it and see what we got. Joe Smith, male. Joe Jr., male, and so on. There's Sue, female. See how that works? Drop this down. Pick a value.
Let's bring in the parents. Let's make parents combo boxes now. We're going to put the parents right up top here. In fact, I'll put a little line in here just to break this up a little bit. Here it'll put a horizontal line in there. I do not use these often but there we go, a little line.
Now we're going to put two combo boxes up here, one for the mother and one for the father. Let's go back up here and grab a combo box. Let's find them right there. Drop it down here somewhere. The wizard starts up. I want the combo box to look up the values from a table or query this time.
Next. Now where am I getting my list of values? I want to use that query that we built earlier, that person full name Q.
Next. What fields do you want in this box? The person ID goes first. That's the bound value or the bound column. Then I want full name. That's what I'm going to see when I drop the box down.
Next. How do you want to sort it? Let's sort it by the full name field. Ascending is fine. This is what it is going to look like when you open up the box. If this was based on a table, then you'd have a little checkbox saying you want to hide the key column. But if it's based on a query, you don't get that option. All you have to do is take your mouse and just make the width of this first column nothing: make it zero.
Yes, I do cover this in my class. This I believe is Access Expert 1.
Next. Now which one of these columns in the combo box contains the value that I want to store? That's the person ID. Person ID is going to be stored in the table under this form.
Next. What do I want to do when the person picks a value? I want to save it in a field. We're picking the mother, so I'm going to save this in the mother ID.
Next. Let's give it a label: Mother. That is just a label that goes next to it.
Finish. There's my mother box.
If you don't know who Alex is, watch more of my videos. You will.
So there's mother. Let's save this, close it, and open it up and make sure it's working. Joe Smith Sr., we don't have his mother. Let's go to the next person. Joe Jr., mother is Sue Smith. That seems to be working so far. Mother is Sue.
Now we just have to do the same thing for father. If that was new to you, run through that wizard again. Rewind the video if you want a little bit. Run through that same thing again using father instead of mother this time. I am just going to copy this box, copy and paste it like that, change the label.
For you more advanced students, open this guy up. Right here we just change this to father ID. Under 'All' I'm going to come in here and call this one father combo. I like to name my combo boxes father combo. What I did was I changed the control source to father ID. Control source is the field in the form and the table where the data actually is bound to where it could store it.
So father ID is a control source and this is what the wizard sets up for you along with this data set here, which is where you get the data, the row source.
Now what I'm also going to do is go to the mother ID and I'm going to change this from combo13 to mother combo. You'll see why in a little while I picked mother combo instead of just mother ID.
Let's make sure both of these are working, save changes, and reopen it again.
We don't know Joe Sr.'s parents, but if I go to Joe Jr., now we got both mother and father. Let's go to the next one. Joe III. We know who Joe's father is, that's Joe Jr., which is this guy. Sue. Then, there's Sally Smith's parents.
So we're halfway done. We've got the user in there and we've got the person's parents. Now we could stop here and this is a good enough way to track genealogy. You don't really need to see the children down here. That's what we're going to do next. But this is the bottom line and you could just go backwards. Starting with the children, you could put in the records for the parents and then build your family tree that way.
But we're going to take it one step further. We're going to show the children in a subform under this. So Joe Sr.'s children, which would be Joe Jr. and I think someone else in here, let's see. No, does he only have one? I can't remember, but anyway, that's what we're going to build a subform for so we don't have to remember. If I'm looking at Joe's record, I can then see Joe's parents and Joe's children right below.
Now, this is where it gets a little tricky, but I'm going to walk you through it.
What we're going to do next is build a subform. It's going to be another form, still based on the person table, but it's going to appear in continuous forms format. If you've never built a continuous form, I've got lessons on that too.
Let's close this temporarily. Let's go create another form. Form Design.
Again, let's set our data source, pick the form properties right here, go to Data, drop this down, and pick personT.
One more thing while I'm in there: we're going to change this on the Alt-Tab, we're going to change this from Single Form to Continuous Forms. This is where you see one row after another. So you have multiple records at the time on the form as opposed to single form where you only see one at a time.
Again, splash a color for the children. Let's go light purple.
Let's throw some data on here. Design, add existing fields. What do we want to see for the children? I want the ID, the first name and the last name. I want the gender. I pretty much want all of this, but don't bring in, actually, don't bring in gender, mother or father because we're going to again replace those with the combo boxes that we already have built. We can still use the same combo boxes.
So just bring these three things in.
I'm going to turn on the form header and footer. Right click, form header/footer. That appears once at the top of the form. I'm going to chop off these labels, and I'm going to paste them up here. First name, last name, just like that. Person ID goes over here. That's the ID, and again, I like to make those gray. That way, it just visually indicates to the user you can't change that. First name and there's last name. If you want to pretty up these labels, that's fine. I just like to make them black so I can actually read them. You can put spaces in here if you want to and do all kinds of stuff.
Let me bring the bottom of this up like this because this is going to be nice and thin. Save this. I'm going to save this as my childF because this is only going to be used to display children.
Leave some space over here because we're going to put extra combo boxes and stuff in this in just a minute.
Let's close it and open it back up and see what we got.
There's everybody. I hate this alternating row color. I always forget to turn that off and I can't stand it. Design view, form properties. Under Format, click on the detail band, and see how I picked the back color here? Just get rid of that alternate back color. Delete. In this case, it puts that in there. We don't want that. That's black. Let's copy and paste this one here.
Save that. That way it does not alternate the color bands. That's handy on really big continuous forms or reports, but that's what I wanted it to look like.
While I'm in here, just take a second to pretty this up. I want to left justify that. So there we go. Left align. Did I do that in the person form? No, I didn't. Let's fix that too. There are little things that bother me and I have to fix that. I can't sit here looking at it.
So there's our child form. These are all the children. There are 10 of them in all the records. We'll break them down into children in just a minute, and here's all the people.
Now I'm going to take this child form and put it inside of this one. Watch this. Design view. Open this up. Take this child form, drag it, drop it there. That's how you put a subform inside of another form the easy way. I'm going to make this a little bit bigger so you can see it all. Save it. Close it, and let's open it up again. This time, let's open up the person form.
Now, what am I looking at here? I'm looking at Joe Sr., and down below here, I see also Joe Sr. Let's go to the next record. Now, remember, these are the records for this. These are the records for the parent. Go to the next one, Joe Jr. Go to the next one, Joe III.
What's happening here? What happened is that Access saw related fields of the same name on both the master form and the child form, the parent and the child form.
If you open up the properties, not here, not this form's properties, but the subform object's properties right here, this is the tricky part. This is the part that you just have to follow along closely if you've never seen this before. I will explain it though. There's a boundary around this object. That is the subform object inside of the main form. As opposed to if you click there, now you're on the properties for the form itself, this form, versus that, which is the subform. It's a little confusing, but that's how you have to do it.
This is the childF object on the person form. If you go to Data, you can see right here where it says Link Master Fields and Link Child Fields. Access made a relationship because, hey, it sees there's a person ID on both of those forms. So that must be the record that relates these together. Access does not know what we're doing. That's handy if you've got, let's say, customers and orders, as your normal one-to-many relationship. You've got a customer table, and you've got an order table. Each of your orders has a customer ID on it. So if you bring in a subform that has a customer ID on it, it's going to just make that link for you automatically.
Access has no idea we're doing a self-join because it's not really something it's designed to do. So we can still keep this, but we have to break this relationship. Come in here under Data where it says Link Master Fields and Link Child Fields, and just delete those. That will get rid of that relationship, and now this guy has no link to the data up here, which is what we want.
Now let's see what we got this time. Save it, close it, open it back up again.
With no relationship, this is just going to show every record in the table. If you look, every person has every other person as a child. So we need to somehow say, hey, only show these records where this person here is either the mother or the father of this person here.
How do we do that? Like I said, it's a little tricky, but follow along and you'll catch on. What I'm going to do is make another query that's going to show the people who are children of the currently displayed person.
Don't confuse the parents up here with this being the person. These are the parents, that's the actual person record, person ID 1.
So another query that's going to show me just his children. How do we do that? Now, this will only work if the person form is open. That's important to know because we're going to reference this guy right here.
Let's create a query. Create, Query Design. Under Tables, bring in personT. Close that. Let's bring in all the fields.
Now, mother and father. I want to show all of the records for the children down here, where the mother is equal to this person here or the father is equal to this person here. Let's do them one at a time. Let's do the mother first.
So mother ID in the child, in the children records down here, mother ID equals forms!personF!personID.
The mother ID of the children that we're looking up is equal to that personID. Let's save this and check and make sure this is good. Save this. I'm going to call this childCurrentQ. This query will show the children, and again, I keep it singular, the children of the current person. Let me close that.
Now, the current person is Joe Smith. He's a male, so he shouldn't have any children. Let's open it up. The record is empty because nobody equals the mother ID of one. Let's find someone who's got a female who has children. Let's see, there's Sue Smith, and I believe Sue Smith has children. Let's open it up. Yep, Joe Jr. and Sally both have a mother ID of 4.
Now it says personT.motherID because it's duplicated. There's personT.motherID and then there's field zero. We can fix that very easily by just hiding that field. We want to use that value, but it's duplicating it because of the star over here. I teach this in my classes.
Let's run it again, and there we go, motherID of 4.
Now, same thing. We have not updated this subform yet, so don't panic, it's just the query so far. Let's do the same thing but for the father.
Design view. Remember our query basics. Remember, it's AND across and OR down. I'm going to bring in fatherID, hide the field, but I can't put it here. If you put it here, that says this has to be the motherID and the fatherID, which is impossible. So we have to move it down a row. This has to be equals forms!personF!personID.
So this says, basically, show me all the child records, all the people, where the motherID is the current person shown OR the fatherID is the current person shown.
Save that. Close it. Run it again. Let's take a look. That's good. I got mother 4 because the current personID is 4. Let's go to someone else. Let's go to Joe Sr. and run it again. Good, father of 1. See that? Same kids show up. Let's pick someone else. Joe Jr. I think he's got one child, Joe III. Run it. Yep, there we go, Joe III.
So the query is working. Now all we have to do is make this query the record source for the subform. I told you it's a little tricky, but if you do it step by step, you can follow along with me here.
I made my query, it's got the records in it that I need. Now go in here and go to the properties for this form. Change the record source from personT to childCurrentQ. So this guy will get his records from that query we just built.
Save it. Close it. Close it. Now we're going to open up the person form, and this should be correct down here now. Joe Sr., we don't have his parents, but those are his children. Let's go to the next record. Joe Jr., those are his parents, that's his one child. Next record, Joe III, there's his father, we don't know who his mother is. Next record, Sue Smith has two children: Joe Jr. and Sally Smith. Next, Sally Smith, there are her parents. Bob Jones has a child, Chris Jones. Grace Jones is the mother. Chris Jones, there are the parents. Now everything is working correctly.
That, in a nutshell, is all you need for the basic design of your genealogy database. That took us about a half an hour to get the basic functionality down. We can put people in, we can put in the parents, and now we can see all the children down here.
Of course, there is lots more we could do with this database, and I'm going to do some more stuff in the extended cut for the members. But this is the basic functionality to get you started. The hard stuff is already done. The relationships are set up, our basic query is set up, our forms.
Now we're just going to make some enhancements.
OK members, there's lots more to learn about genealogy. In the extended cut, members only edition, I'm going to show you how to double click to open up one of those parent records or one of those child records. So you can double click here on Tannen Buford, for example, and jump right to his record. We'll see how to add children in right in the subform. We'll create those little plus buttons so we can click on a button and open up our own little quick add form, so if you don't want to leave the record and you want to add a mother or father, you can just type it in there, and it will automatically populate in the form for you without having to jump around in multiple records to add people. I'll show you how to prevent deleting a person if they have children. This way you don't leave orphaned records in your database, and that's actually a database term, orphaned records. Not because they're orphaned, but because you deleted the parent.
So that's in the members-only extended cut edition. You'll find a link to it down below.
I also kind of maybe had some fun with this. After recording the members cut, I went for another eight hours and recorded a whole bunch of other stuff, and it ended up becoming a full seminar. You can find the full seminar on my website; I'll put a link below. There is also a template you can get that has the actual database that I built in it.
Lesson one is what we just did in this class. Lesson two is the extra material for the extended cut members-only edition. Then I added a bunch of extra stuff. I added ages so we can calculate their age, the current age or their age at death. We colorize the gender combo boxes. I added pictures and a graphical family tree that you can click and jump up and down and navigate through the tree. I added tracking life events like marriage, birth, death, name changes, and so on. A main menu, a master person list where you can search and sort, and all kinds of extra features.
So for more information on either the seminar or the template, find the links below this video.
However, if you just want this and more extended cut TechHelp videos, join now and become a member. I try to post at least a couple of extended cut videos every week. This members-only extended cut video by itself is an hour long. The genealogy stuff that I did for the members was about an hour until I realized, you know, this is getting kind of long. I should probably make this a full seminar; then I went for seven more hours.
If you join, you'll see a list of all the different levels and the perks that are associated. But this video and more like it will always be free here on YouTube and on my website. You can actually download a free copy of this database template, a working copy that you can add and edit records and see how you like it, and that is free too.
Make sure you subscribe to my channel. That of course is also and always will be free. You'll get notifications whenever I release a new video. You can find all the extra links down below the description window. YouTube does a pretty good job of hiding it, but it's down there; if you click on that, you'll see all these different links to different stuff.
If you have not yet taken my Access Level 1 course, please do. It's free. It's three hours long and it covers all the basics of building tables, queries, forms, and reports. If you like Level 1, Level 2 is just $1, and it's free for all members of my YouTube channel.
If you'd like to see your question answered in a TechHelp video like this, there's the page. Drop it on my TechHelp form.
Don't forget to stop by my website and check out the Access Forum. There is all my other cool stuff: Blog, Facebook, Twitter, and of course, my YouTube page.
That's it for today, folks. Thanks for watching and I hope you learned something.
Quiz
Q1. What is the main purpose of the video tutorial?
A. To show how to create a genealogy tracking database in Microsoft Access
B. To compare Microsoft Access and Excel for family research
C. To demonstrate advanced Visual Basic programming in Access
D. To teach how to secure Access databases with passwords
Q2. What type of relationship is essential for tracking genealogy in Access, according to the video?
A. One-to-one relationship
B. Many-to-one relationship
C. Self-join relationship
D. Outer join relationship
Q3. In the context of Microsoft Access relationships, what is a self-join?
A. A table that joins to itself via primary and foreign keys
B. A join between two unrelated tables
C. A join between a query and a table
D. A relationship used only for lookup tables
Q4. When designing the person table for genealogy, what is the main entity being tracked?
A. Families
B. Vehicles
C. Persons
D. Events
Q5. What field type is used for the primary key in the person table?
A. Short Text
B. Number
C. Autonumber
D. Lookup Wizard
Q6. Why are gender values stored as Yes/No in the database?
A. To allow for multiple genders
B. To simplify the database structure for tracking mothers and fathers
C. Because Access does not support text fields
D. To allow users to input their own gender descriptions
Q7. Which field types store the Person IDs of the mother and father in each person record?
A. Short Text
B. Date/Time
C. Number (Long Integer)
D. Hyperlink
Q8. Why does the instructor prefer keeping table and object names singular, like personT instead of peopleT?
A. It conforms to Access defaults
B. It prevents confusion in code and naming conventions
C. It is required by Access templates
D. It helps Access run faster
Q9. Why is creating sample data in tables recommended before building forms and queries?
A. To test printing features
B. To allow for easier understanding and visualization of relationships
C. To enable automatic form generation
D. To improve security
Q10. What is the purpose of creating a full name field using string concatenation in a query?
A. To simplify data entry
B. To display combined first and last name values in combo boxes
C. To automate form creation
D. To improve database performance
Q11. What is a combo box used for in the genealogy database setup?
A. To display static images
B. To allow users to pick parents and gender from a list
C. To prevent data entry errors
D. To create reports
Q12. Why are two combo boxes (for mother and father) placed at the top of the person form?
A. To let users select which family tree to view
B. To enable selection or assignment of mother and father for each person
C. To choose the person's marital status
D. To display their birth dates
Q13. What is the function of the subform childF in the genealogy database?
A. To list all siblings for a person
B. To display all children of the person shown in the parent form
C. To show a list of all unique last names
D. To manage user permissions
Q14. What property in the subform setup must be cleared to ensure it only shows children of the current person?
A. Default View
B. Link Master Fields / Link Child Fields
C. Allow Additions
D. Record Source
Q15. What is the key technique used in the childCurrentQ query to filter children records?
A. Filtering by current date
B. Using a reference to the open form's current person ID in the motherID or fatherID fields
C. Grouping by last name
D. Searching by suffix
Q16. Why is it important to use OR instead of AND when setting up criteria in the childCurrentQ query for motherID and fatherID?
A. To connect multiple tables
B. To show records where either parent could be the current person
C. To filter records by type
D. To sort records by gender
Q17. What does the instructor warn about regarding removing people from the genealogy database?
A. It may crash Access
B. It can create orphaned records if a person with children is deleted
C. It will merge records automatically
D. It deletes associated templates
Q18. Which Access object is used to display a list with multiple records at once (such as a list of children)?
A. Single Form
B. Datasheet View
C. Continuous Forms
D. Split Form
Q19. What is a suggested enhancement for more advanced members according to the video?
A. Adding a picture to every person
B. Double-clicking to open related records and adding quick-add functionality in subforms
C. Automating table creation with macros
D. Creating PowerPoint presentations from data
Q20. What should you do if you are unfamiliar with the basics of Access relationships before building a genealogy database?
A. Watch introductory videos on one-to-many and many-to-many relationships
B. Only use Wizards and avoid manual design
C. Buy a template and avoid learning relationships
D. Install additional Access plugins
Answers: 1-A; 2-C; 3-A; 4-C; 5-C; 6-B; 7-C; 8-B; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B; 15-B; 16-B; 17-B; 18-C; 19-B; 20-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's video from Access Learning Zone focuses on how to track genealogy using a Microsoft Access database. I am your instructor, Richard Rost.
The topic today centers on a question from a student who wanted to know if there is an effective way to organize family ancestry information using Access rather than increasingly complex Excel spreadsheets. The answer is yes, but it requires understanding a special kind of relationship called a self-join.
Before jumping into genealogy, I want to mention that if you are not already familiar with basic relationships in Access, such as one-to-many or many-to-many, you should review those concepts first. There are free resources and videos for both of those types of joins on my website and YouTube channel. These foundational relationships are essential to understand before getting into self-joins, which are a bit more advanced.
With genealogical data, you encounter a relationship where each person is associated with two parents and potentially has multiple children. This complexity means you must set up a self-join in the database, where records in the table relate to other records in the same table. For example, an employee-supervisor structure could also use a self-join, but in genealogy, the relationship is more involved due to multiple parent-child connections.
To keep things straightforward, I decided to build a simple genealogy database from scratch in Access. The fundamental entity in this setup is a person, so I started with a basic "person" table. In Access, I began by creating the table in design view, preferring to avoid wizards except when they simplify repetitive tasks.
The person table needed an autonumber field for the unique Person ID, along with essential fields like first name, last name, and a gender field. For the genealogy database, the gender field is treated as a yes/no value, strictly for biological parent tracking – necessary to store information about mothers and fathers. Additionally, each record includes fields for Mother ID and Father ID, both of which store the Person ID of the respective parent. These fields create the self-join structure because they reference records within the same table.
I also discussed naming conventions for tables and fields. I keep everything singular – for example, "personT" instead of "peopleT" – to avoid confusion later when programming.
Next, I entered some sample data into the person table to illustrate how relationships are formed. For each person, you input names, gender, and, as the database grows, you record the IDs for their mother and father if they are known. For demonstration, I created a small family including parents, children, and another couple with their child, showing how Mother ID and Father ID link records together.
One challenge with data entry in the raw table is having to remember and manually enter Person IDs for parents. In practice, once the database forms are set up, this process is handled by combo boxes for user-friendly selection rather than typing in numbers.
To improve the data entry process and display, I set up a query to create a "full name" field by combining last name and first name. This full name is easier to present in combo box lists, letting users choose "Smith, Joe Sr." or similar when selecting parents, instead of just an ID number.
The next step was to create the main form for entering and managing person records. In form design, I set the record source to the person table, added a bit of color for clarity, and placed the core fields at the top of the form. Instead of using a simple checkbox for gender, I built a combo box that lets users pick male or female, and explained how Access stores yes/no values internally (as 0 or -1).
For the parents, I created two combo boxes at the top for selecting a mother and a father. These combo boxes source their lists from the full name query mentioned earlier and store the selected value in the appropriate parent ID fields. I explained that you can sort these by full name for ease of use and hide the ID columns so the user only sees the names.
Once the core interface was working, I added an important enhancement: the ability to view a person's children directly under their record. This involved building a subform, also based on the person table, but formatted as a continuous form so it displays multiple rows.
Initially, when adding the subform to the main form, Access tries to automatically link the records using matching field names, such as Person ID. However, for genealogy, that default link is not correct, since a person's children are not merely records with the same Person ID. Therefore, I removed the default link between the forms.
To display only the correct children for a given person, I created a new query as the data source for the subform. This query returns all records where either the mother or father matches the currently selected person's ID. By referencing the current value of the open person form, it provides the list of direct children for the displayed record. I walked through the process of creating and testing this query to ensure that it reliably shows only the appropriate children for any given individual.
Once linked, this subform provides a clear, dynamic summary of each person's immediate descendants, complementing the parent pickers at the top. This setup gives you a fully functional family tracking database: users can add people, identify parents, and instantly view someone's children in one place.
For those who wish to take their genealogy tracking further, I offered additional guidance and resources. In the extended cut for members, I expand on advanced features, including:
- The ability to double-click on a parent or child to jump to their detailed record
- Quick-add buttons to let you insert new family members directly from the subform, without navigating away
- Automatic checks that prevent deleting a parent who still has children, to avoid creating orphaned records in the database
I also mentioned that after filming the extended cut, I expanded the material even further into a comprehensive seminar. That seminar covers even more features such as calculating ages, color-coding gender fields, handling pictures, building navigable family trees, logging life events (birth, death, marriage, name changes), adding a main menu, a master person list for searching and sorting, and several other advanced database enhancements. The seminar and an actual template database for genealogy tracking are available via my website.
Beyond that, you can find more resources, including the basic and extended tutorials, on my website and YouTube channel. My introductory Access Level 1 course is a free three-hour course that covers the foundations you need to succeed with Access, and Level 2 is available for just $1 or free for members.
If you have database questions of your own, you can submit them through my TechHelp form. Be sure to check out my website for community forums and all the other content I have to offer.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Tracking genealogy using a self-join relationship in Access
Creating a Person table with autonumber primary key
Adding gender and parent IDs to the Person table
Entering sample data with parent-child relationships
Using queries to concatenate first and last names
Creating and customizing combo boxes for gender selection
Building a query for combo box display of full names
Designing a form to display and edit person records
Adding combo boxes for selecting mother and father on the form
Customizing combo box labels and properties
Copying and modifying combo boxes for parent selection
Creating a continuous subform to display children
Embedding a subform inside the main person form
Configuring subform properties and breaking default relationships
Creating a query to show only children of the current person
Filtering subform records using the current person ID
Linking the subform to the filtered children query
Testing and verifying parent-child relationships in the form
|
