Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Students & Grades 2 < Students & Grades | Students & Grades 3 >
Back to Students & Grades 2    Comments List
Pinned    Upload Images   Link   Email  
Transcript
Richard Rost 
          
5 months ago
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.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Students & Grades 2.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2025 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 2/15/2025 2:22:47 PM. PLT: 1s