Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Create an Interleaved Report Part 3 – The Report

The database development process is straightforward but can onCartoonly be efficiently carried out if the developer follows a set sequence of steps. The order of the steps is important because each step depends on the step before it. This series of articles has illustrated this sequence. Part 1 described the results of the data analysis step where we need to gain a full understanding of the data available to meet the client’s requirement. Of course, the design process illustrated here itself depend on a complete and accurate understanding of the client’s needs.     

Even the best design in the world sometimes needs some revisions and tweaking when we start to develop queries and reports. Feedback from the Output phase of the process sometimes sends us back to revise and refine some aspect of the design.

 

Design Process Diagram

 

The query at the end of part 2 is a good example of this. As is, that query can only provide the id of a representative, not the representative’s actual name. So we need to add a another table, tblHumanResources  to the query.  This change enables the query to provide representative names for the report.

Final Query Diagray Rather than including the three representative name fields in the query grid, the first column of the query is defined by a calculation: Representative: [hrlname] & ", " & [HrFName] & " " & [hrmname].

All that remains now is to design the actual report.

Report Design Graphic

Building the detail section of the report is simply a matter of dragging and dropping fields form the field list in the correct position. The naming convention described in part 2 helps ensure that the fields are situated in correct product column and weekday Sales or Quotation  row. While the field names obscure the actual product name.

Because there are multiple controls for each product in two different categories, individual addition expressions must be created in order to display weekly totals for both Sales and Quotations. For example the expression for the weekly sales total for Widgets is:

=Int([SAPr1_MO])+Int([SAPr1_TU])+Int([SAPr1_WE])+Int([SAPr1_TH])+Int([SAPr1_FR])

Here again, the naming convention make it easy to create the expressions for each product without a lot of typing. After creating the first control (for product 1 – widgets) and typing the expression as its data source, simply make five copies. Edit the data source for each product total in turn and replace the ‘1’ with ‘2’, ‘3’, ‘4’, ‘5’, or ‘6’, depending on the product. This technique will also work for the Quotations totals.

After adding all the controls, adding appropriate labels then formatting and aligning them, and setting up report grouping on Representative, this is what the final report looks like:

Report Preview Graphic

You should keep in mind that, because queries are limited to a maximum of 255 fields, this approach will only work if 255 or fewer ‘cells’ required in the report details. This report has 60 ‘cell’ (6 products x 5 days x 2 categories) so it is will within the limit. 

Comments are closed.