A Report on the Books at Madam Curry

 

Some Background.  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 purchasers.

Again you may choose to download the database that I have already been working on.

madam_curry.mdb

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

qry_orders_amount_joined.

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

  • books
  • orders
  • purchasers

The fields you want are

  • purchasers.bill_first_name
  • purchasers.bill_last_name
  • books.title
  • orders.date_of_purchase
  • orders.amount

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

  • bill_last_name
  • Bill_first_name
  • date_of_purchase

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

Summary Options

button.

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

Stepped Layout

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

rpt_orders_by_purchasers

When the report is executed you should get something like the following.