Welcome to another TechHelp video brought to you by accesslearningzone.com. I'm your instructor, Richard Rost. Today is part three of my attendance series where I'm teaching how to take attendance like a teacher.

If you haven't watched parts one and two, go watch those first. We did those last week, and then come on back here for part three.

So, we got our attendance table built. We've got our attendance form where we can see everybody. We have a button here to add students for that date. After you click the button, you have to close this and then reopen it again. Then there they are, right there, 6/2. That's today's date.

Yes, I'm going to show you how to make it so that we don't have to close and reopen the form ourselves. But that's going to involve a little VBA programming, so we're going to get there. First, I'm still trying to get through the expert level stuff which doesn't require any programming. Then we're going to add some extra enhancements that do use some VBA.

Today, we're going to make a nice little report to show all this stuff. We've got Friday here, Saturday, let's say these people were in class on Saturday, and then on Monday, these people were in class.

Now I want a nice report that shows each student and what days they were present, and then a count of absences, and it'll look nice. Let's build that today.

But first, a couple of prerequisites. First, I want you to watch my form footer totals video. I know this video talks about form footer totals, but the same thing works in reports. Whether you're in a footer in a report or a footer in a form, it doesn't matter. It's the same kind of stuff, so go watch this.

Also, go watch my video on grouping levels in real reports because we're going to group it by each student. These are both free videos. They're on my website and they're on my YouTube channel. Go watch those and then come on back.

First, we're going to need to add a couple of fields to our form here because we're not going to do attendance of everything in the database. I want a start date and an end date, so I'm going to copy this guy, copy, paste (Ctrl+C, Ctrl+V), and come down here. This will be our start date. And we're going to copy and paste that guy again, and this will be our end date.

Now, we have to give the boxes names. Don't forget, those were just the captions that I just edited. We have to change the boxes, so double click. This guy is going to be "start date," and this guy is going to be "end date."

If you want to put some default values in here, that's fine too. Let's say normally you're doing like the past week, so you could say in here for the default value, let's make this one today's date minus seven. Then for the end date, let's do today plus one because I know sometimes if it's a Friday, you want it to go to Saturday.

You want to include everything up to that date, or default it to today, and then you just have to make sure in your criteria that you add one to it so you get all those dates. Let's do that, let's do date minus seven and then date.

Save it, close it, open it. Looks pretty good. Nothing crazy, you're new here.

Now I'm going to make a query that's going to show me all the attendance records between these dates. Create - Query Design. What's going to be in here? I'm going to bring in my attendance data, and then I'm going to bring in my student queue because my student queue has my student information in it - just the students in here.

We're going to join this together by customer ID. If it doesn't make the join for you, sometimes when you're working with a query, it doesn't automatically make that join, so we're just going to go click, drag, drop. Everyone that's in this table should also be in this table, so you shouldn't have to worry about your join type here.

I want to see the customer ID, I want to see the class date time (that's where we're going to put our criteria in just a minute), I want to see if they're present, and I also want to bring in their student name because I want to see that on the report.

Now, that criteria we're going to get from these fields back here that we just created. The criteria for class date time: I'm going to come in here to criteria, I'm going to zoom in (Shift+F2). It's going to be greater than or equal to Forms!MainMenuF!StartDate and less than, not less than or equal to, just less than, Forms!MainMenuF!EndDate + 1. Is that plus one?

If I put in Monday through Friday, you want it to include all of Friday because they might have times in there. If they have 9 p.m., you want to make sure, because end date by itself would be just at midnight, so you want to add one to it and make it less than that. So you're actually saying give me everything from Monday at midnight all the way up through Saturday at midnight, but not including Saturday at midnight. That's why we do it this way - less than this date plus one. You want to assume they're typing in Friday when they really want all of Friday.

Hit OK.

Save this as "MyAttendanceQ."

There we go. There's all of our dates, and if you want to just test this, you can change this guy to "31". Make sure you leave the field. If you just change the date here and hit the button or run the query, it doesn't update that until you leave that field. It's another thing you can control with VBA, as you can do a refresh of this data before opening up the report.

Now if I run it, you'll see just "31" and the second. That's perfect. I'm going to put this back to "26" or whatever it was before so we see everything. There's good; we got three dates for everybody. Let's make the report now. We're ready.

In my TechHelp free template, I have a blank report down here that I use for making new reports. It's just got my margins and my page width and all that stuff set up so I don't have to keep doing it in every report that I make. I strongly recommend you do something like that. I'm going to copy and paste this to "MyAttendanceR" for "report".

Right click, design view, and here we are. It's already set out to the width of an 8.5 by 11 sheet of paper, got my margins all set. I'm going to open up the report properties, and I'm going to bind this by changing the record source to that MyAttendanceQ that I got. That's where it's going to get its data from.

Now I can add fields to it. I'm going to go to report design, and then "Add Existing Fields", and there they are. I usually keep this guy around for formatting, but we don't need it right now. I'm going to bring in - you don't really need the customer ID for this unless you want to see it. If you've got two Joe Smiths, you might want a customer ID for clarity. I'm going to skip it for now. I'm going to bring in these three guys, which is - click on the first one, hold the shift key down, click on the last one, let the shift key go, and then click and drag and drop. There we go. There's all three of those things.

I'm going to make these guys go side by side like that just to see what we're dealing with here. Put that there, put that there, shrink up that detail section.

Save it and do a print preview. I'm going to right click here on the bar and go to print preview. See what we got.

First things first, let's get rid of these boxes. I hate the boxes. Back to design view. Close that field list, we're done with you. Click on that, shift click on that, go to "Shape Outline," set that to transparent. That should look a lot better.

Take a quick peek. I got print preview up here on my quick launch toolbar. That looks a lot better now.

Next, I want to group this based on the student so every student and all of their dates show up together. How do we do that? Close print preview.

You could group on student name, but again, you run into that problem where if you've got two Joe Smiths, then it's going to put them together. I like to group on customer ID. Go over here and do "Group and Sort," then add a group based on customer ID.

Also, turn a group footer on because it gives you a group header, but I want to click on "More" and then pick "With a Footer Section" so we get a footer there too.

I'm going to squeeze up that header, they were from the report header, the page header, and the page footer, report footer. We might do something with those later, but for now we don't need them.

So here's the customer ID header. I'm going to move the student name up here so it's grouped by each student, because a customer and student is the same thing from lesson one. We could probably get rid of that label too. Delete the label, slide that over there like that.

Now, the class date times are going to be here. I don't need the label next to it. Maybe put the label above it, slide this over here, maybe make this a little bit bigger. This definitely has to be bigger to fit the class date time. There we go.

I'm just going to take that label off of there, cut it off, click up here, and paste it. Then slide it over here like this. This will be "Class Date/Time" or just "Date," whatever you got in there. Copy, click, paste it again, copy, paste. Sometimes you can copy-click-paste, sometimes you can't. Then "Present".

We're going to do that, left align this because it's a date, so it's going to come in right aligned. I like it all left aligned.

We can turn off this grouping in total right here. Don't click this, as this will actually remove that and delete that group. You want to click on that guy to close the pane.

Save it, and you can close it and reopen it if you want to. Right click, print preview. And look at that, there we go. Looking pretty good.

I hate this alternating band stuff. I really don't like that. So let's do this: go into design view again, for the customer ID header, double click on this guy. It will bring up the properties. Go to "Format - Alternate Back Color", set that to "No Color". Same thing with the footer.

Now the footer, I want to actually change the footers. Let's set this to "No Color" and let's make this just slightly gray. Click on the dot dot and go with like a light gray. That's where we're going to put our totals – they'll break up each student a little bit. Maybe we'll make this bold and just a smidge bigger, maybe go 14 point like that.

All right, starting to get there. See, this is one of my exceptions to the save-it-close-it thing. Reports, I tend to just be lazy and go in here and go to print preview because it's a pain to switch between them the other way.

That looks a lot better. Richard Rost, James Kirk, William Riker, Worf, Red Sparkly, Mr. Spock, class date/time and present there. Looking pretty good.

Maybe shorten it up just a little bit, maybe get rid of just a little bit of extra space in there. Let's see what this looks like. Save that, print preview. There we go. Now we got everybody on one page. That's perfect. You can put a line in there. There's all kinds of stuff you can do now.We're ready to put our totals in here. I want a count of how many items total, so there would be three for each of them. How many were they present for, and how many absences do they have? Then we will do the whole thing and total up the entire class.

We will do all of that in tomorrow's video, so tune in tomorrow, same bat time, same bat channel.

We have a bunch of other stuff to do as well. I got some good ideas from some students, and we will throw them in and make even more videos. Some of it is going to be some VBA stuff.

Again, a lot more cool stuff is coming, members. I am recording right now, so you can probably watch it tonight.

All right, that's going to do it for part three. Hope you learned something. Live long and prosper, my friends. I will see you next time.

TOPICS:
Adding start date and end date fields to the form  
Setting default values for date range selectors  
Creating a query to filter attendance by date range  
Joining attendance data with student information in queries  
Applying date criteria using form field values in queries  
Saving and naming attendance queries  
Testing date filters in queries  
Copying and modifying a blank report template  
Binding a report to a specific query as a record source  
Adding and arranging fields on the report  
Removing outlines from report controls  
Grouping the report by student using customer ID  
Adding and configuring group header and footer sections  
Moving labels and formatting report fields  
Adjusting alternate background and footer colors  
Formatting group footers for summary sections  
Previewing and adjusting report layout for optimal fit

COMMERCIAL:
In today's video, we're continuing with part three of the attendance series where you will learn how to build a report that displays each student's attendance with date ranges, counts, and grouping. We'll discuss adding start and end date fields to filter results, setting default values, joining attendance and student data, and grouping reports by student ID for accurate results. I'll show you how to clean up the design, adjust formatting, and set up headers and footers for a professional look. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.