Students & Grades 2
By Richard Rost
14 months ago
Grades in MS Access: Data Entry & Crosstab View Part 2 In this Microsoft Access tutorial, I will show you how to create combo boxes for student and assignment data entry, link subforms by student and assignment IDs, and set up composite keys to prevent duplicate entries. This is part 2. 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 grades Microsoft Access, Access tutorial subform, student form tutorial Access, student data entry Access, create combo box tutorial, Microsoft Access combo boxes, assignment ID Access, junction table Access, assignment form Access, Access average function, composite key Access, preventing duplicates Access, Richard Rost Access tutorial
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today is part two of my students and grades series. So, if you haven't watched part one yet, go watch part one and then come on back.
Alright, so in the last class we got our tables set up, we got our student form set up, and we've got our subform set up. Now we're going to replace these guys here with combo boxes. So, instead of seeing an ID, we can actually see information that we can use. Let's get rid of these two things.
Let's start off with the student combo box first. I'm going to bring up a combo box right there. Drop it in here. I want the combo box to find values from another table or query. Yep, we're getting them from our student table. We're going to bring over both of those fields. Next, sort it by student name. That's up to you. Next, that's what it's going to look like. The key column is hidden. Next, we're going to store that value in the student ID of the junction table. We're picking a student ID from the student table, and we're going to save it in the student ID of the junction table. Got it? Okay, next. What label would you like? Doesn't matter. We're going to delete it anyways.
Let's delete it right now. Right there, it usually comes in all squished like that. Alright, we're going to put that there. One of my pet peeves with the combo box was that it doesn't give you the chance to make a nice name for the combo box. I don't want it to be called combo 64; let's call it student combo. Can you call it student ID? Yeah, you can. This is one of those things where later on when you get into programming, if you know the field is student combo, then you know you can play with other properties that combo boxes have like columns and stuff.
Alright, let's do the same thing for the assignment ID. There's a lot of little tricks and reasons why I do things that I just try to get you in the habit of now because you'll thank me later on. I don't want to explain everything right now.
Alright, so combo box, get the value from a table or query. Where are we getting this list from? Assignment T is correct. We're going to bring over both fields. We're going to sort by the assignment name. Or if you have a date or something in here, that's fine too. Next, that's what it looks like right now. Next, we're going to store that in the assignment ID of the junction table. Next, hit finish, delete the label. We're going to slide you up into here and resize it so it fits perfectly.
Okay, I'm going to see these aren't all exactly the same size. So, I'm going to click on them, right-click, and go to size to grid. I know it's down below the screen. Let me move this down so you can see it a little better. Right-click, size to grid. That's going to snap all those guys on that grid dot lines in the back there.
Alright, one more thing: since I added these guys after that was already there, let's adjust our tab order. I'll hit auto-order. Oh, and look, see this always reminds me to combo 66. See, I always forget to do it, assignment combo. Usually, when I get back to it, assignment, I can type today, assignment combo. Usually, when I go to refer to this from somewhere else and I see it's combo 35, I'll change it at that point.
Okay, so let's resize this, resize this. We did our tab order. Alright, everything looks good. Save it. Close it. Now, we're ready to put that inside the student form. Right-click, design view. Right from here, click, drag, drop. Boom, there it is.
Alright, I want to delete that, slide you over here. I'm going to make this a little taller because right now it would only show a couple of records. So, we're going to do this with it. Grab that, bring it down like so. This doesn't need to be quite so wide. Alright, save it. Close it. Open up the student form and there you go.
Okay, now this relationship should be made for us. So, if I come down here and add another quiz, it's add quiz 2. Look at that. Jim goes in there automatically as the default value for the next record. Now, when you're entering stuff down here, you can just skip over the student. What are we missing? Test 3. Alright, and that's all down arrow, by the way. If you're a keyboard, a helmet like me, all down arrow opens up a combo box. Now you can put in his grade for that. Same.
Okay, now let's do the same thing, but for the assignments. So, we can see the assignment up top and then put all the grades down below for each of the students. This is nice to look up what a particular student's grades were. We can also look up a particular test or quiz. That's maybe the easiest way to do the data entry. When the students all just finished a test, you open up the test form and put them all in there.
Again, I'm going to copy my single F, copy paste. This time, we'll call it the assignment form. We'll do the same thing pretty much, design view. I'm going to change this background color so we don't confuse it with the other one. We'll make it green. A little more greener. Let's go. A tiny bit greener. Okay.
This guy is now going to be bound to the assignment table. We'll just change these fields here. So, control source for this one is the assignment ID. Copy. Paste. This is going to be the assignment name. Copy. Paste. Alright. Save that. Once again, bring in the subform, click, drag and drop. We can get rid of that little label that comes in with it. Okay, then we'll resize this and resize this.
Now, here's the nice thing about the way subforms work. On the other form, it was linked by student ID. But on this one, it's linked by the assignment ID. If you click on the subform object itself, see how I got the border around it? I've selected the subform object. Don't click inside of it because now you're inside the form. Click off of it, and then click on it just once. You'll see you got this orange border around the whole thing. It says subform up here. Now look, see where it says link master fields, link child fields. It saw that there's an assignment ID on this form, so it made that what it's linked by.
Whereas on the other one, if we save this, back into the other form, the student form, design view. If you look at this guy, click on this and bring up the property sheet. You can either double-click on that border, or you can click up here on the property sheet. Look at this one. This one is linked by the student ID. See? So it's smart. It knows which one you're looking at.
Now look at this. Assign it. Okay. Here's test one. Now we can put everybody in who did test one. Got an 85. Who's next? Chekov got a 72. Hoorah got a 93. Whatever. See? I can look at the test, or you can look at the student. This is the best way I've found to simplify data entry for this stuff.
Oh, hey, while we're at it, why don't we put an average down on the bottom? Right down under here. Copy. The grade. Paste it down below. I'm going to slide it under here. Right there. This is going to be a form footer total. But we're not going to use total. I'm going to use sum. We're going to use average, AVG. It's not like Excel. In Excel, it's the word average. In Access, it's AVG. So, we'll click here. We'll go to control source. It's going to be equals AVG. Grade. Here, I'll zoom in so you can see it better. Just like that. AVG. Grade. And also change the name. I'm just going to make the name AVG grade. Don't actually put the function up there. Then we'll maybe put a label down next to it. Copy. Then I'm going to click on this field and paste it. Then it attaches to that label. See? The label name is now that label 69. I don't worry about changing label names unless I'm going to refer to them somewhere else. Or change their colors or something. Save that. Close it.
Now if you open up one of these guys, look at that. There's the average for Jim for all of his stuff. You can do the math and make sure it's right. If you open up the assignments, there's the average of all the students for that particular test. That's pretty cool, huh? If you want to, you can turn off these navigation buttons and record selectors, whatever you want to do. There's all kinds of, I got lots and lots of other videos to teach you how to do all that stuff. You can make a list of all of the assignments or a list of all the students. Kind of like what I did with the main menu here where I have a customer list. See? You can do the same thing with your student list. I cover this in the video for the TechHelp free template, which I'll include a link down below. If you want to learn more about form footer totals, these things we did down here, I got a whole separate video on that too. Go watch this one.
One more thing for today, what we're going to do is set up something called a composite key so that this doesn't happen: if you've got your student up in here and you come in here and you put in test one again and a 90, oh wait a minute, I shouldn't do that. I shouldn't have the same test in there twice. So, we can set up something called a composite key to make sure that doesn't happen. A composite key is basically an index on two fields in the same table.
Alright, here's how you set that up. Go to the table, this guy here, the junction table, open it up in design view. Go to indexes. Now there's a bunch of other indexes in here including the primary key. Don't worry about all the stuff that's up there already. We're going to make our own. We're going to add our own composite key down here. So, we're going to give it a name. We're going to call it no duplicate assignments or whatever you want to call it. We're going to start it off with the student ID and then come down to the row below that and put in the assignment ID.
Now click on this guy and then down here where it says unique, change that to yes. That's all you got to do because since this is blank, that means that this is part of this. So, these two fields together make this key, right, this unique index and it's going to make it so that it's unique so that you can't have any record where these two things are the same. That's what this does. Save this, close it, save it, close it and now if you open it up again, let's go back to the form this time though. Open up the student form. If I try to do that same thing again where I put test one in a second time, it yells at you. Because you got duplicate values in an index, so it'll prevent you from doing that. That's called a composite key. If you want to learn more about that, check out this video. I cover it in a lot more detail.
Alright, so we got most of our data entries good, but now you're saying, hey Rick, I thought we were going to do something where I can look at it like a spreadsheet view, right? I want to see my data looking like an Excel spreadsheet like Candice said, yeah? Okay, alright, we're going to do that next.
Tomorrow. So, tune in tomorrow, same bad time, same bad channel, or you can join as a member and watch it right now. That's all for today folks, that's your TechHelp video for today. Hope you learned something. Live long and prosper my friends. I'll see you tomorrow for part three.
TOPICS:
Replacing fields with combo boxes
Setting up a student combo box
Configuring combo box to find values from a table
Sorting combo box by student name
Storing combo box value in the junction table
Renaming combo box for easier programming
Setting up an assignment combo box
Adjusting sizing and tab order of combo boxes
Inserting a subform into the main form
Linking subform by assignment ID
Configuring subform link master and child fields
Adding average grade calculation in the form footer
Setting up a composite key to prevent duplicate entries
Creating and configuring an index for composite key
COMMERCIAL:
In today's video, we continue our students and grades series with part two. First, we setup combo boxes to easily select student and assignment IDs, replacing plain ID fields. I'll show you how to add combo boxes for both students and assignments, link them to the junction table, and arrange them neatly with 'size to grid'. We'll adjust the tab order, and rename controls for clarity. Next, we'll create an assignment form to track grades and calculate averages, both for individual students and assignments. Finally, you'll learn how to implement a composite key to prevent duplicate entries. 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 main objective of replacing the elements with combo boxes in the student and grades form?
A. To improve the visual appeal of the form
B. To allow easier data entry by displaying relevant information instead of IDs
C. To increase the security of the form
D. To reduce the loading time of the form
Q2. When creating a student combo box, what information is brought over from the student table?
A. Only the student names
B. Only the student IDs
C. Both student names and student IDs
D. Only the student's grades
Q3. Why do we rename the combo box to something more descriptive like "student combo"?
A. To avoid programming errors in the future
B. To make the form look more professional
C. To shorten the name for better readability
D. To comply with database naming conventions
Q4. In the tutorial, what properties of the combo box needed to be adjusted before using it in the form?
A. The background color
B. The name and the value selection
C. The font type
D. The border style
Q5. Why is it necessary to adjust the tab order after adding new elements to the form?
A. To improve the form's aesthetics
B. To ensure a logical and efficient navigation flow for data entry
C. To make the form compatible with keyboard navigation
D. To update the form's properties with new elements
Q6. How do you synchronize forms and subforms by specific IDs?
A. By adjusting the layout settings
B. By linking the master and child fields in the subform properties
C. By using VBA code
D. By using a built-in Access wizard tool
Q7. What formula is used in Access to calculate the average of grades in a form footer?
A. =SUM(Grade)
B. =AVG(Grade)
C. =TOTAL(Grade)
D. =AVERAGE(Grade)
Q8. Why is a composite key important in the junction table?
A. To link the main form with the subform
B. To enforce referential integrity between tables
C. To prevent duplicate records of the same student assignment pair
D. To index the table for faster searches
Q9. What does setting the "unique" property to "yes" for a composite key ensure?
A. It allows multiple entries for the same student assignment pair
B. It prevents multiple entries for the same student assignment pair
C. It shortens the loading time for the table
D. It hides duplicate records
Q10. What should you do if you receive an error for having duplicate values in an index while using the form?
A. Restart the Access application
B. Check and fix the composite key settings
C. Ignore the error and continue entering data
D. Recreate the form from scratch
Answers: 1-B; 2-C; 3-A; 4-B; 5-B; 6-B; 7-B; 8-C; 9-B; 10-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 covers part two of our students and grades series. If you haven't watched part one yet, I suggest you start there and then return to this tutorial.
In our last session, we set up our tables, student form, and subform. Today, we're going to replace some of the current elements with combo boxes so that instead of seeing IDs, we see usable information.
We'll start with the student combo box. First, let's add a combo box and set it to find values from another table or query. We'll use the student table and bring over the necessary fields, sorting by student name. We'll store this value in the student ID of the junction table. After setting it up, we'll rename the combo box to something more intuitive, like "student combo," to make future programming easier.
Next, we'll create a combo box for the assignment ID using similar steps. We'll retrieve the values from the assignment table, sort by assignment name, and store them in the assignment ID of the junction table. After setting this up, we'll rename the combo box to something like "assignment combo."
Paying attention to the tab order, we'll ensure everything is lined up correctly. Sometimes Access doesn't give ideal names to combo boxes by default, so making these adjustments now will help us later.
After that, we need to place this updated configuration into our student form. We'll adjust the form's height for better usability, ensuring all records are easily viewable. This setup will allow us to add quizzes or tests seamlessly, with default values set for the next record entry.
We can do a similar setup to view assignments and the grades for each student. We'll create an assignment form and follow similar design principles. The form will be bound to the assignment table, and we'll change the appropriate fields. After creating the combo box for assignments and ensuring everything is in place, linking the subform by assignment ID will allow us to view all student grades for that assignment.
Now let's add an average grade field at the bottom. This will involve calculating the average grade using the AVG function in Access. We'll place the calculated field in the form footer and ensure it displays correctly.
Finally, to prevent duplicate entries, we'll set up a composite key in our junction table. This composite key will involve indexing both the student ID and assignment ID together to ensure uniqueness.
If you try to enter the same test for a student more than once, Access will prompt you with an error, thanks to the composite key setup.
That's it for today's tutorial. 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
Replacing fields with combo boxes
Setting up a student combo box
Configuring combo box to find values from a table
Sorting combo box by student name
Storing combo box value in the junction table
Renaming combo box for easier programming
Setting up an assignment combo box
Adjusting sizing and tab order of combo boxes
Inserting a subform into the main form
Linking subform by assignment ID
Configuring subform link master and child fields
Adding average grade calculation in the form footer
Setting up a composite key to prevent duplicate entries
Creating and configuring an index for composite key
|
