Access 2007 Tutorial 4.1 Reports

Uploaded by djdates on 10.01.2010

In this video, we are going to take a look at how you create reports in Microsoft Access.
A report is basically a query that has been formatted to be printed or displayed on the
screen. Right now, I am looking at the relationships
view of my database. I have a directors table that has the directors of movies and I have
a movies table that contains the title, rating, and the year of the movies in my database.
I have two association tables. I have a cast Association table that associates actors to
the movies they are in. I have a rental association table that associates rental customers to
the movies that they had rented. I will now close out of the Relationships View, click
on the Create ribbon and click on the Report Wizard.
For my first report, I want to create a list of all the actors in the database. In the
Report Wizard, I will select the actor table and select the actor first name and the actor
last name. I can do this by double-clicking on the fields
I want or I can click on the field and click on the greater than symbol which will select
the field. Once I have selected my fields, I can click on next.
At this point, I can add a grouping level. We will talk about grouping levels with a
later report, so I will click on next. I can set my sort order, sorting by actor last name
first. I can set the type of layout for the report. I can select the style for my report.
I can click on next, title the report, and click on finish.
So I have created a report with all the actors in my database. This report could easily be
printed and if I print it, it will print on eight pieces of paper.
Let's create another report. I click on the create ribbon, click on Report Wizard, and
this time, I want to create a report that tells me what movies were rented by each customer
with a count of the movies rented. I will select the customer field, and then I will
select the movie table, and select the movie title, rating, and year. And then click on
At this point, I can choose how the relationship between these two tables impacts the reports.
I can view my data by customer, showing the customer information with the movies underneath
each customer, or I can view my data by the movie and then see the customers underneath
each movie.
I want to view my data by customer, so I will click on next. I can also add a grouping level,
but we will talk about grouping levels at a later point.
I want to sort my report by the title underneath each customer. The layout and style are fine,
so I will finish the Report Wizard.
My report shows me each customer that is in my database and it shows me what movies they
I will now go into design view and select the title in the detail view. When I look
at a report, the report is divided into headers, footers, and detail sections. The page header
contains my columns. The section header has the customer name. The detail section has
the title, rating, etc. The page footer shows the current date and the number of pages.
I will right-click on the title in the detail section and click on total, selecting count
by records. Notice that it has added a section footer with a count and it also added a report
footer. It is going to show me two counts: it will show me a count of movies for each
customer and will show me a total count of all movies for the full report.
I will go back to the report view and now I see at the end of each customer section,
the number of movies they rented. When I go to the end of my report, I also see the total
number of movies rented by all the customers.
I want to create another report. This time the report will tell me what movies were in
the database for each rating.
I will go back to Report Wizard. For the data that I need, I only need to select the movie
table. I will select the title and the ratings fields and then click on next. I want to group by the ratings, so I will
select the rating, and then promote it as a grouping level, so it will show the rating
and each title underneath. I will then click on next, and sort the titles underneath each
rating. The layout and style are fine, so I will finally finish my report.
Notice that when I view my report, I see that there is only one movie in the database with
a G rating, while there are a number of movies with PG, PG-13, and R ratings.
Again, I will right click on the tab for my report and select Design View. I want to add
a count on my title. First deselecting the objects, I can select my title detail object
by itself. I can right click on the title and add a total to count the number of records.
I can again view my report and I see that there was one G movie, 14 PG movies, 29 PG-13
movies, etc.
I will create one last report.
This report will be a little bit more complicated. I want to know who are the actors in my database
and what are the number of movies each actor has been in. I do not want to display the
titles of the movies. I just want a simple list of the actors names and what are the
number of movies they have been in.
So the first thing I will do is create a query that answers my question. I will select the
Create ribbon and click on Query Design.
I will begin selecting fields from the actor table and cast tables. I do not need to select
the movie table because I can see from the cast table how many movies each actor was
in. I want to select the actor last name, the actor first name, and I will select one
of the cast fields, it doesn't matter which one, and then I will click on totals and count
the number of cast records.
This will tell me how any times an actor was cast in the movie.
I will run my query to make sure it looks how I expect it to look.
So I see each actor with a number of movies that they been in. Sean Astin, for example,
appears in three movies in my database, two of the Lord of the Rings movies, and the movie
Now that I know that my query works correctly, I am going to close my query and I will save
it, giving it a name, and now I will create a report. I will click on the create ribbon
and click on Report Wizard.
Instead of selecting a table, I will select my query. I will pull over all of my fields
and click on next. I am not going to add any grouping levels to my report. I will sort
by actor last name, but I will accept the default layout and style, and then I will
finish my report.
Now I see the actors in my database and I see that Sean Astin was in three movies.
If I want to change the appearance of my report, I can go into Design View. For example, if
I want to re name the column, I can type in a different column name. I can move the column
header. Notice I can see my page footer. Notice that it is using the concatenation operator,
the “and” symbol. And it is using quote marks to delineate the word “of” as a
string of text between the page numbers. This creates the line stating page 1 of 2, page
2 of 2, etc. at the bottom of each page of the report.
I can click between fields and expand the fields making fields larger. When I go back
to report view, I can see the changes that I have made.
Obviously creating reports in Access is very much like creating queries in Access and we
can even build off from our existing queries. As you can tell from Design View, there many
more options that we can elect to use in our reports and we can create reports that can
be much more complicated than what we have seen just now.