Students & Parents 6
By Richard Rost
2 years ago
Relate Students & Parents in MS Access Database Part 6 In this Microsoft Access tutorial, we will build a report to properly relate students and parents, setting up groupings and sorting defaults for a well-organized phone list. This is part 6. 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, relating students and parents, Access report design, student parent phone report, Access group by student, student ID grouping, organizing student records, combining names in Access, Group and Sort in Access, customizing report layout, Access report print preview, fixing Access report errors, Access report header design, adding new students and parents
Intro In this video, we continue building the Microsoft Access students and parents database by creating a printable phone list report. I'll show you how to copy a report template, set the record source to a query, group and sort records by student ID, last name, and first name, add fields to the report, and format the layout for a professional look. You'll also see how to troubleshoot common issues like field naming errors and combining first and last names in a single box. This is part 6.Transcript We're up to part 6 of my students and parents database. Hope you're enjoying the series. If you haven't watched parts 1 through 5 yet, you know what to do. Go watch those and come on back.
Alright, so in the last video, we set up our query. So we've got all the information that we want and need in our phone list. Let's go ahead and build a report. Now in my blank template, which by the way you can get a copy of this off my website, I've got a blank R. This just has my default settings that I like. I've got the page size, the margins, all that stuff already set up. So I don't have to keep doing it every time I make a report. So I'm just going to copy this guy. So copy, paste, this will be my student, parent, phone, R, my report. And now we can design view this guy.
Now, report, I'm going to turn this off real quick. Reports, I find it's easier to work with reports while they're maximized. So I always maximize reports when I'm working on them. I don't know, it's just my preference. Forms, I like to see how big the form is going to be on the screen. Reports, doesn't matter. I'm going to print this out anyways or make a PDF out of it.
We're going to set the record source of this report to the query that we just built. So open up the properties by double-clicking right there where that little box is. Go to data. The record source is going to be that query, the student parent phone queue, that guy. Okay, close that.
Now I want to group this by student. I want each student to appear in order. So we're going to group by student ID so that the student appears and then his parents appear underneath him. Okay? So we're going to turn on a grouping level. Go to Report Design, Group and Sort. That's that thing that I just turned off a second ago because I wanted to show you how you turn it on. Okay, add a group. We're going to group by student ID. And I also want to sort it by last name and first name. All right, so we're going to add a sort by the student last name, then add another sort by the student first name. Okay, see how that works?
And yes, some of you who are sharp and have a lot of experience with reports might see the problem, which I'm doing it this way intentionally because in my classes, this is how I see everybody make this mistake. So just bear with me for now. Okay. All right, so when we created the student ID group header, it gave us a student ID header right here. We don't need a footer, just a header. Okay?
And here is where I'm going to put the student's name. So I'm going to go to Add Existing Fields. I'm going to bring in student first name and student last name, bring them over here, drop them like that. And I'm going to delete these labels. Let's put the last name over here and the first name next to it like so. Okay, we can shrink that up a little bit and then here in the detail section, let me delete this, that's where we can put the parents' information. This stuff here. Click, drag, drop, delete the labels. We're going to go last name, first name, and then phone number next to it like that. We'll make it pretty in a minute, don't worry. All right, close that up, shrink this like so. Looks pretty good so far.
Let's save it and let's get a print preview. You can just click anywhere up here in the ruler bar, right click, print preview, and okay, not too bad. Let's get rid of all these boxes around everything and this alternating background color, that is for the header, the student header. I want to make it so that all of the students have that gray. That looks pretty nice. Right-click, Design View. Let's get rid of all those boxes.
So I'm going to select everybody just like that, right-clicking on the ruler there. Format, shape outline, let's go transparent and let's also go shape fill transparent. These are all transparent boxes. Now for the student ID header, double click on that, go to format. I'm going to set the back color to, let's go with, let's go dot, dot, dot. Let's go with like a light gray like that one. And then the alternate back color, we're going to set the no color. So that every student is gray and then the parents underneath them are in white. I think that'll look better. Maybe bold this. Okay, save it. Let's right click, print preview. All right, it looks much better. Looks really good. Okay.
Now, I hinted to this in the last video. Let's put first name and last name together here, so it's not Kirk, big space, Bobby. I want Kirk, Bobby. Okay, right click, Design View. Get rid of the first name field. We'll make one big text box here with both of those in it. Okay, double click, and let's go to Data. And it's studentT.lastName and quote, comma, quote, quote, quote, comma, space, quote, and studentT.firstName, just like that. Now, when I hit OK, Access puts the brackets around it for me automatically. Let me zoom in again so you can see that. Okay, all right, that looks normal. That looks right. Okay, let's save it and let's print preview.
Enter parameter value student T, what's that? I shouldn't be getting this. I don't want to. What's that all about? Okay, I'm getting pound names here. Now, this is one of my biggest pet peeves when it comes to reports and access. Okay? Okay, take a look at what we got here. StudentT.lastName, this is perfectly fine. There's nothing wrong with this. If you do this in a query, it works just fine. Let's go to a query real quick. Create, query design, give me studentT. Here's firstName, lastName, and I'm going to make a new field. Let's call it full name, which is student t.firstname and a space and student t.lastname. Okay. All right. Let's hit okay. All right. Access puts the brackets around everything for me. All right. I'll zoom in again to show you. Looks good. Okay. And if I run this query now, everything works fine. No problem. What's the deal with the report? Why isn't this working in a report? It's the same thing. But when you run it, well, preview it, it doesn't work.
Alright, Sammy, definitely add this onto the access irritating list. What you have to do in forms and reports is this. You have to have studentT here because you can't just say last name. Right? You got student T last name and you got parent T last name. Here's how you fix it. This is why I wanted to make sure I left this in the video instead of just making this a field in the query. You got to do this. Look at that. Leave the bracket around the whole thing. But you got to have the studentT.firstName, studentT.lastName. This is different behavior than how it works in a query, how it works in an SQL statement. In forms and reports, you got to do it this way. It's irritating. I know. I didn't make it this way. This is just how you got to do it. Okay?
And now, we're in preview. Oh, hang on. What did I do? Oh, I didn't catch this. I've got a circular reference here, and the circular reference is because the name of the object itself is studentT.lastName. So you've got to change that. Just call it studentName. Okay, it can't refer to itself. All right, and now, there you go. Ah, type, where am I going to type error? Hold on, sorry. Now, it changed it here. See, this is where Access is trying to be helpful, and it's not. It changed the name of the control, and it also changed it down here. I mean, that's, no, I didn't want you to do that. Student t.lastname. See, sometimes that auto-renaming of stuff T dot last name. See, sometimes that auto renaming of stuff can actually be a hindrance. All right. Third time's a charm. Ready? There we go. Okay. That's what I was basically trying to show you.
And the same thing will happen with the parent name too. So we come in here and we can get rid of parent T dot first name just like that. We'll come over here. We'll call this parent name, make the control source equals parent t dot last name, all in brackets, and parent t dot first name, just like that. Hit okay. Save it, right click, print preview, and there we go. Looks good, looks good.
Now, you might be saying to yourself, self, that list is improperly alphabetized by the student name. We've got Kirk, then Spock, then McCoy; M should be above S, what's going on there? Well, we did the grouping first, then we did the sorting. If you look at our lists down here, at our levels, let me turn this thing off so you can see it better. We did the group by student first. That's going to go first. And I know, like I said at the top of the video, some of you who are astute might have caught that. But I wanted to do this so I could show you how to move this stuff around. What you want to do is first sort by last name, then sort by first name, and then group by student. Because the first name and last name is going to be the same. The only time you're going to have a conflict here is if you have two students with the exact same last name and first name, but then you want to group by student ID after you do these things. So how do we do this?
We'll come all the way over here to the right. See these little arrows here? We're going to move the student ID down. Move it down, and then move it down again. Now it's going to first sort by student last name, then by first name, and then it will group by student ID. Save it, right click, print preview, and now you'll see they're properly sorted. Kirk Bobby, Kirk Sue, McCoy, Scott, Spock, and so on. There we go. Looks are good? Looks are good.
If you want to learn more about these grouping level thingies, I got a whole separate video on them. And I cover them a lot in a lot of my different classes in my Access Expert series, tons and tons of videos on my website. Check them out. I'll put links to all that down below.
Got more feedback from you guys. The next thing that people want to see is, okay, hey, I'm adding a student. Okay, I go and I add a new student, let's call him new guy, Smith, and I want to add his parents now but they're not already in the parent list. So now I got to close this form, I got to go over here, I got to add them, I got to close this, I got to refresh. Is there an easy way to add them to this? Well, yes, we can. We can use something called a list items edit form. I'm going to show you how to do that in the next class and I'm going to show you how to make buttons to that on the main menu. It will open up a list. You can add people, change people, do all kinds of stuff. That's coming up in the next video. That will be part 7 tomorrow. So stick around, come on back.
Stick around, come on back. I guess stick around, stay on my channel, stay on my website, watch more videos while you're waiting. Or, you can sign up and be a member and you can watch it right now. But that's going to be your TechHelp video for today. Hope you learned something. Live long and prosper my friends. I'll see you tomorrow for part 7.
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:
Building a report from a query
Copying and pasting report templates
Maximizing reports for easier design
Setting the report record source
Grouping and sorting by student ID
Adding and sorting by student last name and first name
Adding existing fields to the report
Formatting fields and labels in the report
Previewing and adjusting print settings
Fixing field bracket and naming errors
Combining first name and last name in a text box
Handling circular reference errors in reports
Correcting sorting and grouping ordererrors
Adjusting alternate back color and field visibility
Saving and previewing the final report
COMMERCIAL:
In today's video from Access Learning Zone, we continue the students and parents database series with part 6. I'll guide you step-by-step through creating a detailed phone list report. First, we'll set up the report using a pre-configured template. Next, you'll learn to group and sort the data by student ID, last name, and first name. I'll also demonstrate how to organize the students' and parents' names for a clean presentation. Finally, I'll tackle common issues with form and report syntax to ensure your data displays correctly. 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. What is the first step when designing a report in Access from a blank template?
A. Setting up the page size and margins
B. Creating a new query
C. Copying the blank template and renaming it
D. Adding fields to the report
Q2. Why does the instructor prefer to work with reports maximized?
A. It allows better visualization of how the form will look
B. It makes it easier to print directly from Access
C. It provides a clearer view of report elements
D. It simplifies the process of adding fields
Q3. What should the record source of the report be set to?
A. A table containing student information
B. A query that was previously built
C. The main database table
D. An external data source
Q4. What grouping level did the instructor choose for the report?
A. Student last name
B. Parent first name
C. Student ID
D. Parent last name
Q5. After adding a grouping level, what sorting criteria were applied?
A. Student ID, Parent ID
B. Last name, First name
C. First name, Last name
D. Parent ID, Student name
Q6. What common mistake did the instructor mention other students often make?
A. Not adding a footer to the report
B. Grouping before sorting correctly
C. Using the wrong database table
D. Forgetting to save changes
Q7. How did the instructor modify the appearance of student names in the report header?
A. By changing the font color to blue
B. By combining first and last name fields into a single text box
C. By adding a background image
D. By making the text italicized
Q8. What issue arose when combining first and last name fields in a report?
A. Missing records
B. Circular reference error
C. Incorrect page margins
D. Incorrect spellings
Q9. How did the instructor fix the circular reference error?
A. By renaming the field and control source
B. By restarting Access
C. By removing the text box entirely
D. By changing the background color
Q10. When sorting students by name, what sorting order issue was mentioned?
A. Sorting by student first name only
B. Sorting by last name, then first name, and finally grouping by student ID
C. Sorting by student ID first
D. Not sorting at all
Q11. What future topic did the instructor mention for the next tutorial?
A. Creating a new query
B. Using list items edit form to add new parents without closing the current form
C. Adding images to reports
D. Exporting reports to Excel
Answers: 1-C; 2-C; 3-B; 4-C; 5-B; 6-B; 7-B; 8-B; 9-A; 10-B; 11-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 continues our students and parents database series with part 6. If you are just joining in, I recommend reviewing parts 1 through 5 first so you are up to speed.
In the previous lesson, we built a query that prepares all the data needed for our phone list. Now, we're going to create a report based on that query. I like to use a blank report template with my preferred settings already in place, such as page size and margins. This saves time by eliminating repetitive setup for each new report. To get started, I simply copy and paste the template, and rename it to reflect its purpose, in this case, the student parent phone report. I always maximize reports in the design window so I have the most workspace available, which makes formatting much easier since reports are typically printed or saved as PDFs.
The report's record source should be set to the query we created earlier. You do that by opening the property sheet for the report and assigning the record source to the student parent phone query.
Next, I want to group the report by student so each student's details come first, followed by their parents' information. This involves turning on grouping and sorting in the Report Design tools. Start by adding a group on student ID, and then add sorts on student last name and first name. It's important to group and sort the data properly so that the report displays students in the correct order with their associated parents listed underneath.
When the group header for student ID is created, we only need the header, not the footer. In this header, I'll place the student's name using the existing fields for first and last names. Often, I delete the auto-generated labels to keep the report clean. The labels and fields can be rearranged as needed for the desired appearance. Below the student information, in the report's detail section, I place fields for parent information such as last name, first name, and phone number. Once the fields are arranged, I shrink unused space to minimize the report's size.
After saving changes, I preview the report to see how it will print. At this point, I usually remove default outlines and background colors from fields and headers to improve clarity. For example, I set outlines and fills to transparent for all boxes, and apply a light gray fill to the student header for better visibility, while keeping alternating backgrounds off for consistency. Sometimes, I also bold the student's name for emphasis.
To present names together in a single field (rather than having the first and last names separated), I use a text box that combines them. In a query, combining fields like first and last name is straightforward, but in a report, Access requires you to include table qualifiers, like studentT.lastName and studentT.firstName, inside the control source. This is a common stumbling block for people working with Access reports, because the syntax is stricter than in queries or SQL statements. Additionally, you have to avoid naming a control the same as a field it references, otherwise you'll run into circular reference errors. Renaming the control, for example to studentName, resolves this.
The same approach is used for parents' names: create a text box that concatenates the parent's last and first names, label it appropriately, and adjust its control source with the full field names. After saving and previewing, names should now appear together as intended.
At this point, you may notice the report isn't sorted correctly by student name. That happens because grouping by student ID takes precedence over the sorts by last name and first name. To correct this, adjust the order of the sorting and grouping levels—move the sorts for last name and first name above the group for student ID. This ensures the report displays students in alphabetical order, as expected.
If you're interested in further detail on grouping and sorting in Access reports, I have separate tutorials available that cover these topics extensively.
Based on viewer feedback, the upcoming lesson will address how to add new student and parent records more efficiently. Instead of having to close forms and manually refresh lists, I'll introduce the list items edit form feature, which allows you to directly add or modify entries from within the main forms. We'll cover how to set this up and add shortcut buttons on the main menu in the next video, which will be part 7.
That wraps up today's guide. 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 Building a report from a query
Copying and pasting a report template
Maximizing reports for easier design
Setting the report record source to a specific query
Grouping data by student ID in the report
Sorting the report by student last name and first name
Adding existing fields to report sections
Arranging student and parent names in report layout
Formatting fields by removing outlines and background colors
Setting a custom back color for group headers
Combining first and last names in a text box
Fixing Access report errors with field naming
Handling circular reference errors in controls
Correcting report sorting and grouping order
Adjusting alternate back color in group headers
Saving and previewing the report in print preview |
