Students & Parents 8
By Richard Rost
2 years ago
Relate Students & Parents in MS Access Database Part 8 In this Microsoft Access tutorial, I will show you how to manage a mailing list for students and parents. We'll learn to store which parents are on the mailing list for each student (not everyone needs to get a copy of Timmy's report card). This is part 8. MembersThere is no extended cut, but here is the database download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, relate students and parents in Access, mailing list in Access database, Access junction table, track report cards Access, parent subform in Access, student table design Access, parent emergency contact Access, adding address fields Access, aggregate query example Access, many-to-many relationships Access, mailing labels Access tutorial
Intro In this video, I will show you how to manage mailing lists in your Microsoft Access student and parent database. We'll walk through adding an onMailingList field to the junction table, updating the parent subform to include a mailing list checkbox, and creating queries to generate mailing lists and identify students without parents on the list. You'll learn how to group and sum records in queries, use outer joins to include all students, and easily track who should receive important communications. This is part 8.Transcript This is part 8. It's a lot of parts of my students and parents series. Today we're going to put people on a mailing list so that not everybody has to get a copy of little Timmy's report card. You want them on the list. You want to be able to have them in the parents list for emergency contacts like we did last week or yesterday or whenever we did that one. But not everybody has to be on the mailing list to get stuff. So that's what we're going to do today.
Of course, if you haven't watched parts one through seven yet, what are you doing here? Go. Get out of here. Go watch one through seven. Come on back when you're ready. All right. So we've got a call sheet. And the call sheet's got everybody on it, in case you got a call. William McCoy's got to come home. I'm going to be able to have all of his contacts there. But not all of these people need to get a copy of the report card. So that's what we'll do by putting people on a mailing list. Now take a moment and think about it. We've got a student table, we've got a parent table, and we've got a junction table. Where do you think the is on mailing list field needs to go? In the parent team? Maybe, maybe not. What do you think?
Here's my list of parents. If I open up, let's go to students. If I open up, oh this is just, this is messing around the extended cut here. If I was messing around with William McCoy, well that's a bad example. Let's go to Bobby Kirk. All right, he's got three parents listed on his call sheet. Where am I going to track who's getting his report card? Is it Jim? Is it Jim and Carol? Is it just Mr. Spock, the logical one? Because you know he'd be the best person to go over that report card with. Well you can't save it in the parent table because Mr. Spock is responsible for three students and he is you know listed as a responsible party for Bobby Kirk but he might not be needing to get his report card.
So we're going to store this value in the junction table. It's that relationship between the parent and student that determines if you're on the mailing list for that student. A parent might be on the mailing list for one student and not for another. So let's go to the junction table design view and here we're going to add onMailingList. That can be a simple yes, no value. If you want to leave it defaulted to no, that's fine. If you want to default it to yes, that's completely up to you. If you want to just assume that all parents are added to the mailing list unless you take them off the mailing list, totally your call. They're your Legos, build them however you want to build them.
All right, save it, close it. Let's go to the parent subform. So we need to go to parent sub F, this guy, and we're going to put that checkbox right on here. We should be able to add existing fields. There you are. Click drop bang. And at this point, we might want to put some headings above this. I'm just going to chop this off, stick it up here. Let's make it white so we can actually read it. And this will be parent. And this will be mail. You know what it means. Mail. On the mailing list, send them mail. Send them the bad news. Don't send it to the parent. It's going to ground you. Okay. OK. OK, save it. Close it.
Now if I go to my students, and I open up Bobby Kirk, and I got to make this bigger here now too, don't I? All right, close that. Open this up. It's on my wish list for Microsoft to make subforms automatically resize. I know that's never going to happen. You can do it with code, but maybe drop anchor points in here. OK. Anyways, let's try that again. There we go. Now Jim Kirk gets your mail. Carol doesn't need one. OK, go to somebody else. Sally Kirk, maybe Sally. Let's put Carol Marcus on here. Maybe Carol gets the mail for Sally. All right. And then you go to Sue. OK Willie McCoy, let's see what else we got. Joe McCoy, maybe both parents. They love a party. They want to get several.
Of course now this means you're going to have to put addresses in for each of your parents. Open up the parent record, put an address field down here. But at least now you know who is getting the mail. Now as far as making those mailing labels goes, that is going to involve a query as well. Now, we already have a query here that's got the phone stuff in it. Really, all we have to do is add the onMailingList and then add a criteria and there we go. So, let's copy this, copy, paste, student, oops, try it again, copy, paste. See, another one of my pet peeves with Access sometimes, you've got to go copy, paste real fast. Because if you hit copy and wait a second, somehow it loses focus on that navigation pane. It's a pain in the butt. This is going to be student parent mail queue. All right, design view. In here, we need onMailingList. This has got to be true. And now when I run it, you should only see people that are on the mailing list.
Now, I'm not going to waste time making an actual mailing list or a mailer. You can add address fields to the tables. That's easy to do. You can make mailing labels. I cover that in my Access Beginner One class. Very simple stuff. One thing that might be handy to know, though, this is a little more of an advanced lesson, is which ones of my students do I not have a parent on the mailing list for? That might be very handy to know. Because I've only put people on mailing lists for a couple of students. I've got a lot of students in my database. A lot of them don't have parents that are on the mailing list. That might be important. We can do that with a simple aggregate query. If you want to learn more about aggregate queries, here you go, it's basically a summary query, like you add up a bunch of people's orders, that kind of thing.
Well, we can count up the number of records that are available that are on the mailing list for a particular student. How do we do that? See, a lot of this stuff is taking concepts you might already know and putting them together in different ways. That's why sometimes I cover the same topics more than once, because there's a lot of different ways to do some of this stuff. You can use an aggregate query for something you might not have thought about using it for before. All right, create, query design. All right, let's bring in the student table and the junction table. That's all the data that I need. I don't care about the actual parent records themselves. Right, the data that I need is right there. All right, bring in student ID, first name and last name. We're going to make an aggregate query, so don't bring in the star. And then bring in on mailing list. OK.
All right, next we're going to make this relationship an outer join, so I see all of the students, whether or not they've got a parent listed in the junction table. Okay, and if I run this now, there's everybody. OK, now you might see Bobby Kirk in here three times, it's because he's got three records over here. OK, one of them is on the mailing list, two of them are not. And that's important, we're going to get to that in a second. Next, we're going to turn on totals to aggregate this stuff. Now, these will all be grouped by, because we want to group by the student. That's normal. We want to see one record for Bobby Kirk, for example. Now, you might think over here that you want to count these records, you want to count how many records are on mailing list. But if you run that, it's just going to give you a count of the total number of records. What you really want to do is you want to sum these records up. Sum them up because this is what you'll get now. OK. Anybody who's got a negative one means they've got one record that is on the mailing list. Negative two means Joe McCoy's got two records on the mailing list. All right. What we care about are the zeros or the nulls.
Zeros means that Peter Scott and Adam here, for example, they all have parents, but none of them are marked on the mailing list. And Billy Williams doesn't have a parent record at all. That's why his is null. OK, and now you can use this query to determine which students you still have to go through and mark so you've got a parent on the mailing list. All right, and you can reformat this, and you can multiply it by negative 1. You can do all kinds of stuff. I'll rename this as parents on ML. Save the query. Parents on mailing list q. You can run this whenever you want. You can sort this by that if you want to. Give it a good sort so everybody shows up. But you got your null up top and all your zeros. I would now feed this through a different query and then you can say show me all the records where this is null or zero. But there you go that's going to do it for now and I think this is going to do it for this series for now.
Of course only two of these, only parts one and two, have gone public so far the rest of the comments I've been getting from members who have been watching these because remember members get to watch these as soon as they're made you don't got to wait for them to get released to the public it's one of the benefits of being a member. If you've got more ideas for things you want to see me do with this database, post them in the comments down below and if I like them I'll make more parts to the series. I like getting comments from you guys. I do read them all. I might not reply to everybody, but I do read them all.
If you want to learn more about all this relationship stuff, the many-to-many, the subforms, all the different kinds of relationships that are available, one of my more popular seminars on my website is the Relationship Seminar. Here's a link to it there. I'll put it down below. Yeah, sorry, it's not a very... it's an older seminar. It's one of the first ones I did, but it's really, really good. That's why I don't have a really cool slide for it. I've got to make a better slide for it. But check it out. You can scan a little QR code there. This covers all the different kinds of relationships you could possibly have in your database, including self-join relationships. Self-join relationships are really neat. In fact, I've got a whole separate video on them. You can check them out. You use them usually for genealogy databases, where people relate to other people. You can do that with students and parents. OK?
In fact, I thought about maybe making a part nine where I show how to take students and parents and put them together in the same table. Because you can, you can do that. And you can have people who are like you know parents of other parents. Because they're all people, they all share the same things. Right? They all have a first name, a last name, an address, a phone number. They can be on a mailing list. This way you can have people that are related to other people. It's a little more complex, but it's certainly possible and doable. And you just distinguish them with a field. Is this a parent? Is it a student? Give them a person type. You can add teachers to the same table, too, and administrators and all that stuff.
In fact, that's one of the core concepts that I cover in my ABCD, my Access, Business, and Contact database. Everybody in here is a person. You can put them in different groups. You can give them different classifications and all that different kind of stuff. Check it out. But there you go. That's going to do it for this series for now. If I decide to make a part nine, check the comments down below because I can't update the video after it's published. That's one of the YouTube things. But I can update the description down below. So if I do decide to release a part nine in the future, check down below the description underneath the video and you'll see a link to part nine if it's down there.
Thanks for putting up with my cold. I know I've had a cold over the past couple videos here with the series, but I'm starting to feel a little better, so I'm getting there. I know I've recorded most of these videos over the course of two or three days, but you guys will be suffering with my nasty voice for about a week and a half. All right, so that's going to do it for today. That's your TechHelp video. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Putting people on a mailing list
Storing the mailing list field in the junction table
Adding onMailingList field in junction table
Adding a checkbox for mailing list in parent subform
Creating mailing list functionality in student records
Using a query to generate mailing labels
Creating an aggregate query to identify students without parents on the mailing list
Counting records in an aggregate query
Grouping by student in an aggregate query
Summing records to determine mailing list status
Identifying students without parents on the mailing list
Using outer join to include all students in a query
COMMERCIAL:
In today's video from Access Learning Zone, I'll show you how to manage your student and parent database by creating a mailing list. If you don't want everyone to get Timmy's report card, this one's for you! We'll start by deciding where to store the mailing list info and work through adding a checkbox in the parent subform. Then, we'll make sure only selected parents get those important mails. Whether you need mailing labels or just want an efficient way to track who's on the list, you'll get the steps here. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper, my friends.Quiz Q1. Where should the "is on mailing list" field be stored?
A. In the student table
B. In the parent table
C. In the junction table
D. In the call sheet
Q2. Why can't you store the "is on mailing list" field in the parent table?
A. Because the parent table is already full
B. Because the parent table is only for phone numbers
C. Because a parent may be on the mailing list for one student but not another
D. Because the parent table should only contain permanent records
Q3. What data type should the "is on mailing list" field have?
A. Text
B. Date/Time
C. Yes/No
D. Currency
Q4. After adding the "is on mailing list" field, what should you do next in the parent subform?
A. Create a new subform
B. Add the checkbox for the "is on mailing list" field
C. Delete the subform
D. Add a new parent record
Q5. What is one method suggested to adjust the default value of the "is on mailing list" field?
A. Default to "No"
B. Default to "Yes"
C. Do not set a default value
D. Alternate the default value every month
Q6. What should be included in the query to create mailing labels based on the "is on mailing list" field?
A. Only parent names
B. Only student names
C. The "onMailingList" field and apply criteria
D. Only phone numbers
Q7. Why might you want to know which students do not have a parent on the mailing list?
A. To remove those students from the database
B. To ensure all necessary parents are contacted
C. To delete the parent records
D. To print report cards for only those students
Q8. What type of query can tell you which students do not have a parent on the mailing list?
A. Outer join query
B. Append query
C. Aggregate query
D. Make table query
Q9. When designing the aggregate query, what operation should be used to determine the number of parents on the mailing list?
A. Group by the "onMailingList" field
B. Sum the "onMailingList" field
C. Count the "onMailingList" field
D. Average the "onMailingList" field
Q10. What does a zero or null value indicate in the aggregate query results for the "onMailingList" field?
A. The student has multiple parents on the mailing list
B. The student has no parents on the mailing list
C. The student has all parents on the mailing list
D. The student data is corrupted
Q11. What functionality does the tutorial suggest adding to the query to refine the results?
A. Include a filter for null or zero values in the "onMailingList" field
B. Sort the result randomly
C. Remove the student data entirely
D. Add all the parents to the mailing list automatically
Q12. How should feedback be provided for additional ideas for the database series?
A. Through direct email only
B. By commenting under the video
C. By sending a physical mail
D. By posting on social media
Answers: 1-C; 2-C; 3-C; 4-B; 5-A; 6-C; 7-B; 8-C; 9-B; 10-B; 11-A; 12-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 TechHelp tutorial from Access Learning Zone is all about building mailing list functionality for your student and parent database. Specifically, the goal is to ensure that not every parent receives everything, like little Timmy's report card. Sometimes you need certain people to be emergency contacts, but not everyone needs to get the mail. Today, I will walk you through how to selectively put people on a mailing list.
If you have not yet worked through the first seven installments of this series, I would highly recommend starting there before moving on. This lesson builds on the work we've done in those earlier parts.
Let's talk about the scenario. In your database, you probably have a call sheet that lists everyone who might need to be contacted, such as multiple parents or guardians for a student like William McCoy. But not all these contacts should get a report card mailed to them. Our goal is to have control over who appears on the mailing list for each student.
Now, consider your database structure. You likely have tables for students and parents, with a junction table linking them together to represent the many-to-many relationships. The important question: where should information about being on the mailing list be stored? Should it go in the parent table? That might seem reasonable at first, but it's actually not the best choice because a parent could have multiple children, and you might only want them to receive mail for some, but not all, of those students.
The right place to store this mailing list information is in the junction table, which represents the relationship between a particular student and a particular parent. This way, a parent can be assigned to the mailing list for one child but not another. To do this, open the junction table in design view and add a new yes/no field, which I'll call "onMailingList." You can choose to default new relationships to either yes or no, depending on your preferences. Some people want all parents automatically on the list, while others prefer to add them individually. It's flexible.
Once that's set up, open the parent subform in your student form. Here, add a checkbox corresponding to the onMailingList field right in the subform so you can easily select which parents should receive mail for each student. You might want to tidy up the form's layout with appropriate headings like "Parent" and "Mail," making clear what each column means.
Now, when you pull up a student's record, such as Bobby Kirk, you'll see all his associated parents and be able to specify which ones should be included on the mailing list. You can quickly make selections - for instance, maybe only Jim Kirk should get the mail, while Carol and others do not. When working through other records, you can adjust the mailing list memberships as needed for each family situation.
Keep in mind, if you're planning to actually mail things, you'll need to make sure there is an address stored for each parent. You can do this by adding an address field to the parent table if it's not already there.
To create mailing labels, you will need to build a new query that includes only those parents on the mailing list. You can use an existing query for phone calls as a starting point, then copy and modify it to include the onMailingList field and apply the appropriate criteria so only parents marked "yes" are included. This query will then feed your mailing label reports or other mail merges as needed.
An additional useful feature is figuring out which students don't yet have any parent on the mailing list. Maybe you're just starting to use this system, or maybe some gaps have slipped through. To check for this, create an aggregate query. Bring in your student table and the junction table, set up the appropriate join to include all students (even if they have no related parents), and then bring in the student's identifying information and the onMailingList field. Use aggregate tools to group by student and sum up the "onMailingList" fields. Students with a total of zero means that none of their parents are on the mailing list, and a null value points to students without any parent entries at all. This summary is particularly helpful for quality control as your database grows.
At this point, you can further filter or sort your results as desired. Show only students with no parents on the mailing list, and quickly work through updating their records as needed.
This concludes this part of the students and parents series for now, but I welcome your ideas for future lessons. If there is another topic or enhancement you would like to see, please leave a suggestion in the comments. I do monitor all the feedback and may continue the series if there's enough interest.
If you're eager to learn more about database relationships, such as many-to-many structures and the use of subforms, I strongly recommend my Relationship Seminar, available on my website. While the video production is a bit dated, the content is thorough and covers different relationship types, including more advanced ideas like self-join relationships.
For those interested in consolidating students and parents into a single people table, perhaps for even more complex relationship mapping, that's a possible topic for a future lesson. This is actually an advanced concept explored in my Access, Business, and Contact Database series, where everyone is treated as a generic "person" and group membership or role is managed through classifications.
If I decide to create a part nine, be sure to check the description under the video on my website or YouTube channel for links, as I can't update the video itself after it's published.
Thank you for sticking with me through this series, especially while I'm getting over my cold. I appreciate your patience and participation. 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 Putting people on a mailing list
Storing mailing list info in the junction table
Adding onMailingList field to the junction table
Configuring default values for the mailing list field
Adding a mailing list checkbox to the parent subform
Labeling new checkbox columns in the subform
Testing mailing list selections for different students
Preparing data for mailing labels in a query
Adding mailing list criteria to queries
Copying and modifying queries for mailing purposes
Using an aggregate query to count parents on mailing list
Grouping aggregate queries by student
Summing mailing list status per student
Identifying students with no parents on the mailing list
Using outer joins to show all students in queries
Filtering aggregate results to find missing mailing list assignments |
