A Report on the Books at Madam Curry
We just went through developing a report based on the purchasers and
what they've bought and spent. Now we want to get a report that
organizes the information around the books. We want to display what
books are being bought and how much has been spent on each.
But we also want to display some basic information about the
Again you may choose to download the database that I have already been working on.
If you want to duplicate my steps you can do it in this database or one of your own creation. But you are going to want to name your QUERIES and REPORTS slightly differently than the names I've used so you do not overwrite those that already exist. But all the other names should match as exactly as you can. Or you can work in a copy of madam_curry.mdb.
The first step is to open the database and select the queries. Generally, I choose to first develop a query to base a report on. The report design wizard can often create the underlying query without you doing it or creating an underlying query at all. But creating an underlying query has its advantages.
You will be able to sue the same query we used in the last report called
We are going to work in Query Design View so that you should see something like the following pulling the query up in design view.
So, to review, three tables have been added for the query
The fields you want are
Again the notation table_name.field_name helps you identify what tables the fields are contained in.
The field we use for sorting, date_of_purchase, will be overridden in the report.
Now we launch the report wizard in the reports section of the database. The first step will eventually look like the following after you have selected the underlying data source and moved the appropriate fields that you want available for the report.
|Again, you should notice that we have selected to
base our report on the query we have just created and we have moved
all the fields to the right to make them available for the report.
Then click on Next.
The next step allows us to qualify how we want the report to be grouped/organized. We are doing it by books so the form should look like the following.
|So we have reorganized this query
to be based on the title or book.
Now you get an option/form to add other groupings. We do not need to add any other groupings.
Then you get the option of sorting the entries by prioritizing up to four different fields. We want to sort by
so the form should look like the following.
|But we also want to make sure we get some summary
options so you want to click on the
This will give you a form like the following, which also shows how you should fill out the form.
|I've selected the Sum
option because I want to see the total amounts each purchaser has
spent. I have also selected to see
Detail and Summary so that you can see how much has been
spent on each book by each purchaser in addition to the totals.
On subsequent forms/steps I then selected the style
and landscape printing, which isn't really necessary when we only have this few fields to display.
You can choose a particular print display, and I chose Formal.
Then you want to name the report which I called
When the report is executed you should get something like the following.