Throughout this handout, you will see the phrase "should look something like this" followed by an example.  Creating reports can be very tedious.  The examples are shown to keep you on the right track, but your report may not look exactly like the example. The position of your fields may be slightly different, or your spacing may be a little different.  All of these things can be corrected with time and patience, so do not worry if you have a minor difference.  If, however, you have a major difference, please review the steps you just completed.

The report in this handout will be created on the First Period query in the New School 3 database.  Click here to download the database. A relationship has been established based on the SSN field in the Student Info table and the SSN field in the Student Schedule table. The First Period query contains the SSN, FirstName, and LastName fields from the Student Info table, and the FirstPeriod field from the Student Schedule table.  No critieria were used in the query.

If you need to review how to create queries or relationships, please refer to the Beginning or Intermediate Access handouts at  http://learningcenter.clayton.edu/fidl/online.htm. 

Click back on the SSN text box control, then move your mouse to the top of the text box control. Your mouse pointer should change to a hand. If you click and drag. the hand allows both the text box and the label to move together, keeping their relationship to each other.  Now move the mouse to the larger handle located at the top left of either the text box or the label.  Your mouse should change to a pointing finger. Clicking and dragging with the finger pointer will move only that control.   

Click on the SSN label box, hold Shift down, and click on the other label boxes to select them.  When you finish, there should be handles on all of the label boxes. 

Click on the cut icon.

Click anywhere in the Page Header section, then click on paste.  The labels should move to the Page Header section. Your screen should look something like this:

Click anywhere in the Page Header section to deselect the labels.

This is a tedious process - sometimes you have to go back and forth many times to get things aligned just right.  Using the Grid dots is helpful, but the Align tool can help speed up the process.  

Select the SSN label in the Page Header, hold Shift down, then select the SSN text box in the Detail section. 

Click on Format, Align, and choose which side to align on.  This will align both controls at whatever side you choose.

Try to make your report look like this:

You must leave enough space between the controls, especially in the detail section, for the student's entire SSN, first name and last name to show.  This may mean that you need to increase the size of the controls, which you can do by clicking and dragging on the handle on the right side of a control. Change to the Print Preview and scroll through the pages of the report. You can change from page to page by clicking on the navigation buttons in the bottom left corner of Print Preview.  

Notice that neither of the FirstPeriod controls is aligned.  These will be moved into the FirstPeriod Header in the next step.

If you change to the Print Preview, alignment is probably close, but there is a great deal of blank space in the report. In the next step, we'll close up the sections to get rid of the white space in the report.

When you change to Print Preview, your report should have shrunk down to one page.  However, it is hard to distinguish where you change from one class to another, so in the next step we'll add a line and some formatting to make the report easier to read.

The toolbox contains icons for many things, including some unbound controls such as lines and labels. Unbound controls are in no way connected to the underlying table or query.  You can move them or change them to suit you without worrying.

Click on the line icon on the toolbar.  Drag your mouse just under the controls in the FirstPeriod Header to draw the line.  If you hold Shift down while you drag, you will be assured of a straight line. 

Preview the report.  The line helps, but it would be better if it were thicker, if the controls were bolded and if there was a space between First and Period. Go back to the Design View to make these changes.

Each control has properties.  Click on the Properties icon on the Report Design toolbar.  Since the line was selected, the Line Properties window should appear.

Click on the Border Width box, then click on the list arrow that appears to the right, and select 2 pt.  This will make the line fatter and easier to see.  Preview the report.

Change back to the Design View.  If both the label and text box are still selected, click somewhere to deselect.  Then select only the label box (be sure about this!) We need to put a space between First and Period.  There are two ways to do this.

Either select the label box, click on Properties, then change the caption to read First Period, or

Click once on the label box, then click again (2 single clicks, not a double-click). This should put you into the Edit mode for the label.  Put a space between First and Period.  

Either method will work.  What's important to remember, though, is that you are doing this on the label, NOT the text box.  If you ever should change a text box beyond repair, delete the text box and its corresponding label, click on the Field List icon, and drag the field back into the Report.  You'll then have to move and edit it back to its proper location and formatting.  

A good clue that you have changed a text box is this: when you preview the Report, you get a Parameter message.  This is usually what happens when Access can't find a bound control that's in the report, but not in the underlying table or query. 

At this point, the report should look something like this in Print Preview:

If the lack of a space between Chem and 101 or Eng and 101 bothers you, you would need to go back to the Student Schedule table and change all the records, adding spaces appropriately.  

Click on the Label tool on the Toolbox.

Drag and draw a box in the Report Header section.  The size of the box really doesn't matter - we can adjust it later.

As soon as you left your finger off the mouse, you'll go right into editing the contents of the label.  Key in First Period Student Roster, then click someplace outside of the box to get out of the editing mode. Preview the report.  The title needs to be bigger and bolder. Go back to the Design View.

Select the label box; handles should appear around it. Select 18 pts and Bold on the Formatting toolbar.  However, as soon as you do this, the text becomes too big for the box. 

Click on Format, Size, To fit.  This should adjust the box size, although you may have to drag the handle at the far right to increase the length of the box.  

While the box is selected, you could also change the font face, color, fill or back color and alignment by using other icons on the formatting toolbar. 

Move the label so that it aligns at the left side of the report. Check it with Print Preview.

The report fits all on one page and it is readable.  Note how the students in each class are grouped by the name of the class they are taking.  This is the Grouping Header (FirstPeriod Header) at work.

The section that a calculated control is placed in plays an important role.  For instance, if you wanted to find the sum of all invoices for companies in a particular state, where the field is called Invoice, the formula =sum([Invoice]) would be placed in a text box in the Grouping header for State. Think of this as a subtotal.  The exact same formula placed in the Report Footer (the last thing in the Report) would produce a grand total.  

Formulas used for calculated controls are very similar to those used in Excel.

Our example has a calculated control in the Report Footer that counts the number of students in our roster. To create this:

Click on the text box icon on the Toolbox, then click anywhere in the Report Footer section.  The text box and its accompanying label will appear. The text box has "Unbound" in it.

Click on Unbound, then key in this formula =count([LastName]).  This will count the number of last names in the detail section. Note the use of both the parentheses and the brackets and their position.  Also the name of the field must be spelled identically to the way it is in the underlying table or query.

Select the text box (the one containing the formula), then click on the left align icon on the Formatting toolbar.  Numbers align to the right by default.  By changing the alignment to left, there will not be as big of a gap between the label and the number.

Click in the label box, delete the contents, then key in Total Students.

Align the boxes at the left side of the report, then switch to Print Preview.  The total should be 10 and your finished report should look something like this:

Close the report. Save it as First Period Student Roster.

If you get a message that some of the area of the report is outside the printable area, take a look at the width of the grid.  It can be adjusted by dragging the right side of the grid.  You can also change the paper to landscape by clicking on File, Page Setup.

Watch out for typos in field names.  Again, a big clue is the Parameter query message. If that happens, go back and start looking at field names in the report.

If you try to open a report and you get a message that tells you the table or query that the report is based on no longer exists or the name may have changed, stop and think.  Did you change the name of a table or query lately, or delete a table or query?  If that's the case, the report will not longer function. A lot of times, users see a typo in a table or query name, so they rename it, not thinking about the consequences.  If the table or query still exists in the database, try renaming it to the previous name and see if that makes your report work.