|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Genealogy Seminar Welcome to the Microsoft Access Genealogy Seminar. Total running time is 8 hours, 12 minutes. Lessons
Resources
QuestionsPost your questions or comments below
IntroIn this lesson, you will learn how to build a genealogy database in Microsoft Access, starting with setting up tables, self-joined relationships, and forms to track people and their family connections. We will cover one-to-many and many-to-many relationships, building a family tree form, using conditional formatting, adding birth and death dates, calculating ages, storing pictures and documents, handling unknown genders, and tracking life events such as marriages and name changes. You'll also learn how to manage and navigate records, filter and sort people, organize external files, and add user experience enhancements throughout the database. This is part 1.TranscriptWelcome to the Genealogy Seminar Part 1 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.The Genealogy Seminar is an interesting project. It started off for me as a simple TechHelp video. These are usually short videos that answer questions sent in by my students. I generally record a free 5-10 minute video and post it on my website and on YouTube. Then, following the free video, I post an extended cut for members only. The extended cut will have a couple of additional features that will add to the video. Well, I finished the extended cut and I was having so much fun with it that I kept going for about 7 more hours. So that is what you have here as the Genealogy Seminar Part 1. You will hear me in the videos referring to it being a TechHelp video or a members only extended cut. Then, somewhere after that, I realized, oh well, this is going to be a full seminar. So, let's see what's covered in this full seminar. Lesson 1 is a free TechHelp video that I post on YouTube and on my website. It covers the basics of how to set up the table, the self-joined relationships, and the forms we need for tracking our people. We will start off by learning about the different kinds of relationships like one to many, and then many to many. Then, we will learn about self-joins, which are a special type of many to many relationship to relate people to other people, basically the table to itself. We will start off building our tables and filling them up with some sample data, of course. We will set up a basic person form where we can put the person's information in, their first name, last name, gender, and then pick their mother and father, which are other people from the table. Then, we will set up a subform so we can track the person's children underneath. So, at the top you've got the person's mother and father, and then down below you've got all of his children. Lesson 2 is the extended cut members only edition. This is generally only available for silver members and up on my YouTube channel, but it's Lesson 2 of the seminar. We will start off by making the mother and father and the child ID clickable, which means you can double click on those to jump to that person. For example, here I could double click on the mother, Sue Smith, and open up her record. We will add the mother and father to the subform and have it default to the current person based on the gender. So, if the current person is male and you add a child, it will put that person in as a father if you add a child record. Then, we will make a quick add person form and we will put little plus buttons next to everybody. So, if you want to quickly add a mother or a father or a child record, you can click on the plus button and it will open up the little person add form. You can type them in real quick and then they will be added directly to the information. Then, we will make our own delete button. We are going to set the main form so you can't delete records and we are going to provide our own delete button. This is because we don't want people to be able to delete records that have children. You have to delete the child records first, then delete the individual person. After recording the extended cut for the TechHelp members, something weird happened. I started having fun with this project. Thus was born the Genealogy Seminar Part 1. In Lesson 3, we start building the ancestor form, what I like to call the family tree form. I will show you how to build it step by step. In Lesson 4, we will use some conditional formatting to change the color of the gender combo box to either blue or pink. You can see it in action right there. We will add birth date and death date to each person. Then, we will add a function to calculate that person's age. If they are not dead yet, it will give you their current age and if they do have a death date, it will calculate their age at death. In Lesson 5, I will show you how to add attachments to your database so you can store pictures and other documents. Then, we will add profile pictures to each of our people. I will teach you how to browse and select files on your hard drive and then store them right in your database. You will also see how to store text files, documents, PDFs, and all kinds of other information directly in your database. Then, of course, we will add those pictures to our family tree form. Then, we will make the ancestors clickable so we can double click on one of those ancestors and jump right to their record and see who their ancestors are so we can travel up the ancestor tree. In Lesson 6, we will add an unknown gender because in doing my genealogy research, sometimes you find an ancestor and you are not sure what their gender is. So, rather than default as male or female, if you find Chris Watkins from 1642 you might not know if Chris is a male or a female. Next, we will add a life event table so we can track different life events for each person, such as their birth, their death, marriages, name changes, and so on. Of course, we will add a nice pretty form for that as well. You can also store location information, a description, notes, documents, and of course link marriages to another person so you can track spouses. Then, we will figure out how to track age at each one of those life events so we will know how old they were when they died, how old they were when they got married, and so on. In Lesson 7, we will make our life events button on the main person form show how many life events they have in the system. Next, we will add children in a list box on the family tree form. This now allows us to navigate up the family tree by clicking on ancestors or down the family tree by clicking on children. It is very difficult to expand the children in a tree view like that because we don't know how many children each person might have. By putting children in a list box, there could be two, there could be 200. Going up the tree, you know how many ancestors people had; each person has a mother and a father, but going down you can't quite tell. I could start down here on Sally, double click on Joe to go up to Joe's record, double click on Biff to go up to Biff, and then go back down the tree. Next, I will show you a better way to store your pictures. Instead of storing them inside the database, we'll create a folder to put all of our pictures in and reference them from inside the database. That makes your database smaller and faster. If your database gets very large, it won't get slow. We'll create a button to select the picture. That button will open up a Windows dialog box and let you browse through your files to pick the correct picture. Then, our database will automatically move that picture to a folder for that person in your file system. This is how your database will keep your pictures organized for you. All of your people will be stored in their own individual person data folders. Later, if we add PDF files or other kinds of documents, they'll go right in that person's folder. Then, we'll link that picture right into the database just like the attachment picture was—only this time it's an external file. In Lesson 10, we'll start building a person list form so we can quickly see all the people in our database. We'll make these people clickable so we can open up their individual person records. We will add search boxes so we can filter our list based on different criteria such as name or gender. Then, we'll add different sort options as well. Then, we'll add birth dates and death dates to it. Of course, what database isn't complete without a main menu to start with? This menu will get a lot bigger in part 2 when we add more features like reporting. Next, we'll add in some code so someone cannot accidentally be selected as their own parent or child. Next, we'll add some code to make sure these little dialog boxes pop up directly over the form that opens them. If I move this form over here and click the button, now it's centered over that form. We'll add some code to hide all these buttons if you go to a blank record. You can't add life events for someone who doesn't exist yet. We'll make our delete button friendlier; we'll put the name in the delete button. If you click on a person with children, it'll give you a warning message: can't delete someone who has children, please delete the children first. Or, if you try to delete someone who doesn't have children, it gives you a more friendly warning message. We'll add some little enhancements. For example, we'll only show that extra person combo box if you click on something like marriage where you can link it to another person. If you click on birth, you don't see that. You can select in the table which of these events you can link to another person. For example, you may want to link job to another person if they're working with someone. Once you have these people set up, you can double click on this person to open up the spouse's record. So that's a pretty comprehensive walkthrough of the genealogy database at the end of part one. I've got lots more planned for part two and beyond. Here's a list of ideas I've got coming up for part two: - The ability to import and export data. This is handy if you want to keep two completely different sets of families. - Additional names like middle name, suffixes, and so on. I want to keep part one simple for the sake of building the database. - Complete contact info and a contact history. Part of the reason for tracking genealogy is to find relatives you might not know existed, and if you find them and they're still alive you might want to save their email address, phone number, current address, and so on. - A lot of genealogy databases I've found have one option to track spouse. Of course, a person might have multiple spouses over the course of a lifetime. We're going to add a list box on the person form so we can see all of their spouses with the current one up top. - You might run into duplicate people so we're going to add a check for that. If you try to enter someone with the same last name, date of birth, and maybe first initial, it will at least give you a warning and say, hey, this person might already be in there. I know I've accidentally done that when entering in my own information. - A person easy setup wizard where it simply asks the name, then the person's parents, then the person's children. That will be good for getting started the first time while using the database. - A printable family tree which you can print out on an 8.5x11 sheet of paper. It will look like our form, the printable tree ancestor form, but it will have more information: birthday, death date, locations, and so on. - Of course, a complete person history showing all of their parents, grandparents, children, and so on. - A life event timeline: this person born, job, married, death, and so on. - Then we'll make a form to search through the life events table. You might want to say, show me everybody born in 1930 or everyone who lived in Buffalo, New York. So those are all upgrades I have planned for part two. After I posted about the genealogy seminar on my website, some of my users suggested some other options. Importing and exporting GED files, for example; those are industry-standard genealogy files. Tracking genetics information, calculating the most distant known ancestor and common ancestors, and building a Google-like search form. So all you have to do is type in Joe Smith and it finds Joe Smith anywhere in the database, at any person level, might be in a related table, anything. If you want to see these upgrades added to this system, then post something in my forums and let me know about it. The more people that express interest, the more likely I will be to add these in the future. Let's talk about what you should know before taking this class. You should definitely have taken my beginner series. You should know all the basics of creating tables, forms, and reports. If not, you will be lost. I strongly recommend you also take Access Expert Levels 1 and 2, where I cover relationships. This database is all about relationships. We will be using a unique kind of relationship called a self-join, where we're relating a table to itself, but still understanding relationships between multiple tables is essential. I also have a relationship seminar, which is very helpful. That's not required, but it is recommended. There will be some Visual Basic in today's seminar. I will show you everything you need step by step to accomplish the goals. However, I do recommend you take my Access Developer 1 course first, at least Level 1, to learn the basics of how to put VB in your Access databases. I will be using some SQL, so my SQL seminar is also recommended. Again, I will show you everything step by step in this seminar. So, you don't have to take these other courses, but they will give you a deeper understanding of the material if you really want to understand how I built this database. If you have questions about the material covered in today's class, please feel free to post them in the Access Forum on my website. You will also find student questions and answers about each video if you scroll down to the bottom of the page you're on right now. You will see previous questions that were asked and the answers to them, plus there is a form where you can post your own questions and comments as well. If you have questions of a non-technical nature such as customer service or sales info, please feel free to email me directly. I do prefer technical questions are posted on the website. So, let's get ready to learn with the Genealogy Seminar Part 1. QuizQ1. What was the original intent of the Genealogy Seminar project?A. To create a short TechHelp video for students B. To build a commercial software product C. To design a printed book for genealogy D. To conduct a live workshop on genealogy Q2. What is a self-join in the context of this seminar? A. A join between two unrelated tables B. A join relating a table to itself to connect people to other people C. A join used for linking forms together D. A join for connecting to external databases Q3. In the initial setup of the genealogy database, how are children related to a person in the form? A. By creating a separate children table B. By adding child IDs to the parent record directly C. By using a subform under each person to track their children D. By only listing children in reports Q4. What feature is added in Lesson 2 regarding navigation between records? A. Search by location B. Clickable fields for mother, father, and child to jump to their record C. Printing a family tree D. Sorting by last name Q5. Why is the main form set to prevent record deletion directly? A. To avoid accidental edits B. To ensure records with children are not deleted before their children C. To improve database speed D. To maintain alphabetical order Q6. What does Lesson 4 add to the genealogy database? A. Payment tracking for each person B. Conditional formatting for gender and an age calculation feature C. Ability to export data to Excel D. Multi-language support Q7. How are attachments such as pictures handled in Lesson 5? A. They are not supported in the database B. All pictures are stored only externally C. Pictures and files can be attached and stored directly in the database D. Only text-based files can be attached Q8. Why is an "unknown" gender added in Lesson 6? A. To support non-human ancestors B. For historical records where gender is not known C. For statistical analysis D. For data validation Q9. What purpose does the "life event" table serve in the database? A. To track only the dates of birth and death B. To record different life events, including marriages, name changes, and more, for each person C. To manage login history for users D. To maintain a list of surviving descendants Q10. How does the improved method of handling pictures make the database more efficient? A. By compressing images before storing them in the database B. By linking to pictures stored in organized folders instead of embedding them C. By disabling photo storage D. By automatically deleting old pictures Q11. What is the main function of the person list form introduced in Lesson 10? A. To display only deceased persons B. To quickly view, search, and open records for all people in the database C. To print family trees directly D. To show only direct ancestors Q12. Which technique is implemented to prevent logic errors when selecting parents or children? A. Validation code that stops someone from being their own parent or child B. Disabling the mother and father fields C. Sorting child records alphabetically D. Using color coding for immediate family Q13. What prerequisite knowledge is strongly recommended before starting this seminar? A. Advanced macros and web integration B. Access Beginner Series and Access Expert Levels 1 and 2 C. Detailed knowledge of network security D. Prior experience with SQL Server only Q14. What option is available for users with non-technical questions? A. Post in the Access Forum B. Email the instructor directly C. Use the delete button in the app D. Register for Part 2 only Q15. What kinds of upgrades are planned for part two of the seminar? A. Importing/exporting data, tracking multiple spouses, printable family trees, and life event timelines B. Real-time collaboration on genealogy records C. Voice recognition input D. Integration with social media platforms Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-B; 7-C; 8-B; 9-B; 10-B; 11-B; 12-A; 13-B; 14-B; 15-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. SummaryToday's video from Access Learning Zone kicks off the Genealogy Seminar Part 1. I am your instructor, Richard Rost.This project started as a simple TechHelp video answering a student question. Typically, I record a short, free video of about five to ten minutes and share it on my website and YouTube channel. Afterward, I often post a members-only extended cut with some advanced tips and extra features. However, this particular project took on a life of its own once I finished the extended cut. I found myself having so much fun with it that it grew into a roughly seven-hour seminar, which has become Genealogy Seminar Part 1. As a result, you may hear me refer to TechHelp videos or members-only extended cuts early on, but soon I realized this project deserved a full seminar approach. Let me give you an overview of what is covered in this comprehensive video course. Lesson 1 starts as a typical free TechHelp video. Here, I lay out the foundational concepts: how to create the necessary tables, set up self-joined relationships, and design forms to track information about people in your genealogy database. I introduce basic database relationships, specifically one-to-many and many-to-many relationships. Then, I explain self-joins, which allow us to relate people to other people within the same table. We begin by building the tables and populating them with sample data. Next, I walk through creating a basic form to enter each person's details: first name, last name, gender, mother, and father—choosing the parents from the same people table. I also show how to include a subform so you can view a person's children right on the same form. This means you'll have mother and father fields at the top and all of the person's children displayed below. Lesson 2 is the members-only extended cut. Generally, this content is available to silver members or higher on YouTube. In this lesson, I enhance the interface by making the mother, father, and child ID fields clickable. Now, double-clicking on a mother's name, for example, will jump straight to her record. I update the subform to automatically fill in the mother or father based on the gender of the current person when you add a child. I also demonstrate how to create a quick-add form for people. Plus buttons are added for mother, father, and child fields, so you can add related people on the fly, speeding up data entry. Next, I show you how to create a custom delete button and disable deletion directly from the main form, preventing accidental deletions of records that still have children. The software will now require deleting all child records first before you can delete a parent. Around this point, after finishing editing the extended cut for TechHelp members, I was enjoying the project so much that I kept expanding it, which is how the Genealogy Seminar Part 1 was born. Moving on to Lesson 3, I begin building the ancestor form, also referred to as the family tree form. I walk you through constructing it step by step. In Lesson 4, I use conditional formatting to color the gender combo boxes, making it visually clear whether the person is male or female. I guide you through adding birth and death dates for each person and introduce a function to calculate their age. This way, if someone is living, you see their current age, and if there's a death date, the program displays their age at death. Lesson 5 explores adding attachments so you can store pictures and documents with each person in the database. I explain how to select image files from your hard drive, attach them to records, and handle various types of attachments like text files, documents, and PDFs. We also integrate profile pictures into the ancestor form, making the family tree more visually appealing. Additionally, I update the ancestor form so that you can double-click on any ancestor to view their record, allowing you to navigate up through the family tree structure. Lesson 6 introduces an unknown gender option. This is particularly useful during genealogy research when you encounter an ancestor and their gender is not known from the records. This way, you are not forced to choose male or female for someone like Chris Watkins from the 1600s if you cannot be certain. Next up is creating a life event table, which records significant milestones such as births, deaths, marriages, name changes, and more. I develop a dedicated form for life events, which can store locations, descriptions, related notes, attached documents, and even link marriages to spouses. I go over how to calculate and display a person's age at each life event so you can track ages at death, marriage, and other milestones. In Lesson 7, I update the life events button on the main person form to indicate the number of life events associated with each person. I also replace the children display in the ancestor form with a list box. This improvement allows you to quickly see and access all of a person's children, regardless of how many they have. It makes navigation more flexible—clicking on ancestors moves you up the tree, while selecting children moves you down it. Expanding family trees can be tricky, as every person can potentially have many children. That's why the list box works better than trying to use a tree view for the children, since the number can vary widely. As you use the new navigation, you can double-click on a person to move up or down the tree. I also introduce a more efficient way of storing pictures. Rather than storing image files directly in the database, which can make the database large and slow, I walk you through setting up a file system folder structure for each person. When you select a picture, the system automatically copies it to that person's folder and links it from the database, keeping everything well-organized and the database performing efficiently. This approach also works for documents or any other files: each person gets their own folder. Lesson 10 is about building a person list form. This gives you a quick way to see everyone in the database, with options to open their detailed records, search by various criteria like name or gender, apply sorting, and display birth and death dates. I also start building a main menu for the application. Part one keeps it simple, but you will see it expand in part two when I add more features, including reporting. To enforce data integrity, I add checks to prevent someone from being set as their own parent or child. I also improve the user experience by making sure dialog boxes appear over the correct forms, hiding buttons when not needed, and tailoring delete prompts depending on whether a person has children. Further, I introduce interface enhancements. For instance, the combo box for linking another person only appears for relevant life events such as marriage, not for events like birth. You choose which event types can be linked to another person. With the relationships set up, double-clicking a person in a linked field jumps you directly to their spouse or other related individual. At the conclusion of part one, the genealogy database is quite robust. Still, I have plenty of additional features planned for part two and beyond, including: - The ability to import and export data. This allows you to maintain multiple separate family sets. - Support for additional person fields such as middle names and suffixes for more complete records. - Storing full contact information and a contact history, which is valuable for locating and following up with living relatives. - Accommodating multiple spouses for individuals, as is often needed in family history, with a list displaying all of a person's spouses, showing the current one first. - Duplicate checking, so if the same name and birth date is entered again, you will get a warning. - A quick person-setup wizard, which guides you through entering names, parents, and children for fast initial data entry. - Features to print family trees in an easy-to-read layout, complete with vital information and locations. - A full person history display, outlining all relationships including parents, grandparents, and children. - A timeline view of a person's life events. - A form to search through life events, allowing for queries such as everyone born in a certain year or a particular location. These are just some of the upgrades I plan to bring in the second part. After announcing this seminar, several users suggested even more ideas: supporting industry-standard GED file import and export, tracking genetic information, identifying the most distant ancestors, calculating common ancestors, and including a Google-style search to quickly find any person anywhere in the database. If there are features that interest you for future enhancements, please post your requests in my forums. The more interest a particular feature gets, the more likely I will add it in a future update. Before starting this seminar, I recommend you are comfortable with the basics of Microsoft Access. Ideally, you should have completed my Beginner series. Knowledge of table, form, and report creation is essential. If you're unfamiliar with relationships, my Access Expert Levels 1 and 2 courses are very important, since the foundation of this database is built on complex relationships, especially self-joins. My Relationships Seminar is also helpful, although it is not strictly required. Some Visual Basic will be used in the seminar. I'll walk you through each step. However, taking my Access Developer 1 course, at least Level 1, will help you understand how to integrate VBA into your projects. I will also demonstrate some SQL, so my SQL Seminar can be a good resource if you'd like to dig deeper. All of these topics are braided together in this project, and while I show you every step along the way, prior experience will help you get more out of the seminar. If you have questions about anything covered in this class, please use the Access Forum on my website to post them. You'll find answers as well as previous student questions and feedback on the same page. For non-technical issues such as billing or sales, you're welcome to email me directly, but I prefer technical questions be posted for the benefit of all students. Let's get started with the Genealogy Seminar Part 1! 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 ListSetting up the genealogy table structureCreating self-joined table relationships Building a person data entry form Adding mother and father selection to forms Creating a subform to display children Setting up clickable mother, father, and child IDs Making mother and father default based on gender Adding a quick add person form with plus buttons Custom delete button with safety checks Building an ancestor family tree form Applying conditional formatting to gender selection Adding birth date and death date fields Calculating age and age at death Attaching files and pictures to person records Selecting and storing profile pictures Displaying documents and PDFs in the database Making ancestors clickable on the family tree Adding unknown gender option Creating a life event table and form Tracking birth, death, marriage, and other life events Storing location and description for life events Linking marriages and spouses in the database Calculating age for each life event Showing life event count on person form Adding children navigation in a list box on the tree form Navigating up and down the family tree Storing pictures as external files in organized folders Creating a picture selection and file move button Referencing external files instead of attachments Building a searchable person list form Adding search and sort features to person list Filtering people by name or gender Adding birth and death dates to person list Creating a main menu form Preventing selection of self as parent or child Centering dialog boxes over parent form Hiding action buttons on blank person records Friendly, dynamic delete warning messages Conditionally displaying combo boxes based on event type Linking specific events to other people in forms Double clicking to open spouse records from events |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: access genealogy seminar ancestry family tree Page Tag: whatsnew PermaLink How To Build a Complete Genealogy Family Tree Database With Self-Joins and Forms in Microsoft Access |
