detailed reports

Usually in business applications reports are quite a bit more complex than what we saw in the previous section. It will frequently happen that a list from a single table will be required but most reports will be more detailed than that.

The usual situation is that you will be required to pull data together from several tables, make multiple calculations on them and then format the results for the printer.

There is also the added complication that the audience must be taken into account when designing the format: a report to be distributed at the shareholders' meeting will probably not have the same look as an inventory report for the warehouse manager.

the sales statistics report

This is one of the most common applications. The garage manager needs to know precisely how the business is doing. How much money came in yesterday, last month, this year so far? How profitable are we? Which services are making the most money?

To produce that we will probably have to look at most of the tables in the database. And like we did in building forms, we'll start with a query.

The information we need to calculate sales is all on the invoices we produced. We will probably need information on customers (who are the best customers in terms of revenue), parts (which parts are the most profitable) and all of that for specific periods of time.

First, create a general query pulling all the relevant information together.

Now, add the calculated fields. To simplify processing later, concatenate the customer's first and last names:
CustomerName: [first name] + " " + [last name].

Then, calculate the actual selling price for each line item:
ActualPrice: ([invoice details].[sell price]*[quantity])-([invoice details].[sell price]*[quantity]*[discount]/100)

So, you create the query and obviously you run it to test it for errors and, what do you see?

It seems to work fine except that when there's no discount there's no amount calculated. Why not? Because of a special entity called NULL. The discount field has a value in it or it has nothing, null. Now, null is not the same as 0 or space, which are characters. When you calculate with null, even if it's a subtract, the result is always NULL.

The simplest solution to the problem is to go back to the design phase and put in a default value of zero for the discount percent in the invoice details table. Thus, in the query discount amount will calculate as 0 and, no problem with the rest.

Sometimes you don't have access to the design and you have to live with what is there. In that case you must account for the possibility of a null value in the calculation. Here we use the IIf() function as shown:

Next we'll look at creating a report with this general query. Of course there will have to be changes included later: for example, we haven't accounted at all for dates yet and the results will contain all invoices from all time but that will be easily corrected with the appropriate criteria.

a report from a query

The first thing you do is: create report from query. Then edit the report to get all fields to show, like you did in the previous section. You'll notice that you now have an additional section in the report: the Invoice number Header. That was done automatically because the query contains multiple tables.

Now, use the Group & Sort button to generate a group footer for each invoice. In the footer create a Text box to calculate the sub-total. In the text box write: =Sum([ActualPrice]). You can copy that text box and paste it into the report footer. The value calculated depends on where the text box is: in the Invoice footer it's the sub-total for that invoice and in the report footer, it's the total for all the invoices.

When you discover that you forgot to include a calculation into the report design, there's no need to panic! You simply go back to the query, you change it, then you save it and return to the report design. Now, using the Field List you insert and format the new field.

In the next lesson we'll see how to create a specific kind of report: the Invoice. In that case what we'll do is get the information as it appears in the Invoice form and then print a hard-copy of it.