A Report on the Purchasers at Madam Curry
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
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.
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
So 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.
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
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.