In this article I will focus on the data structure behind the interleaved report. The data illustrated is only a small subset of what you would find in a typical full-scale application. In order to produce the report described in part 1, we need to know:
- who made the sale or quotation
- what product was involved
- was the action a sale or a quotation?
- when did it happened
These descriptions suggest the need for certain tables:
- product lines
- sales activity
- sales activity types
Here’s how the tables are organized in the database:
Obviously this is a minimal structure designed to illustrate what’s needed for the reports. The Human Resource table has only three name fields whereas in a real application much more information about people would be required.
The data is normalized to the third normal form. The problem here is to create a report from a flattened version of the data. The client’s requirements in part one specified that the report require one column for each product group sales on one row followed by a similar row or product group quotations. Keeping in mind also that the desired report should have some vertical space between each pair or rows and that there be one pair of rows for each day of the week, it should be apparent that a simple datasheet or even a continuous report will not meet these requirements.
Before designing the actual report it is first necessary to flatten the data. Crosstab queries are specifically designed to do exactly that. The data we will need in the query includes the sale representative, the date, the type of action, and the number of times the action happened on each day. So the basic crosstab design will look like this:
Notice that the query has included a field to restrict the results to a particular time frame (July 26 – 30, 2010). In this example the criterion has been hard-coded. In a working database the query would most likely refer to a user-specified time frame from a form or memory value.
What’s missing from this query, however, is any sort of specification of the activity type (sale or quotation) and the relevant day of the week. To further refine the specification, it is necessary to add two additional Where fields to the query.
Actually, in order to report on each product for each day of the week, a total of ten queries will be needed (5 days x 2 action types.) Before creating these queries, however, it will speed things up greatly if you specify column headings in the queries properties. (Remember that cross-tab data has to have pre-defined column headings if the data is to be used in a report.
This basic query is only needed as a model for the queries that will ultimately be used in the report. By setting the query up with as much generic detail as possible, you can quickly create the ten queries simply by modifying the product and day of week criteria and using save as repeatedly for each set of criteria. Subsequent work with the queries will be much easier if you use a consistent naming style for these day/product queries.
The names used in the example database are:
Sales queries: Monday to Friday
Quotation Queries: Monday to Friday
Notice how these names follow a consistent pattern. The names all begin with qxtbActivity, indicating that the query is a crosstab of the Activity tab. The next two characters after the first underscore indicates whether the query is retrieving sales or quotation data. The final two indicators indicate the weekday that data represents. Using consistent naming patterns like this simplifies both the creation and identification of the queries. For example, you can use a text editor to search and replace the distinguishing characters to create the other queries in the set.
These queries will return the sales and quotation date for each sales rep for each day on which each rep, respectively, had activity. However, in the unlikely event that a rep had no sales or no quotation activity, that rep will be missing from the days sales or quotation data or both. What we really need is for the rep to be included in the query output with zeros for each product column.
What we need is a list of sales reps who were active in the reporting period. A simple query of representatives in the activity table grouped by rep with a criteria limiting the date range will do the trick. The SQL of the query is:
WHERE (((tblActivity.ActDate) Between #7/26/2010# And #7/30/2010#))
GROUP BY tblActivity.Rep;
Now this query can be joined to each of the crosstabs to produce a set of ten selection queries. These queries all follow a similar pattern and use an outer join to ensure that all representatives will be included whether or not they have sales.
In these queries, the product names have been “aliased” so that they can be distinguished as to sales or quotations on a day by day. Again a consistent naming pattern helps speed up the creation process. For example, QUPr1_MO is the alias for Monday’s Widget quotations and SAPr1_MO is the alias for Monday’s Widget sales. The Pr1 simply indicates that this is the first field of products which is widget data. The queries themselves are names “qsel” followed by the part of the crosstab name after “qxtb”
For example, the query in the diagram above is named qselActivity_QU_MO, indicating that it will provide data for the Monday column of the report.
We need one more query to give us the data that the report will display. Think of this as an umbrella query that will combine the results of the ten queries just created into a single query:
The order of the sub queries doesn’t matter much but it will be easier to work with query when designing the report if the field list are arranged left to right, Monday to Friday. Each table should be joined to the table on its right by the Rep field. In this diagram the field list are arranged in two rows simply to make it easier to display in this article. In the actual query, qselActivity_QU_MO is to the right of qselActivity_SA_FR.
The final query has 61 columns – rep plus one column for each of the SAPr… and QUPr… fields.
In the final part of this series we will see how to display this query data in a grid format grouped by representative.