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)

Sort Birthdays by Month, then Day, then by Year

Happy Birthday to me! Yes, as today is my birthday, it is only fitting that I post an IT training tutorial about sorting birthdays in Access.

To prepare for this tutorial, download our University Records database, and open a new query, by clicking on the "Create" tab, and then "Query Design."

• Add the Students table.
• Add these fields to the query: first_name, last_name, and birthday.
• Sort birthday ascending.

Your query should look like this:Here's the problem: When you run this query, it sorts first by year, then by month, then by day. Sorting like that makes sense because obviously December 25, 1953 came before October 31, 1975. However, we want our birthday list sorted by month, then day, and then year. That way we can send out birthday cards to everyone that was born in June, regardless of the year they were born.

Query Expressions
Were going to use some functions to pull out important information from our birthday field.

1. In query design view, clear the sort from the birthday field.
2. In the next column over, name a new field by typing (without quotes): "B-Day Month:"
3. Now type in the function: "Month()"
4. Now, between the parenthesis, we want to specify what value we want the month of, and that would be the birthday. So type: "[birthday]" between the parenthesis.
5. Sort this field ascending, and run your query. (final design view below)

To sort next, by day, add another column on your design view. Guess what the name of this function is...Day()!

• It should look something like this: "B-day Day: Day([birthday])"
• Sort ascending.
• Done

To learn more skills like this, sign up for one of our free Access classes. It will change your life in all the right ways.

Last names 255 characters long? Yeah, right.

Anytime you create a table in Access that includes text fields, the default field size is 255. Well, most people change this (or should!) every time they create a new field; and 255 is almost always bigger than the field needs to be.

Is there a way to change the default to something other than 255?

Of course! Its actually very simple.

1. With Access 2007 open, click on the Office Button in the top left corner.
2. When the menu opens, click "Access Options" in the bottom right corner of the menu.
3. After you have clicked Access Options, a box will open. On the left is a list of option categories - click Object Designers (and that's what you are!)
4. At the very top of these options you will see a line that says "Default Text Field Size: 255" - change that number to anything less than 255! The screen shot below shows this step.
5. That's it!
   
   

To learn more about the basics of tables and working with Access, click here to sign up for our free Access Foundations course.

Access Security Warning

Whenever you bring in a database from the internet or if you store your database on a network, you will get a security warning when you attempt to open the database in Access. It should look this:

Access is run by a programing language called visual basic (VB) and so there is a small possibility that there might be a virus embedded in the code of the database. By default, Access disables all of the "macros" (or bits of programing) so that there is no risk of a malicious virus taking over your computer. To enable the content, click on the options buttons and select the radio button entitled "Enable this content." If you don't enable the macros, some of your database's functionality will be restricted.

If you have any further questions, please sign up for our Access Foundations class!