A Report on the Purchasers at Madam Curry

 

Some Background.  Now we want to get a report that lists each of the purchasers, what they've each purchased and how much they've spent on each title and cumulatively.

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.

We are going to work in Query Design View so that you should see something like the following after adding the tables and fields.

 

 

Notice this query is very similar to other queries we have done in the Madam Curry database.  The biggest difference is it includes the field from the orders table that relates the amount each customer spent on the book, shipping and taxes.

So 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.

Since you have experience with queries we will not do more than save this with the name qry_orders_amount_joined.

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 purchasers so the form should look like the following.

 

 

In the next webpage we will reorganize this query to be based on the title or book and we will select something different at this step.

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

  • title
  • 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.