Create a Basic Report in Access

This tutorial will go through the basics of creating a report in Microsoft Access 2007. These skills are covered more in depth in our Access Foundations class and our Advanced Reports SkillBuilder.

To follow along with this tutorial, download the University Records database used in our classes. If you aren't familiar with this database, take a moment to open the tables and look around to see what we're working with.

The Report
We are going to make a report that lists Students, the basic information about the courses they are taking, and the faculty that teach those courses.

Note: As you are working throughout this tutorial, it will be best to have all objects (tables, queries, reports, etc.), except for the one you are working on, closed. This will save you some headaches caused by possible Access errors

Decide the Record Source
Where is the data for your report going to come from? Will the information on your report come from one table, or multiple tables? If it is only going to come from one table, select the table by clicking on it, and skip the next step. If the record source is going to by multiple tables, consider making a query to base the report off of. That's what we're going to do.

Make the query. Using the query wizard(Create tab -> Query Wizard), let's create the record source for our report.

1. With Simple Query Wizard selected, click OK.
2. In the Tables/Queries box, select Table: Students.
1. Double click the essential fields (first and last name, byu_id, and net_id)
3. Select Table: Courses
1. Add these fields: course, transcript_title
4. Select Table: Faculty
1. Add these fields: first and last name
2. Just for fun, lets add the teacher's salary. I think it would be nice for students to see how much their teachers are making :)
5. Click Next.
6. Detail or Summary Query? Choose Detail, and click next.
7. Click Finish. Your query should look something like this...
   

Close the query, and make sure it is simply selected (It should be orange in the object navigation).

Create the Report
Okay, we're ready to make the report. Go to the Create tab and click Report (pictured below). This is to create an "Auto-Report."Grouping and Sorting
There is no need to see the same name over and over again, so we will group similar information, and then sort. We could have done the sorting in the query, but I wanted to keep the data as raw as possible.

To group classes by student, click Group and Sort (make sure you are in Layout view).At the bottom of your report, you will have a new section.Click Add Group.

We could group by name, but there are students with the same name, and it would group their schedules together. So let's group by something unique, like BYU ID. Click byu_id.To sort by last name, click Add a Sort, and then select Student_last_name.

Once you do, you'll notice that it is still sorting by BYU ID first. We need it to first sort by last name, and then group by BYU ID. To change the order of grouping and sorting, click the up arrow on the right.

We can still see information repeating over and over again, like the students' names. To put all the repeating information in the group header, we need to go to design view.Now that we are in design view, de-select the table by clicking in a blank spot of the report. All the orange-ness should go away.

We are going to delete a few things and add them back in a little bit. Click First name, and push delete. Do the same for last name, byu id, and net id.

Creating a new text box
In the "Controls" group of the ribbon, click Text Box. Then, your next click should be right under where it says "byu_id Header." Click where it now says Text 23, and push delete. Where it now says Unbound, type the following:

=[Students_first_name] & " " & [Students_last_name]

Format the text to look something like a header (bold, bigger font, etc.) It should look something like this:Now switch to layout view, and in the AutoFormat group, choose one of the prettiest formats you can find. Resize the columns so it all fits on one page by clicking one of the columns, and then clicking and dragging the right edge (the orange one) to the left.

The final report
Congratulations, that's it! Here is the final report (in the Print Preview view)