Association Series
By Alex Hedley
3 years ago
Microsoft Access Association Database Series
Lessons
Members
Members we'll learn how to open and edit family members without using the list items edit form button. We'll create our own button and the events that go with it which I think is much better. The list item edit form button is unreliable.
Members will learn how to double click on the combo box to open up the helper type form to the specific helper type ID, make any changes such as adding a new value, and then when they close that form it will update the combo box. Well then add a password and make the password sticky so it remembers that you're a manager for the rest of this Access session.
Members will learn how to open the helper form from a control on the subform and have it return the value entered or selected. Then we will also display the person's age next to their date of birth.
Info
In this series of videos, we will be building a database for tracking association members in Microsoft Access. This database will be suitable for a wide range of organizations, including churches, educational institutions, social clubs, and more. If there are specific features or types of databases that you would like to see, please let me know in the comments.
This is Part 2 of my Microsoft Access Association Database. In this video we will continue laying out the tables that we need and we'll layout the forms for the database.
This is Part 3 of my Microsoft Access Association Database. In this video we will modify the TechHelp template that I use for most of my databases. We'll change the customer table over to a person table and see what other items in the database that effects that we have to then change. We'll build an update query to set some default values for existing records. We'll see how to do a global find and replace in VBA code. And we'll build the rest of the tables in the database which will be easier now that we have a great road map to follow from the first couple of lessons.
This is Part 4 of my Microsoft Access Association Database. We will convert the old customer form over to the new person form and add whatever fields we have to. We'll learn how to make Control Tip Text and use the Locked property. We will add a family combo box to select which family this person belongs to. We will create a family form with a combo box to select the head person of that family. We'll modify the row source of the combo box to only show people in that family. This will involve changing the SQL statement and using an OnCurrent event in the form.
This is Part 5 of my Microsoft Access Association Database. In this video we will build the family member list which shows a list of the family members in that family. This will be a list box on the family form. We will create a list items edit form button to add or remove members. Then we will make a family list form much like the person list form and a button to open that from the main menu. Then we'll make it so you can jump to the family from the person form and vice versa.
This is Part 6 of my Microsoft Access Association Database. In today's video we're going to work on the helper form and the helper type form. These allow you to create lists of lists so you don't need a million tiny tables in your database for lists of things like gender, occupation, ethnicity, and so on.
This is Part 7 of my Microsoft Access Association Database. In this video we're going to build the combo boxes on our main forms to select helper data based on the specific type of data we need to pick for that combo box.
This is Part 8 of my Microsoft Access Association Database. IIn today's video we are going to build the demographics form and show you how to switch between the context subform and the demographics sub form by clicking on a label that will change the subform's SourceObject property.
This is Part 9 of my Microsoft Access Association Database. In today's video we are going to make a major revision to one of the features of the database. We're going to replace a single family that a person can belong to with a group table that will allow us to put people into multiple groups including families, companies, interests, and more.
This is Part 10 of my Microsoft Access Association Database. In today's video we are going to make a series of nested forms so you can see the group types then the groups in each type and then the people in each group. We're going to do this using nested continuous sub forms which are really cool.
This is Part 11 of my Microsoft Access Association Database. In today's video we are going to add a subform to select groups (including families, companies, interests, and so on) from the main person form. Will also add a combo box to filter the group type in that subform.
Pre-Requisites
Links
FastTips
What's Next
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, associations, groups, schools, memberships, churches, temples, donations
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, associations, groups, schools, memberships, churches, temples, donations, table design, form layout
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Change CustomerT to PersonT, Update Query, Global Find & Replace in VBA Code, Build Rest of Tables
FastTips Access Fast Tips Change CustomerT to PersonT, Update Query, Global Find & Replace in VBA Code, Build Rest of Tables
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Person Form, Control Tip Text, Locked, Family Combo, Family Form, Head of Family Combo, OnCurrent Event, Change SQL RowSource
FastTips Access Fast Tips Person Form, Control Tip Text, Locked, Family Combo, Family Form, Head of Family Combo, OnCurrent Event, Change SQL RowSource
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Family Member List, List Items Edit Form, Family List Form
FastTips Access Fast Tips Family Member List, List Items Edit Form, Family List Form
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Helper Type Form, Helper Form, Lists of Lists
FastTips Access Fast Tips Helper Type Form, Helper Form, Lists of Lists
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, helper type combo boxes, password
FastTips Access Fast Tips helper type combo boxes, password
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Demographic Form, 1-to-1 Relationship, More Helper Combos, Switch Subforms, SourceObject
FastTips Access Fast Tips Demographic Form, 1-to-1 Relationship, More Helper Combos, Switch Subforms, SourceObject
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, major revision, putting people into groups, families, companies, interests, many to many relationships, m2m
FastTips Access Fast Tips major revision, putting people into groups, families, companies, interests, many to many relationships, m2m
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, group type form, group form, put people into groups, nested subforms, nested continuous forms
FastTips Access Fast Tips group type form, group form, put people into groups, nested subforms, nested continuous forms
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, group subform on person form
FastTips Access Fast Tips group subform on person form
Intro In this video, we will begin building a membership tracking database from scratch in Microsoft Access, designed for any type of association, club, or group. I'll walk you through my process for planning out tables, fields, and relationships using Excel, with features to track people, families, demographic details, memberships, groups, events, attendance, dues, and donations. You'll see how to organize your initial blueprint for a flexible, user-driven database framework, and learn tips for structuring your data efficiently before actually constructing the database in Access. This is part 1.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
This is going to be another Fast Tips series, just like I did with the follow-up series earlier. We are going to build a new type of database for tracking association members, whether you are dealing with a golf club, an educational institution, or a church. This will be a database for you.
I decided to do another database in steps. In other words, I will show you step one, step two, step three as a series of videos. I did something similar a couple of weeks ago with my follow-ups database. If you remember watching this, I added follow-ups to my TechHelp free template with the customers and the contacts and all that. It was like a seven-part series. It got lots of positive feedback, and tons of people emailed me asking if I could build something similar.
So I am going to do another one of these series where I build a database step by step, give you guys a little bit of time, and then we will add more based on your feedback. Lots of people ask me to show more of my process for building new databases.
In my Access Beginner Level 1 class, which is free, I give you a brief overview of how to get started building a database. But I really want to get the new user right into building with Access. We talk about a couple of things for how to set up your tables and your fields and all that, but we really just want to get into building your customer database fast.
In this database, I want to show you more of my process for how I lay out the different tables and the fields and the relationships before we even start building the database. What I have decided to do, based on the tons of emails I get, is an association database. What does that mean? I tried to come up with a nice, generic term for any kind of membership database. It could be a homeowners association, a country club, a boat club, a golf club, whatever kind of club you are in.
Church membership database. Lots and lots of people ask me if I have anything for building a church membership database. That is a big one. Any kind of a social club, a gaming group, educational institutions, if you want to track student enrollment, rec centers, all kinds of stuff. Any kind of membership database, whether it is for pay or for a donation basis, like a church, whatever you want to do, this database will be able to handle it.
What is included? Most of this database is going to be absolutely free. Feel free to build along with me at home or just watch and enjoy. I know I am super entertaining. There will be some extended cuts for members where I add some cool nifty features or things that are heavily involved with VBA programming. There are going to be some extra bells and whistles, but the core of the database will work just fine, and it will be free. You are going to have to build it yourself, but Gold members can download the databases that I build from my website.
I do, however, recommend you build it yourself if you want to learn what I am doing. The best way you can learn is to do it and make some mistakes. Try it yourself. Do not just download my copy and work with that. When we are all done and finished with this, it will be available for purchase as a template on my website in case you do not want to become a member, but you want to copy the database and get the version that I build with all my extra bells and whistles.
But the big takeaway is that this database is going to be driven by your feedback. I am going to release a couple of pieces here and there over the next couple of weeks, and each time I do, I am going to read your comments and your feedback and what you guys are talking about. If there is a feature that you want to see me add, post it in the comments, and if enough people are interested and I think it is a good idea, then I will go ahead and add it. Some of you guys post some crazy requests. I am not even going to get into that.
Also, let me know if there are any other types of databases that you want to see me build in the future. If this series is popular and a lot of people watch it and comment on it, then I will do more of this kind of stuff.
All right, so let us get started. The first thing that you need to do whenever you are sitting down to build any database, before you even touch the computer, before your hands are even on your mouse, you need to make a list of what features you want the database to have.
I think very generically, in broad terms. For example, I am making an association database. What features? Well, I want to track people, and people can be part of families. I want to track demographic information about each person. Then I want to have different memberships. Now, even if you are not a membership-based organization, for example, let us say you are a church and you do not really have memberships, you can use memberships to track different groups if you have different outreach groups or something. So a membership is just a generic term for a group or something within the organization that you want to be able to put people in different categories, let us say. But you can also have that for your membership dues. You can track your membership fees and stuff like that.
We also want to have events and track attendance. You want to know who is there at your different events, and we are going to show you some cool tricks for automating that.
Then, of course, tracking dues, whether or not you have them. Again, you can have people in different membership groups that are not necessarily being charged a membership fee, but you also want to be able to track donations that they give. Then we will be able to track those donations into different funds. Maybe you have got a building fund or an outreach fund, something like that.
Now that you know what features your database is going to have, what generic things you are going to be tracking, now it is time to start making a specific list of the tables that you think you will need. Try to list them all if you can. Run through your brain, get all the tables. It is easier if you have a good roadmap up front of all the tables you need. Sure, you are going to add some later, or you might decide you might not need some, and get rid of some ones that you started with.
Like, I was going to do a different group table. Then I thought to myself, well, instead of having memberships and then having groups, you could just have a membership where you do not charge with a zero. Zero dues that are being charged, for example, and that would just be a group. So there is no sense in reinventing the wheel if something can fit into more than one thing.
For example, I have seen some people build databases like this. Let us take a church database for example, where you have got the members of the church, you have got the administrators, people who work in the office, you have got the clergy, pastors and whatever. There is no need to make three separate tables to store that. They are all people. They are all the same kind of thing, so we will have one person table. Then in that person table, we will have a person type. What type of person are they? Now, are they a member, clergy, a group leader, whatever.
All right, membership, membership type, you get different types of memberships, whereas the actual membership table will track the specific membership information for that person. We have got events and attendance, different tables, a donations table. We are going to do contacts and invoices. We are going to piggyback this database on my existing TechHelp template, because a lot of the groundwork is already done as far as formatting the forms and stuff like that. We will use the contacts. We are just tracking notes with the person. We can also use it for correspondence. We are going to use the invoicing system that I have already built to send their monthly dues or if they have dues, or to send them a receipt for a donation, either one.
So if you have not watched any of my other TechHelp videos yet, if this is your first time with me, welcome. But make sure you go watch my blank template video. This shows you how I build my basic template that I start all of my databases with. It has got all my options and stuff that I have in there.
Then go watch my customer contacts database. This is where I actually track the contacts for each customer, and there are some good nuggets of information in there about using subforms and continuous forms and things you should know. We are going to do a lot in this database with this subform concept, and you will see what I mean as we get to it.
Eventually, go watch this invoicing database. We are going to get to this in a little while. We are not going to start off with this right away, but as we get to the membership and the donations tracking, we want to be able to send out bills, invoices, or receipts. We are going to use this stuff, so go watch this too.
Now that you know what tables your database is going to need, I like to determine what fields each table needs next. Excuse me, a general idea of what is going to be stored in each table. I really like to use Excel for this. Excel is perfect for laying out your tables and getting an idea of your relationships.
Let us switch over to Excel. Excel is just great for laying stuff out. I used this when we just got a new house recently, and I used Excel to do the floor plan of how I wanted to lay the furniture out. You just do this, shrink this down so it is about like a square, and now you have basically graph paper. I use Excel for all kinds of things, none of which really for storing data. That is what Access is for, unless it is something small. You can start off small projects in Excel and then graduate them to Access. It gives you an idea of what kind of stuff you are going to need later on.
So the first thing we need, let me zoom in a little bit here, the first thing we are going to need is our person table, PersonT. I like to keep all my table names and my field names singular if I can. There are some exceptions, like notes. I have just been using notes forever and note does not really do it for me.
Pretty much every table is going to have an autonumber primary key ID field. So PersonID, and if I forget to put one down in the future, it is going to have it. When we get to building the table, you will know that. Now each person can be assigned to a person type. Whether you want to do this as a one-to-many relationship or a many-to-many relationship is up to you. In other words, do you have people that fall into multiple types in your organization? Usually, you have got, for church for example, and that is the example I am probably going to come back to a lot, you have got your clergy and you have got your members. Very seldom are they both the same thing. They might be. I am going to start the database off assuming that each person can be assigned to one type, so it is a one-to-many relationship, much easier to set up. If you guys want to see me do it many-to-many where a person can be in multiple types, then that is up to you. We are going to do memberships as many-to-many. We will talk about that in a minute, that way a person can have multiple memberships or groups that they are in.
All right, so in addition to the primary key, we need a PersonTypeID, and the person type is going to be in a different table. So we will move down here, PersonType table.
Now we need the person's name, address, and yes, when I am prototyping here in Excel, I know that name is going to involve first name, middle initial, last name, or whatever other parts you want. Those should be separate fields, just like address. We have got street address, city, state, zip code. But for the prototyping part in Excel, I know what this means. Just like I am going to put in here phone, so do not forget phone. This does not have to be perfect. This does not have to be exactly like you are going to build it in Access. This is mostly just to give you a blueprint, so when we do get to Access, we know exactly what we have got going on. Unless I say otherwise, pretty much every table is going to get a notes field and a date created field. It is just interesting stuff to have. It is good stuff to have on a build. Pretty much every table is going to have that and an ID field.
Now for people, for persons, there is a bunch of stuff that you can indicate that they are in or not, and these are going to be yes/no fields. I have got isActive, are they an active person? Like when someone dies, for example, you do not want to delete the record. You want to have their information to keep it for whatever accounting you might need in the future, for example. So you do not want to delete them. I have got a whole video on not deleting data. I will put a link to that down below. But if you mark them inactive, they are not going to show up on all your lists. Like when you are doing attendance, for example, you do not want to see people who are not active in that big long list of all those people.
IsRegularAttendee, something I came up with. All right, when we do our attendance for stuff, you know how every group has people who are almost always there, like they are every week, and then there are people who come like twice a year? So this is just something for you to mark them as a regular attendee, because we are going to have a feature later where we can say, OK, I want to just automatically add the regular attendees when I take attendance, and then I can mark off if one of them is missing or not. That is just a neat little thing I thought we would add.
Of course, the benefit of building your own Access database is you get to pick what features you want. If you do not think you will use that, do not put it in there. I do not care. It is your database. Put the Legos together however you want.
Now we have got an address. Oh, we have got email address. Do not forget email address. Let us put that up here too. I almost forgot about email. Of course, this brings up the age-old question. What about multiple email addresses? Multiple phone numbers, multiple addresses. I have got a whole video on these. We are going to keep it simple for now and just do one each. I think for most group-based databases, one email, one phone, one address. If you want to add more, add more. Again, I have got a whole video on the subject of tracking multiples. I will put a link down below in the link section to my multiple phone numbers video. You can watch that if you want to add more than one.
So anyway, we have got their email address. We have got their regular address.
We are going to have different mailing lists. Now, you can set up a separate table just for mailing lists, but I think for most groups, we are going to have an isOnEmailList and isOnMailingList. That will indicate you have got people who you might want to send out your weekly email blasts to, like, this is what we have got coming up this week, make sure you do not miss it. Then you might have your quarterly or your annual, maybe your Christmas mailing list, whatever it is. So is this person actually going to get a physical thing in the mail?
In a minute, we are going to show you how we are going to do family table too, and you might have multiple people grouped in a family. But only one of those people is on the mailing list. All five of them might be on the email list. You do not want to send an email blast to everybody in the family, sure, why not. It does not cost anything, oh, anything much, maybe 0.001, whatever it is. But only one person in that family might be on the actual physical mailing list.
Also, isInDirectory. You might have people who do and do not want to be printed in your directory. I have got a whole different video on doing a directory booklet, which we will talk about when we get to it.
Finally, I am just going to put in here payment info. Again, this falls into the one-or-many thing. If you are tracking dues, or if they do pay with donations on a regular basis, you could store whatever payment information you want. Do they pay by check, PayPal, Venmo, credit card number, that is up to you. I am just going to leave it to payment info for now. We will discuss that in more detail a little bit later on.
Do not go too crazy about these, but I always find when I sit down to do this part of it, I always think of extra things I want to add. I got the email list thing and the mailing list thing, and I always come up with a million different things to add to the database while I am doing this part of it. Better to add this stuff now than when we start to actually get to building the forms.
I am going to do something different with this in just a minute, but for now, just think about what you want with this right here.
We need just a list of what kind of person this is based on your group, your association. What are the different types of people you have got? You might have members, mentors, leaders, moderators, admins, clergy, whatever yours is. Managers and non-managers, it does not matter. This basically is just going to be a list of things, an ID. So we would have a PersonTypeID, and then maybe a description and then maybe some notes. That is pretty much it.
So keep that in mind. We are going to come back to this in a minute. In fact, I like to take these things and color them. Bring this guy up here. I like to use the cells. Let us go with the blue. People are blue. I am already going to have to zoom out a little bit. It is too small to see. Let us go back. Just collapse that. Need more space.
So we have got PersonTypeT just sitting there. Very simple, little, tiny table. Nothing really going on.
Next up from my list, what do we have? Well, we covered people. Let us do the family table next. We have got family, then demographics.
So I am going to put FamilyT here. Of course, we have got a FamilyID, a description, and then I am going to put in here a HeadPersonID. What does this mean? Well, this is a family table that a person may or may not belong to, because you can have just individuals, but you might want to group them together to know who is all together in the same family. So that is what the FamilyT is going to be for. It is going to have FamilyID, a description, the Smith family, whatever you want to call them, I do not care, a head person indicating who the head of that household is, head of the group, head of the family, whatever.
Now we need a way to track what family each person is in. That is going to go right in here, and I like to keep all the IDs up top. So let me copy and paste here so we get the same style. FamilyID. Now this is going to be optional. You do not necessarily have to be in a family, but if you are, there it is. This is how we will track people who are related together.
I am going to take this and just colorize this guy here. I try to keep the stuff that is kind of related together to kind of the same color. So we will go a little bit darker blue here. Maybe that. So these are kind of related. If you want to draw little lines on here, you can. I do not usually. I just kind of know FamilyID, FamilyID. HeadPersonID is a PersonID. Could you put just PersonID here? Sure. But then it gets kind of confusing, but again, it is up to you, because you might want to add other people in the family later on.
Billing person. I do this with sales forces, for example. A company might have a billing contact, sales contact, a service contact. It is okay to name them differently. Just remember if the field is named differently, then queries are not going to make those automatic ad hoc relationships for you. You have to make those yourself. That is not a big deal.
All right. So next up is demographics. Actually, we forgot, just so we do not forget every time, we need notes and we need a date created for every table, almost every table. That is handy to have. You want to know when the family table is created, when the family record is created.
DemographicID, PersonID, so we know who this is talking about, DOB is fine. Now I have got a bunch of things I want to track, and again, this is completely dependent upon your organization. Some people want to track more, some people want to track less, some people do not want demographics at all. If that is the case, do not build this table. You might want to track their gender. Now, gender, especially as of late, is something that is, you could be very old-school definition with this, and you have got male and female, that is it. You might be more progressive and have other things like transgender or whatever you want to have in there. That is completely up to you.
But I like to make gender a list of options. So that tells me I need another table. So I need a GenderT with a GenderID, a description, and maybe some notes. All right, so this will not be a GenderID.
We need a marital status, which again is a small list of options. So now we need a MaritalStatus table with a MaritalStatusID, a description, and some notes.
You see what I am getting at here. We have got a ton of these little tables we are going to need for that, for occupation, for education level, maybe ethnicity. Do I want one, two, three, four, five, six little tiny tables in my database that all they do is store a simple list of information with really no other data? No.
That is why I invented something a while back called a helper table. If you have watched my other videos on this, or you have seen my ABCD videos, I use the helper table a lot because it gets rid of a lot of unnecessary small tiny tables. I have got a whole video on it. If you want, watch this video before we start getting into the next couple lessons so you get a better idea for how this works. I am going to go through it a little bit in this series, but go watch this. I will put a link to this down below as well. It is a very handy video.
So we do not need all of these things. I am just going to make a list of what we need to put in the helper table. Let us get rid of this and put it over here.
We need a helper type table, which is basically going to be the stuff that we need over here. So we need our person table, person type, our gender, basically all this stuff here, copy and paste, that is all going in the helper table. You can see how it cleans up the database a lot. These would all be IDs. OccupationID, EducationLevelID, EthnicityID, and those will all point to this table over here. There is some typeID, gender, you are probably thinking this guy is crazy. What is he doing? You will see when we get to it. Watch the helper type video on this.
Let us make this one. Demographics can be colored slightly differently.
We are already getting over 20 minutes. I am going to try and keep these to like 10 to 15 minutes per part, per lesson. I do not want to go too long on each one, but I know there is a lot of setup and a lot of description in here, so we still have some more stuff to talk about. We will get to it in the next level. We have to talk about the event table, the attendance table, the donation table, the contact table, which we already have built on the other database, the order table, and some other related stuff there. Then we will get into actually starting to build these tables.
So there you go. There is part one. We are going to be doing more with this in part two, and we are going to actually start getting to building some of this stuff in Access.
Go watch some of those other videos that I pointed to if you have not already. That is your homework. Already, based on what you have seen, throw some ideas at me. Tell me what you want to see coming up in the next several parts. It is better to get these ideas now before I build too much of the database. The more ideas you have up front, the easier it is to build them in later as opposed to going back. It is like building a house. You want to get the foundation built right before you start putting stuff on top of it, because if you have to go back and change the foundation, it is timely and costly. Computers are not that bad as far as that goes. You do not have to dig up concrete and stuff.
So tell me what you think, and I will see you soon with part two.Quiz Q1. What is the main focus of the new database series described in the video?
A. Building a database for tracking sales leads
B. Creating a database for tracking software bugs
C. Building a step-by-step association membership tracking database
D. Constructing a database for inventory management
Q2. Why does Richard recommend building the database yourself rather than downloading his copy?
A. Because the download link is not available
B. To better learn the building process by making mistakes and understanding each step
C. Downloading is restricted to only Platinum members
D. The downloaded version is not compatible with most computers
Q3. What should you do before you start creating tables in Access, according to the video?
A. Begin adding reports in Access first
B. Immediately start entering sample records
C. Make a list of the features you want your database to have
D. Download pre-built templates from the website
Q4. What types of organizations could use the association database being built in this series?
A. Only country clubs and golf clubs
B. Only churches and educational institutions
C. Any type of group, such as social clubs, churches, schools, or rec centers
D. Only for-profit organizations
Q5. What is the suggested way to organize people types (like member, clergy, admin) in the database?
A. Create a separate table for each type
B. Store all in a single people table with a "person type" field
C. Use only one table with no labels
D. Make a "type table" for each type of organization
Q6. In Excel, when planning tables, why does Richard recommend using singular names for tables and fields?
A. Because plural names are not allowed in Access
B. To make SQL queries more complicated
C. For consistency and clarity in naming conventions
D. To avoid running out of characters
Q7. Why does Richard prefer adding note and date created fields to most tables?
A. To slow down database performance
B. To have useful information such as when a record was created and any supplementary notes
C. Because Access requires those fields by default
D. They are needed for sorting records alphabetically
Q8. Why is it preferable to mark records as inactive rather than delete them when someone is no longer active?
A. To free up space in the database
B. Because deleting records permanently removes important historical data
C. Deleting is not possible in Access
D. Inactive records can be automatically backed up
Q9. What is a "helper table" as described in this video?
A. A table used only by the instructor
B. A consolidated lookup table used to manage options for fields like gender, marital status, occupation, etc.
C. A table that stores user passwords
D. A table that stores temporary calculation results
Q10. What is the purpose of the Family table (FamilyT) in the association database?
A. To store only individual contact information
B. To group people together who belong to the same family or household
C. To track donations from families only
D. To link events to individual members
Q11. According to Richard, what is the benefit of planning your database tables and fields using Excel?
A. It eliminates the need for Access
B. It is perfect for prototyping and gives you an overview of relationships before building in Access
C. Excel is necessary for all data entry
D. It converts Excel formulas to Access automatically
Q12. How does Richard suggest handling attributes like multiple email addresses or phone numbers for each person initially?
A. Allow unlimited emails and phone numbers per person from the start
B. Only allow one of each and expand later if needed
C. Do not track emails or phones in the database
D. Store all emails and phones in the notes field
Q13. What is the role of the feedback from viewers in developing this series?
A. Feedback is ignored in the process
B. It is used to decide which features and improvements will be added in future parts
C. Only negative feedback is accepted
D. Feedback is required before watching the next video
Q14. What should you consider including for every table you design, according to the video?
A. Images and file attachments
B. Autonumber primary key ID, notes, and date created fields
C. Only the table name
D. Calculated total fields
Q15. What analogy does Richard use for planning the tables and features before building the database?
A. Like setting up a meeting schedule
B. Like building a house – create a good foundation first before adding more features
C. Like planting a garden – let things grow naturally
D. Like writing a story – start from the ending
Answers: 1-C; 2-B; 3-C; 4-C; 5-B; 6-C; 7-B; 8-B; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B; 15-B
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 building an association or membership tracking database in Microsoft Access. This will be especially useful if you work with any kind of organization that maintains a membership list, whether it is a club, a social group, a church, or even an educational institution.
I have decided to create this database as a step-by-step series, similar to the approach I took with the follow-up database I shared recently. That earlier series received very positive feedback, and a lot of people emailed me asking for another lesson series that takes you through the database-building process incrementally. Many viewers requested that I show more of the actual process behind designing a new Access database.
In my free Access Beginner Level 1 class, I cover the basics of getting started in Access, such as setting up tables and fields, but now I want to share the practical, in-depth approach I use for actually structuring a new database. The goal is to make it easier to understand not only how to create your own database, but also how to plan one effectively before you even start using Access itself.
For this association database, my intention is to create something flexible enough to apply to any kind of group or organization that has members, regardless of whether those memberships are paid, free, or based on donations. For example, this could function as a homeowners' association database, a country club or golf club database, a platform for monitoring student groups, or for managing church membership lists. If you need to keep tabs on who is part of what group, this database will be adaptable for your needs.
Most of this database series will be freely available to everyone. You can follow along with me as I build it, learning each step as I go. There will also be some extended cut features available to paid members, especially covering advanced features or more involved VBA programming, but the base functionality will be completely free. While Gold members will be able to download copies of the database, I recommend building your own as you follow the lessons. You will get a lot more benefit from constructing it yourself rather than working just with my finished file. When the full template is finished, it will also be available for purchase on my website for anyone who is not a member but wants the completed version with all the enhancements.
The other important aspect of this project is that your feedback will shape its direction. I will release each section gradually and read your comments and suggestions. If several people want a specific feature and I think it is practical or useful, I will work it into the next part. So if you have ideas or additional database types you want covered in future videos, let me know in the comments, and I will take those requests into consideration for future lessons.
Approaching database design methodically is important. Before doing anything on the computer, I always begin by writing down a list of features I want the database to handle. Start by thinking in broad, general terms. For an association database, I know I will need to track people and families, collect demographic information, manage various membership types, record events and attendance, handle dues, and track donations.
Memberships are worth considering broadly. Even if you do not operate a formal membership structure—for instance, if your organization is a church—you can still use memberships to track different groups or teams, or different categories of activity within the organization. Memberships simply help you sort people into meaningful categories. The database will also keep track of member fees, donations, and contributions to different funds, such as building or outreach funds.
Once you have a clear idea of what features you need, the next planning step is to list the tables that your database should contain. Get as complete a list as you can at the beginning, but do not worry if you end up adding or removing tables later as the project evolves.
When considering how to model your data, avoid unnecessary duplication. For example, in a church database, you might have members, clergy, and administrators. There is no need to create three separate tables for essentially the same kind of data. Instead, use a single people table, and simply include a field that specifies the person's role or type.
Memberships will probably be stored in a separate table linked to members, with another table to manage membership types. For events and attendance, there will be appropriate tables for tracking who attended what, as well as tables for handling donations, contacts, and invoices. I plan to leverage work I have already done in my TechHelp free template to make use of proven forms and structures, and the existing contacts and invoicing systems will provide a solid foundation for those parts of this project.
If you are new to my videos, I suggest starting by watching my blank template tutorial. This will introduce you to the basic Access template I use as a starting point, along with the various options and customization tricks I frequently apply. Beyond that, it is helpful to watch my customer contacts and invoicing database lessons, as key concepts like using subforms and continuous forms will show up again in this association database.
After defining the required tables, the next step is to button down what fields each table will need. Here, I often rely on Excel as a handy tool for laying out tables and fields before diving into the actual Access database. Excel is great for mapping out the data structures and visualizing table relationships, much like working with graph paper.
For the people table (PersonT), I keep naming conventions singular, whenever possible, for clarity. Each table will need a primary key field, usually an autonumber. In the PersonT table, I will include a PersonTypeID field to categorize each individual according to their role within the organization. For now, I am assuming each person will only belong to one type, keeping the relationship simple as one-to-many. If there is enough demand, I might expand this part later to allow multiple types per person.
Other fields to include are the usual name and address details, phone, email, and so forth. For this prototyping step, details like name, address, and phone can be indicated in general terms. In Access, each part (such as first name, last name, street, city, etc.) will eventually become separate fields. Nearly every table will also get a notes field and a date-created field.
Boolean flags like isActive and isRegularAttendee are useful for managing active versus inactive people, especially for things like attendance lists. Marking someone inactive allows you to keep historical records while avoiding clutter in current lists. You might want an isOnEmailList flag to manage who receives newsletters or event notifications electronically, and an isOnMailingList flag for those who get print communications. The isInDirectory field determines whether someone appears in a directory that you might publish or distribute to members.
Payment information and similar details can also be included, depending on your needs. Consider whether you need to account for different payment types or recurring transactions, and plan accordingly.
Moving on to the PersonTypeT table, this will simply store the list of person types relevant to your organization: members, mentors, leaders, clergy, and so on. This table is straightforward, generally requiring just an ID and a description, perhaps with a notes field.
Next is the FamilyT table, which allows you to group people into families or households. It includes fields for identifying the family, providing a description, and linking to the family's primary head or contact. Each person record may optionally link back to a family, allowing you to keep track of who belongs together.
In many organizations, you will want to collect demographic information about your members, such as date of birth, gender, marital status, occupation, education level, and ethnicity. Typically, you would create a separate table for each of these look-up values, but too many tiny tables can clutter your design. Instead, I use a helper table approach, which consolidates these kinds of lists into a single table. This makes the design more streamlined, and I recommend watching my video on helper tables for more details.
At this stage, I keep track of my progress in Excel, using colors to group related information visually. This helps keep the planning process organized before migrating the design to Access.
There are still other tables and features we need to cover, such as the event and attendance tables, donation tracking, contacts, and orders. Much of that groundwork is already established in my earlier TechHelp template, but we will review and expand these areas as needed. In future lessons, I will walk through setting up each part in Access.
Before we get to building tables, take some time to think about what information your organization needs and what features would be useful for your members. Now is the best opportunity to suggest features or ideas, as adding them during the design phase is much easier than retrofitting them after the database is built. Think of this as building a solid house foundation that supports everything else.
This covers part one of the association database series. In the next part, we will start actually building the structures in Access itself.
For detailed, step-by-step instructions on everything discussed here, you can find a complete video tutorial on my website at the link below. Live long and prosper, my friends.Topic List Overview of association and membership databases
Planning database features for different organizations
Identifying main tables needed for a membership database
Laying out table and field structure using Excel
Defining the Person table with core fields
Using yes/no fields for activity and lists in Person table
Setting up a PersonType table for membership roles
Planning families and grouping people in a Family table
Connecting families to people with optional FamilyID
Demographics tracking and related child tables
Using a helper table to avoid redundant lookup tables
Establishing relationships between tables
Prototype planning in Excel before Access implementation
Identifying and labeling table primary keys
Preparing tables for notes and created date fields
Determining mailing list and directory tracking fields
Designing for one-to-many vs. many-to-many relationships
Outlining preparation steps before building in Access |
