Association 2
By Richard Rost
3 years ago
Microsoft Access Association Database, Part 2
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.
Pre-Requisites
Links
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, table design, form layout
Subscribe to Association 2
Get notifications when this page is updated
Intro In this video, we continue building the association database in Microsoft Access by finishing the table layout and starting to plan out the forms. We'll cover essential tables such as events, attendance (including discussions of many-to-many relationships), donations, timelines for tracking personal life events, and membership types with dues and frequencies. You'll also see how to use helper tables to keep your design efficient, and begin organizing forms and subforms for managing people, families, events, and related data. Finally, we'll talk about using a bit of VBA to dynamically swap subforms within your forms. This is part 2.Transcript Today we're continuing on with our association database. This is part two. We're going to continue laying out our tables and we're going to start laying out the forms.
So, continuing on with laying out our tables. We've got our helper table over there. These are the ones we forgot about earlier, so let's make room for them.
We have events or event table. This is going to be certain events you have coming up for the church, the organization, or whatever. You have an event type ID, which can come over here. Event type. Then we've got a description. You could do start date and end date if you want to, or just a single date - kind of up to you. Some events can be multi-day, like if you have a conference, and some are just a single day, like if you're hosting a wedding or similar event. So, I'm going to put start date and end date. A location. Again, you could have a separate location table if you want to.
Whether you want to make a separate location table that's got a full list of addresses and all that stuff, that's up to you. I'm just going to put it in the helper table for now - North Campus, South Campus, Main Street Office, that kind of stuff. I think that works just fine in the helper table for now. You can always expand these things later.
That's probably enough for attendance or events. The next one is attendance. Attendance is basically going to be a junction table with a many-to-many relationship between people (the person table) and the event table. So, we're going to have attendance.
Is it attendance or attendence? I always looked that up, spell check wise. It's attendance. I can remember that. You dance at the attendance. Attendance to the dance. I guess because I say it attendance. Right, attendance. It's attendance. Okay.
I pride myself on my spelling and my grammar. Even though, if you get an email from me, it might not have the best spelling and grammar because I use voice dictation over everything. We all know how that is on our phones.
So, this will have a person ID in it and an event ID. It's up to you if you want to have any other information in here, like if you charge for these things, how much has been paid, and all that kind of stuff - anything related to that instance of the person at that event. Like if you charge for most of your events, if it is paid, that kind of stuff. I'm just going to leave this blank for now. Maybe we'll fill this in with some more details later, like a status or something.
All right, let's color this in. Let's make these, let's do brief attendance and events. Do you like that? These can be a little wider and these can actually be narrower like that. I can do something like this. Okay, hold these guys.
Yes, this is something that I actually do when I sit down and plan out a new database.
That's that. Our donation table. We've got donations that we accept. Person ID, the amount, the fund that it's going into. Again, your fund can be over here. Like I know a lot of churches will have a building fund, tithes, or whatever other fund - you know, send the kids to camp fund, whatever. If they want to donate to a specific fund, or you can have just a general fund - whatever you want to do.
Of course, on all these, don't forget your notes, your date created, that kind of stuff. If you want to have payment info in here, that's fine. You can make that a payment type ID. That, again, can be a helper type table field - is it cash, check, or whatever.
If you want to have payment details in here, that's fine. If you want to actually store credit card numbers or whatever, if you're doing membership fees and you're storing their credit card information, you can put those fields in here - the credit card number, expiration date, that kind of stuff. Of course, check with your regulations for whatever state or country you're in as far as the rules and laws of storing credit card information.
So, donations - should have made donations green. Let's do yellow for that.
Now, the contact table and the order table, I'm just going to set here because we already have those built. Let's probably color this stuff over here.
Based on the feedback that I've gotten from a lot of you since part one came out, a lot of people want to see some kind of life event thing. In my genealogy database, a life event is something like baptism, wedding, funeral. You can also do them for any kind of regular events too, like initiation date or the date they graduated - any kind of event that's tied to the person but not like an association event. It's a person's event.
I want to call this a timeline. Let's call this the timeline. This would be for a person, so it's going to be a person ID, then a timeline type ID. That'll be a list that you'll keep over here - funerals, weddings, communions, and other kinds of things. Start date and again an end date because you could have, like, a marriage or whatever, or something that's going to start and end. Baptisms, funerals, all that kind of stuff. So that'll be a person's timeline of events.
We'll store that. Okay.
I almost forgot membership types. Membership type. What kind of membership they have? A person ID. Membership type ID. Now this is not going to be in the helper table. We're going to have a start date and then due date - when does their membership due? Again, you might not be charging dues for it. That's fine.
We'll define membership types down here. Membership type table. Membership type ID, the description, the normal dues, and they'll have a frequency ID. The frequency can be over here. That's fine. Frequency is going to be like daily, weekly, monthly, quarterly, annual, whatever. That's just going to be a list of frequency types. Even if you don't have paid membership types, you could still use this for different groups or clubs or organizations within your association.
Let's color these up here. Let's do these. We're running out of standard colors here. Let's go back to gray. How does gray look? Okay, these are kind of - we'll do that.
So, you can see the helper table is going to keep us from having to make one, two, three, four, how many tables - 12 extra tables. No sense in all these tables; they're just lists of things.
Another thing someone emailed me: let's do an attendance status ID right here. You could have present, absent, left early, or came late if you care about tracking all that stuff, as I know a lot of people do. We'll copy that over here. Try to keep everything looking good.
So, this will label as our tables. If you want to make another sheet in here and track your forms, you can actually lay them out in here, what you want them to look like. For this, I tend to do something like this where I make these, I make it look a little bit more like graph paper.
Then we'll do something like this, and then we'll color it in. I try to stick to the same colors that I'm using for tables. So, the person table over here, right, person table. I don't put every field, I don't lay every field down in here. What I do is try to make this show what other tables or subforms it might have.
Now, person is not going to be - actually, we don't need that big of a spot for person. Person is going to be pretty much a simple single form. We are going to have a family form. Let me just grab this and do this here. This will kind of be family.
So, family will be over here. In here, we're going to have a subform for people, or at least a list box. I haven't decided yet. So, we'll do this, and we'll just actually take this like this, and we'll do something like that.
You can see now how it looks silly what I'm doing. But person, person, person, person - so, in other words, on our person form, we're going to have a button here for family or a combo box or something. When we click on this, it's going to open up this guy and it's going to show a form with all the people in it.
So that's going on. It's just something simple like this that I do. You can double-click on any one of these people to open up the person record.
Demographics is just going to be a simple form. I'll put it here: demographics. That's just going to be another simple form; you click on the demographic and it'll bring them up in that little form that pops up. Maybe make it modal so it stays at the top of this guy.
Also, we'll have a person list form as well. So, maybe slide all of this over, and we're going to have a person list form, like I've got the customer list form right over here.
My spreadsheets tend to be messy like this. This is just me laying stuff out. You can make more of these. Doesn't have to be perfect. It's just a quick way to give me an idea of what we're doing before we actually start doing it.
Events and attendance: I'm picturing a big event list form. Let me copy it so I get the colors and stuff.
This will be separate; we'll go over here for this. This event list form...
One, event two, event three, and so on. Of course, we'll have something on here so we can show all events, show future events only, show closed events. You can put notes like that on here - show all future, between dates, whatever you want to do, sort differently.
Then we'll have our actual event details, which will have the information about each event. This will probably be bigger, so we'll do this, because we've got more information in here. You're going to have the info over here. In here, you're going to have a subform for your attendees. That's going to be your attendance table. You're going to have your attendees listed here.
So your attendance list: you click one of these, the attendance details open up. Over here is your list of attendees, the people that are attending each event.
You can have sums down here, like number attending, that kind of stuff. You might want to have a max number of attendees. See, this is the part at which you start figuring, "Oh, I got to add this, I got to put that in there." You might want to have a max number of attendees. You might have a minimum attendance, like it's got to have at least five people or it's not going to kick off.
I used to do it with my training classes. I used to put it right on the schedule too: "If we don't get at least three people signed up, we're not having the class." There's nothing worse than having a Microsoft Excel or Word class with two people in it, and it's a room that seats like twelve. It's just a waste of everyone's time. Well, it's not a waste of their time because they get more one-on-one time. It was a waste of my time. I'm sorry to say I used to cancel classes all the time if they only had one or two people in them.
So that's attendance and events.
The person timeline: you could do this as a subform. I think I'd probably just have this as another form though, like here. This would be, you know, event one, event two, and so on. That's just another form that pops up.
Donations: that's easy. Again, that would be a button from each person to show their donations. I'm thinking memberships, though, you might want that to be a subform on the person's form.
You know what we'll do? We'll do these as subforms. Now that I'm thinking about this more, I kind of like the fact - what I do with my ABCD is I make different subforms that appear inside the person form. I think that might work better.
So, the family will still be its own separate thing, but we'll make the person table, the person form, larger. We're going to make a subform inside of this that's going to have these different things. It's a trick I do in my ABCD.
We'll have a subform here with different stuff in it. We'll have little links that'll change what stuff you see. One will be for demographics, one will be for timeline events, one will be for donations, and so on. We'll see those as subform items inside of here, and that'll change. I prefer doing that over tabs. You'll see what I mean when we get to that.
If you're a member, I already covered this in the extended cut for the vehicle maintenance video, where we switch between their maintenance history and their future maintenance. For non-members, this will be brand new content where I'll show you how to swap what subform appears inside of a subform object. I also show how to build it in my ABCD part one. It would be the same thing here - we've got a subform, and we have different types of stuff we can flip between to show in here.
ABCD is basically my good database that I've been building for a while. It's got all the bells and whistles and other tricks and tips and stuff that I know how to throw into it. It's got everything but the kitchen sink.
So, we're going to do demographics as a thing, we're going to do timeline as a thing as a subform in here. We can also flip this around. So, you got the event list, and you've got each event with its list of attendees, we can go the other way too, and we can show the events each person has signed up for. We've got one person signed up for multiple events, or you've got an event with multiple people. That's why I said before that an attendee table is a junction table. It binds together events and people.
If you're not familiar with many-to-many relationships, watch this video before we get to that lesson. I do the same thing with people and cars, because you can have a one-to-many relationship with people and cars if a car is owned by only one person, if you're doing cars and their owners. But if you've got a fleet of cars and a bunch of employees and different employees can be assigned to different cars, that's where you have a many-to-many relationship. So, watch this if you don't know what I'm talking about.
We can have events as a subform item as well. The demographic item will be in here, and the demographic will actually just be a single form that'll be up in there, because you're only going to have one page of demographics, but it'll show up in that little guy. Timeline and events: those will be the continuous forms that will show up in there. Donations: same thing, donations will be in the subform.
Move everybody over. We've got to resize this stuff, we've got to keep making more rows or columns. Then we've got memberships, again, the same thing.
This is why I like to lay this stuff out, because then we're going to have a membership subform: M1, M2, M3, and again, that's going to go inside as a subform. But we're also going to have to go the other way too. We've got our memberships, our groups, so we'll put this over here. In here, we're going to have to have the details, but then we also have to have a subform - member 1, member 2, member 3, and so on. This will be a list of people inside that form.
So, we've got family with a list of people, we've got events with a list of people. These are all people. I know I've got attendee in here, but you get it.
Then, of course, we have our order form, which I didn't really draw that on here because we already have that built in the other database. But the same thing over here: we've got orders, order 1, order 2, order 3, and so on. And, of course, contacts - we've got contacts in here, right, 1, 2, 3.
So, we already have a form like this built, right. If you're familiar with the TechHelp template, which I told you to watch in the last video, we already have a customer form where you can open up contacts as a standalone form, or you can open up customer with contacts as a subform. We already have this built.
So, this guy here is what I'm talking about - we're going to swap that out with different stuff. Across the top, we'll have a bunch of links here - or in my ABCD, I use another design - but across the top is fine. You click on contacts, donations, what groups you're in, what events you're attending, and this box will swap out. You'll see how that will work in an upcoming video.
Finally, don't forget our helper table - that's going to be over here. We'll just put you there. That's going to be its own form. This is going to be a little more complicated than that. The helper form, you'll pick the type ID here, and then the data shows up below. So, you'll pick your list over here, like what list are we working on? Genders? Okay. Then your gender list appears down below. Slide this over left. The form will allow you to edit whatever category you're working on at that time.
I think we've got this all the time. As far as reports go, I honestly don't usually lay out my reports until I'm done with the forms. I tend to get all the data collection and data entry and all that stuff done first, then I sit down and go, okay, what kind of reports do I need when the database is finished?
Unless the whole point of the database is to generate a specific report or two, then you kind of have to mold the data around that report. But I usually find it's easier later on to generate the reports when the data entry is finished. That's just my style. You can do it your way.
I think we've got enough here to give me an idea. I'm glad I did this, because after putting this together and seeing how many other related forms we're going to need, I decided it's better to do this subform trick, and then we'll have a couple of other things where we have to go backwards too. So that's good.
There, we've got our tables laid out. If you can think of any other types of tables that I've forgotten or any other forms that I need to put in here, let me know.
I think in the next video, we know enough to start laying this stuff out in Access. Now, I don't remember in the last video - I just kind of rewatched it, but I still don't remember - if I told you that we're going to need a little bit of VBA in here now. We don't need a ton of it. I am going to have some extended cuts planned where, for the members, I'm going to be going into some extensive VBA to do some cool tricks like, if you've got an event, click on a button and it loads all the attendees in automatically.
But for the rest of you, if you're not planning on getting the member videos, even for the free videos, we're going to need a little bit of VBA here and there, like that trick that I'm going to show you to swap the subform. That's just one line of code, but you need to know where to put it.
If you've never done any VBA programming before, don't panic. Go watch this video - it's intro to VBA, it's 20 minutes long, it's free, it'll teach you what you need to know to get started. So, when I say, "Okay, we're going to put one line of code here," you don't panic and you'll know what I'm doing, so go watch this.
I think we're done with the layout portion. You saw that was less than an hour of laying stuff out. If you take that hour or sometimes two and lay out the database ahead of time, you'll save yourself some headaches later on.
I came up with a few things that I forgot while we were doing this, even after all my other notes I took. I take copious notes in Notepad.
So, in the next lesson, we're going to start laying out our tables and changing some of the ones that we've got, and we're going to make some modifications to the customer table and things like that.
There you go. That's it for today. Obviously, any comments you've got, any tips, any things you want to see added, any features that you're looking forward to, I want to hear from you. Post something in the comments down below, and the stuff that I think is a good idea and that I want to add to the database, I'll include in the next video.
Hope you enjoyed. I'll see you next time.Quiz Q1. What is the purpose of the attendance table in the association database?
A. To record only the events attended by each person
B. To serve as a junction table linking people and events in a many-to-many relationship
C. To store payment information for donations
D. To list only the names of events
Q2. Why might you include start date and end date fields in the events table?
A. To track the lifespan of a person in the database
B. To allow for both single-day and multi-day events
C. To record donation periods
D. To indicate membership duration
Q3. What is the suggested approach regarding the location information for events?
A. Always create a separate table for locations
B. Store all location information inside the event table only
C. Use the helper table to store common location values for now
D. Do not track location information at all
Q4. Why do we use a helper table in this database design?
A. To reduce the number of separate lookup tables by centralizing lists
B. To store all main data in one table
C. To keep track of user passwords
D. To record only financial transactions
Q5. Which of the following is NOT typically included in the attendance table, but could be added for more detail?
A. Attendance status (present, absent, etc.)
B. Amount paid for events
C. Timeline Type
D. Status such as arrived late or left early
Q6. What is the main purpose of the timeline table?
A. To track donations over time
B. To record personal life events tied to a person, not association events
C. To schedule future association events
D. To collect demographic information
Q7. How are membership types managed in this database design?
A. Using a single field in the person table
B. Through a membership type table with frequency and dues information
C. By storing the information in the events table
D. By creating one table for each membership type
Q8. What database object is described as binding together events and people, allowing for many-to-many relationships?
A. Helper table
B. Memberships table
C. Attendance table
D. Order table
Q9. When designing forms, what is a suggested method for allowing users to switch between different data views (like donations, contacts, events, etc.)?
A. Adding a new tab sheet for each data type
B. Swapping subforms using a control (like buttons or links)
C. Creating a separate form for each data view
D. Storing all information in a single text box
Q10. Why does the instructor recommend laying out the forms and tables on paper or in Excel before building the database?
A. It makes the final application run faster
B. It helps visualize structure and catch missing pieces early
C. It eliminates the need for reports
D. It improves export functions
Q11. What is the recommended approach for designing reports in this association database?
A. Design all reports before building any data entry forms
B. Only create reports if they are the main goal of the database
C. Wait until data entry forms are complete, then design reports as needed
D. Never design reports for this type of database
Q12. Regarding storing payment and credit card information, what advice is given?
A. Always store full credit card information for convenience
B. Check regulations in your state or country before storing sensitive info
C. It is safe to store any payment info without restrictions
D. Store only the payment type, never details
Q13. What will the upcoming use of VBA in the database be primarily used for, according to the video?
A. Replacing all forms with VBA code
B. Swapping different subforms inside a main form with a single line of code
C. Automating report generation
D. Encrypting the database
Q14. What is the key reason for using subforms inside the main "person" form?
A. To keep the interface uncluttered and switch between related data conveniently
B. To avoid using any VBA code
C. To reduce the number of reports
D. To combine all tables into one
Q15. What is a main benefit of putting look-up data, like gender or frequency types, in the helper table?
A. Makes it easier to expand or modify lookup lists later
B. Eliminates the need for primary keys
C. Means you never have to update your database again
D. Prevents the need for user feedback
Answers: 1-B; 2-B; 3-C; 4-A; 5-C; 6-B; 7-B; 8-C; 9-B; 10-B; 11-C; 12-B; 13-B; 14-A; 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.Summary Today's video from Access Learning Zone continues our series on building an association database. This is part two of the process. We'll keep refining our tables and begin setting up the forms that will make the database user-friendly and functional.
Starting with the tables, last time we discussed the core ones, but I realized there were a few helper tables that we needed to include, so I made space for those. For events, the event table will track upcoming happenings for your church, organization, or group. Each event will have an event type ID for categorization, a description, and you can include either just a date or both a start and end date, depending on whether your events span multiple days or just a single day. For example, conferences might need both, while a wedding could just have a single date. Each event also needs a location. While you could set up a separate table for locations to store detailed address info, for now I am keeping it simple and storing location names like North Campus or Main Street Office in the helper table. This keeps things flexible, as you can always expand later if your needs grow.
The attendance table is next. This acts as a junction table with a many-to-many relationship between people and events. This way, each person can attend many events, and each event can have multiple attendees. The attendance table will include fields for person ID and event ID, and you can optionally track extra information like payment status if your events require fees. We might expand on this to include things like attendance status or payment details as the database evolves.
Color-coding the tables is something I like to do as I go, just to keep everything visually organized. For donations, the database needs a donations table where you'll record who donated, how much, and to which fund the donation was made. Many organizations have multiple funds, such as a building fund or a camp fund. You can manage these funds with your helper tables as lists. Additional fields like payment method (cash, check, credit card) can be included as needed, and, of course, be mindful of regulations if you decide to actually store payment information in the database.
I've already got a contact table and order table built, so I just set those aside for now. From your feedback since part one, many have asked for a feature to track life events for individuals, similar to what I've done in my genealogy databases. These events might not be association-wide, but personal milestones like baptisms, initiations, or graduations. To cover this, I am adding a timeline table. Each record in the timeline table will be tied to a person and the type of event (again using a helper table), along with relevant dates for each milestone.
Another important table we need is for membership types. This table will track what kind of membership each person has, along with when it starts and when dues are owed. Even if your group does not charge dues, it is still helpful for organizing different types or levels of involvement. The membership type table will include info like the typical dues and the frequency (monthly, annual, etc.), which we can manage through another list in the helper tables.
One of the big advantages of using helper tables is that it prevents us from creating a dozen unnecessary tables for simple lists like payment types, frequencies, or event types. A single helper table lets us manage all these categories in one place, saving time and keeping things tidy.
Someone also suggested tracking attendance status, such as present, absent, arrived late, or left early. We can add an attendance status ID to the attendance table to cover these situations.
As I start planning the forms, I use spreadsheets to sketch out how things might look and which data each form should show. I do this in a way that resembles graph paper, using color coding to tie the forms to their tables. My forms generally show which related information or subforms (such as a list of people in a family, or events attended by a person) they will contain rather than every individual field.
For families, the form will include a subform or list box presenting the members. When you select a family, you see the related people and can dive deeper into an individual's record if needed. Other simple forms, like demographics, will just pop up as modal forms when needed.
There will also be a person list form, similar to the customer list form in my existing TechHelp template. This approach makes it easy to manage large volumes of records efficiently.
For events and attendance, I am picturing a large event list form where you can view upcoming events, filter by open or closed events, or see events by date range. View detailed information for each event as needed. In each event record, a subform will display the attendees linked from the attendance table. You might want to calculate stats like the total number of attendees or set limits for minimum or maximum attendance, which is especially helpful to avoid running under-filled events.
For the person timeline, I may keep this as a stand-alone form rather than a subform, but you could of course set it up however works best for your needs. The donations form will link from each person, showing their donation history. Memberships, on the other hand, probably make more sense presented as a subform embedded within the person form so you can see all active and past memberships in one view.
This brings me to one of my favorite interface patterns, which I use in my ABCD database. Inside the person form, you can display different subforms for demographics, timeline events, donations, and so on, all within the same space. By clicking links across the top of the section, the subform swaps out to show the desired information. I prefer this over using tab controls, and I will walk you through exactly how to set this up as we progress.
If you are a member, you may have seen similar techniques in my extended cut for the vehicle maintenance system, where we dynamically swap maintenance subforms. For non-members, this will be covered step by step in the upcoming lessons, including how to manage which subform appears using a touch of VBA.
Speaking of relationships, remember that the attendance table acts as a junction between people and events for the many-to-many relationship. If you are not familiar with handling many-to-many relationships in Access, I recommend taking a look at my dedicated video on the topic before we get there, as it really helps clarify how these connections work.
As for other forms, the order and contact forms are already developed based on earlier templates, so we will revisit them as needed. Meanwhile, the helper table will have its own form, allowing you to select which type of data you want to manage, like updating gender lists or event types, all right within that interface.
Regarding reports, I generally wait to lay out my reporting needs until all forms and data entry structures are done. Unless your whole database is being built for a specific report, it is usually more efficient to focus first on getting your data organized and entered, then generate the necessary reports afterward.
By sketching all of this out ahead of time, even if it's a bit rough, you save yourself headaches by identifying missing forms or tables early in the process. Planning like this always pays off. Every time I work through the design, I spot things I would have otherwise overlooked, despite taking plenty of notes.
In our next lesson, we will move the design into Access, start setting up the actual tables, and refine some of the existing structures. We will also make updates to the customer table where appropriate. We will begin using a bit of VBA, but only what is absolutely needed for swapping subforms and similar small tasks. If you have never worked with VBA before, check out my short intro video, which will give you enough of a foundation so that adding these little enhancements to your database will be no problem.
That wraps up the planning phase for today. If you have suggestions for additional tables or forms, or features you would like to see included, please share them in the comments. With your feedback and ideas, I can make the database even more useful for everyone.
To see a complete video tutorial, including step-by-step instructions and all the topics we covered here, visit my website at the link below.
Live long and prosper, my friends.Topic List Laying out event and attendance tables
Designing a junction table for event attendance
Adding event type, start and end dates, and location fields
Discussing helper tables for lookup values
Designing a donations table with fund and payment info
Creating a timeline table for person-specific life events
Designing a membership types table with dues and frequency
Adding frequency lists for membership dues
Including attendance status in the attendance table
Planning form layouts with subforms and navigation
Designing forms to display people, families, and related subforms
Laying out event forms with attendee subforms and totals
Arranging timeline and donation subforms in person forms
Implementing membership subforms within person forms
Reversing subform relationships to show group members
Organizing forms for order and contact details
Configuring a helper form for managing lookup lists
Structuring navigation to swap subforms based on selection
Preparing for minimal VBA to support form navigation |
