Association 9
By Richard Rost
3 years ago
Microsoft Access Association Database, Part 9
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.
Pre-Requisites
Members
No extended cut, but here's the database file:
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, major revision, putting people into groups, families, companies, interests, many to many relationships, m2m
Subscribe to Association 9
Get notifications when this page is updated
Intro In this video, we continue building the Microsoft Access Association database by making a major design revision: replacing the single "family" grouping with a flexible "groups" structure. I will show you how to restructure your tables to support many-to-many relationships, allowing each person to belong to multiple groups such as families, companies, clubs, or interests. We will cover redesigning tables, creating junction tables, updating sample data, and building queries to display group memberships. This is part 9.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. We are continuing with the Association database. This is part nine, and in part nine, we are going to do a major revision, which always happens in every project: a major revision.
We are going to switch "family" over to "groups". So in addition to just a single family type - one family - we could have multiple families. We could have families and companies, we could have companies and clubs, we could have interests. We can have all kinds of stuff instead of just a single family, and in this video, I am going to show you how to tear apart your database and stick all this stuff in there. You ready? Here we go.
One of the nice things about building a database slowly over time like this and posting the videos as we go along is I get to get your feedback on how you think the database should move forward.
I have gotten tons of emails from literally hundreds of different people who had their ideas on what they would like to see added to this database. Most of them are great ideas; some of them are too specific for this database. I am trying to make something that is going to have mass appeal for everyone. But of course, your copy of the database, you can do whatever you want with it. You can take it and put the Legos together however you see fit, so I am trying to pick the features and functionality that I think most people are going to want.
A couple of themes came up from the different emails that I received and a few conversations we have had in the forums on my website. People want to see, in addition to families, other types of groups added for people, such as a company. A person can belong to a company, different types of groups that they might belong to, like a youth group in the church or that kind of thing. We are going to have memberships, we are going to still do that, do not worry about that. This will be for like paid memberships and stuff, but people want a more generic way to group people together.
What I think we are going to do is we are going to get rid of "family", and we are going to get rid of "person type", and we are going to make a more generic group that we could put people in, and people can be in multiple groups.
Right now, what we have is a one-to-many relationship, where a person can only be in one family, for example. But you might also have situations where you have kids that might belong to multiple families. For example, mom and dad divorce, and they have kids, and the kids are in there as people, but they belong to two separate families. Both of those families still go to the same organization, church, whatever.
So what we are going to do is switch this around to a many-to-many relationship. Instead of having just one family ID that you can be assigned to, and one person type ID, we are going to make it a group table, and then a person could be in multiple groups. This is going to require a junction table.
Is this bad database design from the beginning? There is a specific reason why I decided to do this: because in almost every project that I have been involved in during my 30 years of consulting, there eventually comes a time when the client wants a major revision. You get it to work fine, but then, "Can we make it do this?"
I thought it would be helpful for you to see how I would now go through and take this stuff out and build the other stuff in, because I have had to do this in almost every major project that I have worked on. It is not through any lack of preparation on my own part. I will sit down with the client, and we will flesh everything out on paper, we will make everything look good, and they sign off on it, but then sometimes, once you start actually using the database, you see a limitation that you did not foresee when you were preparing it, and they are like, "Yeah, but I want to change it to this."
So you have to go in and open up the hood and pull some parts out of the engine and put some different parts in. Like I said, I have had to do this more times than I can count. You will have to do this if you are a consultant, and I cannot stress this enough: make sure that you get the client to sign off on the design before you build the thing. Because if this happens - if you finish the database and the client has been working with it, and they are like, "Well, we really wanted to do this" - you can at least go back to the signed contract and say, "Well, you signed off on it as this. I am happy to do it for you, but it is going to take X amount of more time and X amount of more money for me to do it."
Like I said, this has happened in probably at least half of the projects I have built. They are like, "Okay, yeah, that looks perfect. We got all the forms fleshed out on paper," and they still want changes made. That happens, just be ready for it. It is part of being a consultant. Do it, but just let them know that it is on their dime. So this is on your dime.
I am going to make a list down here of what we have to do. We are going to delete the family T. We are going to create a group table and we are going to create a person X group junction table.
In person T, we are going to delete the person type ID and the family ID. We are going to get rid of the person type ID in the helper table (helper T) data - delete person type ID. We no longer need those; all of these are going to be groups now.
We can have group classifications. You can have groups of groups, in other words. I am going to say, let's do a helper type, which is going to be called "group type." So, for your group type, we are not going to have a person type here anymore; we are going to have a group type ID. Then you will have your list of different kinds of groups: what type of group is this? Is it a family? Is it a company? Is it a club? Is it a whatever?
Then, the actual junction table will store both of those things. Later on, we can filter from the list, so if you want to go through the list of families, you can just pick that group type ID.
This will be replaced with a group table. This will have a group ID, which is the primary key, a description, we no longer need a head person, notes, and date created. I think that is probably all we need. Of course, the group type ID, slide that down.
You know what, I hate how Excel does that - keep the background color Excel copy paste.
Group type ID.
Let's change this guy's color just a little bit, just a little bit. All right, maybe we will go with that.
Over here, we will get rid of the person type and make that... no, no, no, no, we do not need fields in here. We have to delete those fields, because now we need a second junction, which I am not going to put on the roadmap. I do not usually put junction tables on my roadmaps. They are assumed.
Let's switch over to the database now and make these changes.
There is my Association 8. I am going to copy that.
We can make this Association 9.
Take this old Association 8 and drop it in your backup folder.
Remember to do that. All right, let's open this guy up.
First thing - you have to be careful when you just start deleting stuff willy-nilly, because remember this family T: we have a family list here, you have your person list here, and if you open up this guy, there is a family combo box here. You are going to have a lot of places where it cannot find stuff.
Sometimes, I think that is even better, because at least if you delete this and then recreate it as something new, Access is not going to try to rename the components for you, and then you will know something is missing, as opposed to some sneaky error because it is trying to find - you know, if you rename this to the group table, it will find some sneaky way of renaming the fields, and it just messes things up sometimes.
Just be aware of that when you do that. I am going to delete that family table. OK, delete - gone, and let's create a group table: Table Design.
Group ID: AutoNumber; group type ID: that will be a number, that is going to come from my helper table; description: short text; notes: long text; date created, which will be my date/time, and we will put down here in the default value, equals now.
Save it. This will be my group table, primary key, yes.
We can put some data in here now. Actually, before we do that, let's set up the group type IDs.
Start off with the helper type table.
We no longer need person type, which is helper type one, so if you go into the helper T, you can find all the helper type ones. Sort this ascending, and we can delete all of these.
Person type one: gone.
Now, down here, and you could, if you want, replace one with group. I do not care, that is fine too, but it does not matter. Remember, those IDs are meaningless to you, but they are meaningful for your code, because the different combo boxes have to know to pull on that ID. But as far as you are concerned, it should not matter whether it is a 956 or a 2.
Now, we are going to put in here "group types". That is going to be type 9.
Come back over here in the helper table now. Group 9 is going to have a list of all the types of groups in the database. So the first thing is going to be a family.
Company.
Club.
What other types of groups do you want to put people in?
You could even have a list of interests - 9.
Interests, right? Does this person like Star Trek? That could be a group. Put them in the Star Trek interest group.
Right, and the Picard family, and the Starfleet company.
These are group types.
That is all set. Now let's go to our person table.
Design that. We do not longer need person type ID now.
This assumes you do not need to save the data that is in there. I am just assuming we are at pre-production, and you should always do this with your databases.
Even when you think the database is finished, you need to tell the client: do not start using this with actual data that you care about until we are done done, like with version 1.1.
Because if you want to save the data that is in here now, you have got a whole bunch of app inquiries and stuff going on.
So, no, right, we are in pre-production, so it is safe to delete the person type and the family ID.
Hang on, oh, we got indexes. OK, yeah, that's fine, and the family ID.
Now I do not need any more fields added to the person table, because groups will be handled in a junction table. So let's create that junction table.
This will be the person x group ID, which will have a person ID and a group ID.
That is it. If you want to put a date in here, like a date created, that is okay, or you could put notes in here if you wanted to. This represents the relationship between that person and that group. So if you want to keep track of when they were added to the group, that is up to you. If you want to put notes in here, you know, anything you want to add that has to do with this specific relationship.
The transaction of adding this person to the group. Generally, you do not need to. We are going to save this as person x group T.
If you do not know what I am doing, or if this seems weird to you, then make sure you go watch that many-to-many video. I cover this in a lot more detail.
Now, you can close this, and save changes if you need to.
Now, when you go to open the person table, you are going to see that family does not work and person IDs are not going to work either. Did we put... oh, person types down here. That is not going to work either. So let's get rid of those. Design view, nuke family and person type ID.
I am going to leave most of the rest of this the way that it is.
We are going to make groups be another one of these guys over here.
Let's put some sample data in. Actually, let's see what we have got - the family list. Yeah, the family list is not going to work.
We have a family form - where are you? Family form is not going to work; family list form is not going to work.
Now, the family list form we can pretty much keep. We can just turn that into a group form. But the family form itself has some weird stuff in it that we did, where this is a list of people in the family and the person ID. We had some cool friction here; consider that a learning experience. We are going to delete this guy and rebuild it. So let's delete family F.
I click the delete.
The family list form - we are going to convert that over to a list of groups. But right now, we do not have a list of groups, do we? So, we have in the helper table, and the helper type, we have 9 as group types.
We have the group types listed here, but we do not have any actual groups. So let's make some actual groups.
It is easier to see with all this stuff open.
Group type ID: let's put a list of families in here.
We have Picard.
We have... I am sorry, this group type ID here, this is actually going to be a helper ID. So it is 58.
In other words, helper type 9 is group types, so these are all group types. That helper ID actually means "families". So, over here, the group type ID is going to be 58 - that is family.
Another 58 for Rost.
Another 58 for House of Mogh.
Another 58 for Riker. See how this works? Another 58, these are all your list of families. Jones.
Group 59 is going to be companies.
So, 59: ABC Corp; 59: Amicron; 59: XYZ Inc; and so on; 59: Microsoft, whatever.
Clubs: 60.
Interests: 61. So, what are your interests? 61: this guy's a Trekkie; 61: book club; 61: Firefly; 61: Star Wars fan.
See how this is working, and you could put a person in as many of these groups as you want.
How do you do that? You take the group T and the person T and you mix those together with your junction table.
Here are your people, there are your groups, here is your junction table right in the middle.
Person type one, that is me. I am in the Rost family, which is group two. Me, I am a Trekkie, I am in group 10, which I can see, group 10 means it's an interest.
Let's keep going. Person one is in group seven - that is a company type, Amicron.
Let's move down to Jean-Luc Picard - four. That is Jean-Luc Picard, four. He is in the Picard family, which is group ID one. Four, he is also in XYZ Inc, that is group eight. He is also in the book club, that is 11. See how this works?
Now, if you want to list a person with all of their groups, that is an easy query to make.
Create - Query Design - bring in the person, bring in the junction table, bring in the group table, and you also want that group type ID. That is going to be part of the helper table.
These two things do not necessarily mix directly because the field names are different, but this helper ID matches this group type ID. Click, drag, and connect those.
Then I can bring in the person, their name; you can skip the junction table because that is just linking them together. Bring in the description of the group, and then the helper value will give you that group type, and if you run that, there you go.
You see Richard - that is me - the different groups I am in. There is my family, there are my interests, there is my company, and you could have multiples of these. You can put me in multiple interest groups. Let me save this: Person X Group Q.
Put me in multiple interests.
Again, we will pull those tables up. So, a person type is one for me, right? Person list: I am one, OK. The group table has the types in it; all the interests are down here, group type 61.
In that junction table - that is just the date, do not worry about that. Here is me, I am going to also add me to 11, to 12, and to 13. Already in 10, which is Trekkie; I am going to add myself to 11, which is book club; 12, which is Firefly; and 13, which is Star Wars fan.
Now, save changes. Yes.
Come back to that query we made, and now you can see I am in all of those interest groups.
If this seems confusing, this is not easy stuff. This is expert-level material for my courses, but this is many-to-many relationships, and mastering many-to-many relationships is not easy. It took me a while when I first was starting to learn this stuff, so go watch that many-to-many video.
If you really want to get a good handle on this, Access Expert 7: I spend a lot of time covering many-to-many relationships - an hour and a half.
We do products from multiple vendors, groups, junctions, customers, lots of different stuff. I also have a relationship seminar where I cover all of the different types of relationships in detail: one-to-many, one-to-one, many-to-many, self joins, reverse relationships, all kinds of different stuff.
Here are all the different lessons we go over, many-to-many, lots of stuff with many-to-many.
I will put links to both of these things down below if you want to learn more about many-to-many relationships.
In the next part of the Association videos, part 10, we will build forms to get this stuff so we can pick it for each person. What we are going to do is, we will change this over to a group list so we can see a list of groups, just like the family list. Then we will have it where we can put people in the groups, and then put groups in on the people. So, on the person form, for example, you will be able to open this up, click a button, and then pick what groups they are in.
Then, we will do the same thing in reverse. You will be able to open up the groups and then pick what people are in that group, and then eventually, we will filter their group list so you can pick just families, just interest, and so on.
We probably will not get all of that in part ten, but that is what is coming up. See you next time.
There you go, folks. There is your association Fast Tip for today.
Live long and prosper. I will see you next time.Quiz Q1. What is the main reason for switching from "family" to "groups" in the database structure?
A. To allow each person to belong to multiple, more generic group types
B. To make the database easier to backup
C. To remove the need for a junction table
D. To simplify forms and reports only
Q2. What is a key limitation of the original "family" structure in the database?
A. People could only belong to one family
B. People could belong to unlimited families
C. There were no reports available
D. Only companies could be added as groups
Q3. What type of database relationship does the new "groups" approach enable?
A. Many-to-many
B. One-to-one
C. One-to-many
D. Self-join
Q4. What table is required to relate people to multiple groups?
A. A junction table
B. An index table
C. A primary key table
D. A helper table
Q5. When updating the structure, what did the presenter suggest deleting from the person table?
A. Person type ID and family ID
B. Email address and phone number
C. Date of birth and address
D. Person's last name
Q6. Why is it important to have clients sign off on design documents before building a database?
A. To have proof of agreed requirements and scope if changes are requested later
B. To ensure all forms are finished in advance
C. To increase the project's speed
D. To avoid using backup folders
Q7. What is an example of a group classification mentioned in the video?
A. Interests, such as "Star Trek" fans
B. Pets and animals
C. Phone numbers
D. Database backups
Q8. What field is used as the primary key in the group table?
A. Group ID (AutoNumber)
B. Group Type ID
C. Description
D. Person ID
Q9. What happens if you just rename family-related tables and fields without fully restructuring?
A. Access may rename fields incorrectly and introduce subtle errors
B. Everything will work perfectly with no issues
C. The database will run faster
D. All reports will be automatically updated
Q10. What are the benefits of using a many-to-many relationship in this scenario?
A. Flexibility to assign people to multiple types of groups
B. Prevents anyone from belonging to more than one group
C. Eliminates the need for queries
D. Simplifies the database to just one table
Q11. What should you be cautious of before deleting tables or fields during a major revision?
A. There may be dependent forms, queries, or controls that will break
B. The database size will increase
C. You will lose all your queries
D. The application will freeze permanently
Q12. When adding new group types, where should those definitions be placed?
A. In the helper table with group type as a value
B. In the person table as new columns
C. In a macro
D. In a form property
Q13. What should you do before making major design changes to your database?
A. Make a backup copy of your database
B. Start deleting all the forms first
C. Export all tables to Excel
D. Disable all primary keys
Q14. When the presenter says to use a junction table named person x group, what is the structure of this table?
A. It includes at least person ID and group ID
B. It only includes a single ID field
C. It stores phone numbers for each group
D. It stores the group description
Q15. How can you list all the groups a person belongs to using queries?
A. By joining person, junction (person x group), and group tables in a query
B. By filtering the group table directly
C. By looking up each group manually
D. By searching the helper table only
Q16. Why is it suggested to only implement real data after the design is "done done" (finalized)?
A. To avoid losing important information due to redesigns
B. To ensure queries run faster
C. To reduce the number of relationships
D. To keep forms available for users
Q17. What advice does the presenter give regarding client changes after database completion?
A. Changes should be billed as extra time and money
B. All future changes must be done for free
C. Changes are never allowed after sign-off
D. Always make changes without notifying the client
Q18. For which learning resource does the presenter recommend to understand many-to-many relationships in depth?
A. Access Expert 7
B. Access Beginner 1
C. Database Reports Seminar
D. Office 365 Tutorials
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A; 17-A; 18-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 is part nine in our Association database series, and in this installment, I focus on making a major revision to the database's design. This is a common step in many real-world database projects, as requirements often change and systems need to be adapted.
Up to now, we have used a "family" system, where each person could only belong to a single family. However, based on feedback I've received from many of you, and discussions in our forums, it's clear that users want the ability to assign people to more than just families. There is interest in allowing people to be part of companies, clubs, interest groups, and more. So, in this lesson, we are restructuring the system to use "groups" instead of "families," giving the flexibility for people to belong to multiple groups of different types.
The plan is to eliminate the "family" and "person type" structures, and replace them with a generalized "group" approach. This shift changes the relationships in the database. Instead of a one-to-many relationship where each person is part of only one family, we will use a many-to-many relationship. Each person can now be included in any number of groups, whether those are families, companies, interests, or clubs. This requires the introduction of a junction table to connect people and groups.
You might wonder why we did not design it this way from the beginning. In my decades of consulting experience, major changes like this are almost always requested at some point, even after the initial design and sign-off. Sometimes, it is only once the client starts using the system that limitations become clear. When this happens, you need to revise the structure, sometimes pulling components apart and rebuilding them in a new way. I recommend always getting client sign-off on the design before beginning the real work. That way, if changes are required later, you can refer back to your agreement and justify any extra time or cost.
For this update, here are the steps we need to take. We will remove the "family" table and create a new "group" table instead. We will also add a "person x group" junction table. In the "person" table, we will delete the "person type ID" and the "family ID" fields since those are no longer necessary. Similarly, we will update the helper tables by removing the "person type ID" since it too will be replaced by our new system.
To give a bit more structure, we will introduce a "group type." This lets us classify groups by type, for example, family, company, club, or any custom type like interests. We'll update our helper tables to support group types instead of person types. When you add new groups to the database, you will select their type so you can easily differentiate between a family, a company, or an interest group.
Next, I walk through converting the old tables to the new structure. This involves careful deletion of tables and fields no longer needed, making sure not to break relationships or introduce errors by just renaming old components. I build the new group table with a group ID, group type ID, description, notes, and date created.
I demonstrate how to update the "helper" table (replacing person types with group types), and I add sample group types such as family, company, club, and interest. This creates a flexible list that you can expand as needed; for example, you might add interest groups like "Star Trek fans" or clubs like "Book Club."
We then build the new "person x group" junction table. This table allows us to establish the many-to-many relationship, letting people be members of multiple groups, and groups contain multiple people. Fields can include a date joined or notes if desired, to capture information about that specific person's membership in a group.
After updating the structure, I describe how to add these new groups and link people to them using the junction table. For example, I show how to make Jean-Luc Picard a member of both the "Picard" family group, a company, and some interest-based groups. Queries can be built to list all groups that a person belongs to or all members within a given group by joining the person, group, and junction tables, as well as linking the group type from the helper table.
If you're unfamiliar with many-to-many relationships, this is advanced Access material, and I encourage you to review my in-depth videos and seminars on relationships, especially Access Expert Level 7 where I cover these concepts over several lessons.
Looking ahead to part ten of this series, I plan to build the forms necessary to manage these relationships more easily. We will set up group lists, make it possible to assign people to groups from the person form, and allow you to view all members of a group. We will also add features to filter by group type, such as showing just families or just interest groups.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Switching from single family to multi-group system
Replacing family table with group table
Designing a junction table for many-to-many relationships
Removing family and person type fields from person table
Creating a group type system for group classification
Setting up group types in the helper table
Building the new group table structure
Populating group and group type records
Creating the person x group junction table
Populating the junction table with sample data
Demonstrating joining people to multiple groups
Building a query to show all groups for a person
Converting family forms to group forms
Migrating existing data to new group structure |
