Students & Parents 5
By Richard Rost
2 years ago
Relate Students & Parents in MS Access Database Part 5 In this Microsoft Access tutorial, we will learn how to create a comprehensive call sheet by properly relating students and parents in your database, adding parent phone numbers, and using queries to generate reports listing each student with their corresponding parent. This is part 5. 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 CoursesUp Next
Keywords TechHelp Access, student-parent relationship database, Access query design, phone list generation, inner join vs outer join, student contact information, parent contact information, Access report generation, junction table setup, calculated fields Access, ambiguous outer joins fix, linking tables Access, phone number field design
Intro In this video, we continue our Microsoft Access students and parents series by creating a call sheet that lists each student alongside their parent contacts. I'll show you how to add phone numbers to the parent table, design a query to connect students with their parents, and adjust inner joins to outer joins so all students are displayed, even if they don't have a parent listed. We'll also talk about common join errors, formatting names in queries, and saving your query for future reporting. This is part 5.Transcript Today's part 5 of my students and parents video series. What does that mean? Well, go watch parts 1 through 4 if you haven't already. Today we're going to work on some reporting. We're going to make a nice call sheet, so we've got all of our parents listed next to the students that they belong to. Got to call little Scotty Scott and say, "Hey, Mom, you got to come pick up your kid. He's sick." Well, it's easy to find him on the call list. All right? All right, here we go.
Well, the first thing we've got to do is put some phone numbers in here. Now I'm going to assume that you don't want to call the students, even though nowadays even elementary kids have cell phones, which blows my mind. But let's go to the parent table. Let's design this guy. And let's put a phone number in here.
I like to keep phone numbers as short text, not as numbers. Lots of reasons why I talk about them all in my Access Beginner One class. Basically, if you're ever going to do math on it, then it can be a number. If not, it should be text. Okay, all right, save that, close it. Let's put some data in here. Let's just put a bunch of numbers in. Doesn't matter what. Yeah, I know, I'm typing in seven-digit numbers. I'm old. And we're going to put one more parent in here. We're going to call him, let's just put Malcolm Reynolds in here. All right, whatever. And we're going to make sure he doesn't have any students associated with him. You'll see why in just a minute.
And while I'm at it, I'm also going to put a student in here. Let's put in here Billy Williams, and we're going to make sure he doesn't have a parent associated with him. OK. Now, in case of an emergency, I want to be able to have a list of all my students alphabetical, and I want to be able to see all of their contacts, all their parents associated with them. So that screams query. Let's make a query. Query design. Don't need this property sheet. Let's bring in the student table. Now the only way to get from the students to the parents is you have to go through the junction table. So bring in this guy. Notice it makes the relationship for us automatically. And then bring in the parent table. That's how you can relate a student to a parent and vice versa.
Now I'm going to bring in just the fields that I might want to have on the report. So student ID, first name, last name. Don't need anything from the junction table. Let's bring in the parent first name, last name, and phone number. Don't think I'll need the parent ID. The student ID might be handy because students do have IDs and sometimes if you got two Bill Smiths in your school, you might need to know which one it is. So the ID can be handy. You can add your own student code, whatever you want. I've got whole separate videos on doing that.
Now, if I run this right now, there's everybody, but you're going to note that there's somebody missing. Remember, I added in Billy Williams. Billy Williams isn't showing up in my student list. OK, why is that? Well, that's because these are inner joins, which means you have to have a matching record in all three tables for the data to show up there. If you want to see all students, whether or not they have an associated parent, then you have to set this up as outer joins.
I've got a whole separate video on this topic if you want to learn more about this. But essentially we're going to double click on the join line right there and we're going to say, see normally it says only include rows where the join fields from both tables are equal. I want to click on this one include all records from student T and the records from the junction table where the join fields are equal. So I want to see all of the students whether or not they have a matching record in this table.
Now, that's not enough because watch, if I run this now, I get an error message. It says the SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join, then include that query in your SQL statement. Now, what that's saying is that it basically wants you to make a query out of this first, then perform this join in another query. Or you can just get rid of the ambiguity (I can't talk today) by making this one an outer join as well.
You got one outer join in here; you got to make them both outer joins. Now, if I run it, it works and there's Billy Williams. Now we're still not seeing Malcolm Reynolds, but that's okay because for this particular query, we wanted to see all of the students and their matching parents. If you want to see all of the parents, even if they don't have students, you got to go the other way. That's where you'd flip these arrows. Then you'll see all of the parents and the students that match them. For this one, I want a list of the students. And now I can see, oh, hey, we don't have Billy Williams' parent contact info. It's maybe important to figure that stuff out. Got a kid in our school and we don't know where he belongs. That's not a good thing. All right.
But now we have all the information that we need to make our phone list. Now you might be looking at these names here, and you might be seeing that there's student t.firstname, student t.lastname, parent t.firstname, parent t.lastname. Yeah. Honestly, at this point, you could join these into a calculated field to put first name and last name together, which I normally would do. But I want to save this because I want to show you one of my particular pet peeves that I get, that I see all the time when we get the reports. And this drives my students crazy. And I will show this to you when we build the report. So I'm going to leave it like it is. But yes, I personally would join these together in first name and last name in here.
But we're going to leave it, because I like to sometimes do things broken so I can show you the fix. Because if I run into it and lots of my students run into it, chances are you might run into it too. All right, let's save this bad boy. We're going to call this the student parent phone queue. And now we're ready to make our report, which we will get to in tomorrow's video. So tune in tomorrow, same bad time, same bad channel. Or if you're a member, you can watch it right now because I'm going to record it in just a few minutes.
But that is going to be your TechHelp video for today. Oops, wrong slide. There we go. Part five. That's the one for the whole series. Part five folks, as we get over this cold and any more concert... uh... that's going to be a TechHelp video for today. Hope you learned something. Live long and prosper friends. I'll see you tomorrow part six.
A special thank you and shout out to our diamond sponsor Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.
TOPICS:
Adding phone numbers to parent records in a table
Designing a new query
Setting up inner joins in the query
Changing inner joins to outer joins
Creating a query to view student and parent details
Executing and debugging SQL statements in queries
Formatting names in query results
Saving and naming the query
COMMERCIAL:
In today's video from Access Learning Zone, we dive into part 5 of our students and parents series. First, we'll create a call sheet to match parents with their students for easy referencing. We'll start by adding phone numbers to our parent table, then link students to their parents using a query. We'll set up outer joins to display students even if they don't have associated parents and address common join errors. Finally, we'll prepare to create a comprehensive report listing all student-parent contacts. 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. Why does the instructor keep phone numbers as short text fields instead of number fields in the parent table?
A. Phone numbers can be used in mathematical operations.
B. Phone numbers are best stored as numerical values for calculations.
C. Phone numbers might need formatting, which is easier with text.
D. Phone numbers as text fields save more space in the database.
Q2. What should be done to view all students regardless of whether they have an associated parent in the query?
A. Use inner joins for all tables involved in the query.
B. Set up outer joins for the student table and junction table.
C. Use filters to include optional parent records.
D. Create multiple queries and merge them manually.
Q3. What issue arises when running a query with only one outer join in Access?
A. The query runs slowly but displays all results.
B. The query generates an error message about ambiguous outer joins.
C. The query only shows null values for parent details.
D. The query crashes and closes the application.
Q4. How can the ambiguity in outer joins be resolved in Access according to the video?
A. Convert all outer joins to inner joins.
B. Keep one join as an outer join and the other as an inner join.
C. Convert both joins to outer joins.
D. Remove all joins and manually enter data.
Q5. What should you do when you want to include all parents, even if they don't have associated students?
A. Keep the joins as inner joins.
B. Reverse the outer joins' direction.
C. Add extra fields to the student table.
D. Create a separate query for parent information.
Q6. Why might it be beneficial to join first name and last name fields into a calculated field in a query?
A. To make the query more complex.
B. To adhere to database normalization standards.
C. To simplify and improve the readability of the report.
D. To ensure data is stored more securely.
Q7. What is the main goal of the query created in the video?
A. To generate a list of only parents without associated students.
B. To identify students who should be removed from the database.
C. To create a list of all students and their associated parent contact information.
D. To find students based on their activity records.
Answers: 1-C; 2-B; 3-B; 4-C; 5-B; 6-C; 7-C.
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 continues with part 5 in our students and parents series. If you have not seen parts 1 through 4, I recommend going back to review those before proceeding, since today we will be working on reporting.
Our goal is to build a call sheet that conveniently lists each parent alongside their associated students. This way, if you need to reach out to a parent – like if a student is sick and needs to be picked up – you can quickly find the right contact information.
The first task is to ensure our parent table has phone numbers. I prefer to store phone numbers as short text rather than numbers. The primary reason is that, unless you plan to perform mathematical calculations with the data, fields like phone numbers should be textual. For more details on why, check out my Access Beginner One course. Once the field is added, go ahead and enter some sample phone numbers. I often use simple seven-digit numbers for ease, even though nowadays even young students have their own cell phones.
For demonstration, I am also adding a new parent, Malcolm Reynolds, without any students attached. You will see why shortly. Similarly, I am adding a student, Billy Williams, who will not have a parent associated with him. It is important to test scenarios where data may be incomplete.
To prepare a list of students with their contacts, we need a query. Open a new query in design view. We will bring in the student table first. Remember, to connect students and parents we must use the junction table, so add that next. Notice that Access automatically creates the necessary relationships. Then bring in the parent table. This setup allows you to relate students to parents and vice versa.
For the fields, I suggest adding the student ID, first name, and last name. From the parent table, include the parent's first name, last name, and phone number. The student ID can be handy, especially if there are students with identical names. You might also consider a custom student code.
If you run the query now, you will find that students who do not have parent records in the junction table, such as Billy Williams, do not appear in the results. The default join type, known as an inner join, only returns records where a match exists in all involved tables.
To see all students, regardless of whether they have parents assigned, you need to use an outer join. Double-click the joining line between the student and junction tables and choose the option to include all records from the student table, as well as matching records from the junction table. This ensures all students are listed, with or without associated parents.
However, if you only change one join to an outer join, Access will display an error message about ambiguous outer joins. To resolve this, you need to change both joins so that they are outer joins. Once this is done, rerunning the query should display all students, including those like Billy Williams who do not have parents listed.
Parents who have no students attached, such as Malcolm Reynolds, will not appear in this particular query since we are focusing on all students and their possible parent contacts. If you ever need a list centered on parents, with or without students, you would need to reverse the direction of the outer join.
Now that the query pulls all the required information, you might notice that separate fields for first and last names are less than ideal for reporting. It is common to concatenate these fields into a full name, but I am intentionally leaving them separate for now. I frequently encounter student questions about report formatting, so I will address this in a future lesson to illustrate how to fix it.
Save the query with a descriptive name, such as "student parent phone query." This prepares us to create the actual report, which will be the focus of the next lesson. For those following along, be ready for the report-building session soon. If you are a member, you can access the lesson right away.
For more step-by-step guidance, you can find a complete video tutorial covering all these instructions on my website at the link below.
Live long and prosper, my friends.Topic List Adding phone numbers to parent table
Entering sample parent and student data
Identifying students without associated parents
Designing a query with student parent and junction tables
Selecting fields for the call sheet query
Understanding inner joins and missing data
Converting inner joins to outer joins for full student lists
Resolving ambiguous outer join SQL errors
Saving and naming the student parent phone query |
