This is the continuation of earlier Article published on this subject last week. Click here to visit that Page.

Last week we have explored the sample data processing methods and tried to approach the same problem in different ways to arrive at the same result. Reports are the main output component that goes to the User with critical information for analysis of business activities and for making serious business decisions. Transforming raw data into meaningful form and providing them on Reports is a real challenge of any Project.

If you attain some working knowledge of different types of Queries available in MS-Access you can do most of these tasks without touching the VBA Code. Depending upon the complexity of processing steps you can use several Queries, create intermediate temporary Tables and use those tables as source for other Queries to overcome issues that may arise as hurdles in the processing steps.

We will look into such an issue here so that you will know what I meant by hurdles in creating the final Report. Such complex data processing steps can be automated by sequencing each step in a Macro and run that Macro from a Command Button Click or from VBA Sub-Routines.

It is absolutely necessary to create and maintain Flow Charts of process that involves several Queries and Tables for Reports. You may create hundreds of Queries in a Database for different Reports. After some time we may forget what we did for a particular Report. If the User points out any flaw in the output then we can easily back track the steps using the Flow Chart and debug the problem.

Last week I have raised a question as how we will show Revenue, Expenses and Profit/Loss month-wise if the sample data are added with Year and Month Fields. The image of the sample Table (Transactions2) Source data is given below:

The image of the Report Output Created and presented to you last week is shown below:

Click to Enlarge

We can transform the sample data given in the first image above into the Report output form in the second image in two steps. The numbers appearing as Suffix to the Column headings represents the Month Value. For example, Revenue1 is January Revenue and Profit/Loss2 is of February.

We can arrive at the above result in two steps and the SQL String of those two Queries are given below:

Query Name: Method2_1

TRANSFORM Sum(Transactions2.Amount) AS SumOfAmount
SELECT Transactions2.Location,
 Transactions2.Year
FROM Transactions2
GROUP BY Transactions2.Location,
 Transactions2.Year
PIVOT IIf([type]="R","Revenue","Expenses") & [Month];

1. Copy and Paste the above SQL String into the SQL Editing Window of a new Query and save it with the name Method2_1.

 

2. Open the Query and view the output as how it is transformed with the Cross-Tab Query.

    

   Query Name: Method2_2

    

   SELECT Method2_1.Location,
    Method2_1.Year,
    Method2_1.Revenue1,
    Method2_1.Expenses1,
    [Revenue1]-[Expenses1] AS [Profit/Loss1],
    Method2_1.Revenue2,
    Method2_1.Expenses2,
    [Revenue2]-[Expenses2] AS [Profit/Loss2]
   FROM Method2_1;
   

 

3. Copy and Paste the above SQL String into the SQL Editing Window of a new Query and save it with the name Method2_2.

    

   We are using the first Query as input to the second Query for the final Report output.

 

4. Open Method2_2 Query and view the output.

 

Even though we could arrive at the sample result with the above two Queries we have to modify the second Query every time to create Profit/Loss Column when new data records are added for subsequent months. The P & L Report if created using the second Query then that also has to undergo changes to add Revenue, Expenses and Profit Columns for the new period.

This cannot be a good method when we are expected to automate every process in the Database so that the User can prepare Reports with the click of a Button.

We can automate this data processing task permanently with the following few simple steps:

1. Create a second Report Table with Revenue and Expenses Fields for all twelve months.

 

2. Change the second Query created above (Method2_2) as an append query and add the output data of available months into the Report Table.

 

3. Create a SELECT Query using the Report Table as source to calculate Profit/Loss Values for all twelve months only once. This is possible because we have all twelve month’s data fields in the Report Table, even if some of them will have only zero values till December.

 

4. Design the P&L; Report with all twelve months Revenue, Expenses&Profit/Loss Fields using the Query created in Step-3 as source.

 

Once you implement this method you don’t have to make any changes to the Queries or Report when new data records are added in the Source Table. All you have to do is to automate this process, like deleting the old data (for this action we will need a Delete type Query) from the Report Table and bringing in fresh Report data from source table Transactions2.

So, let us get to work and do it.

1. Create a Table with the following Field Structure and save it with the name PandLReportTable.

    

   The Data Fields R1 to R12 and E1 to E12 will hold Revenue and Expenses Values respectively for the period from January to December.

    

   NB: Don’t forget to set the Default Value Property of all Number Fields with 0 values as shown in the Property Sheet below the Field Structure. This will prevent from adding data fields with Null Values when data is not available for those fields. Remember, when you write expressions using Numeric Fields with Null values combined with fields with values; the end result will be Null.

    

   
   

    

   We have modified the first Query given above for simplifying the data field names.

    

2. Copy and paste the following SQL String into a new Query's SQL Editing Window and save it with the name Method3_l.

    

   TRANSFORM Sum(Transactions2.Amount) AS SumOfAmount
   SELECT Transactions2.Location,
    Transactions2.Year
   FROM Transactions2
   GROUP BY Transactions2.Location,
    Transactions2.Year
   PIVOT [type]&[Month];
   

 

3. Copy and paste the SQL string given below into a new Query and save it with the name Method3_2.

    

   INSERT INTO PandLReportTable
   SELECT Method3_1.*
   FROM Method3_1;

 

4. Copy and paste the following SQL String into a new Query and save it with the name PandLReportQ.

    

   SELECT PandLReportTable.Location,
    PandLReportTable.Year,
    PandLReportTable.R1,
    PandLReportTable.E1,
    [R1]-[E1] AS P1,
    PandLReportTable.R2,
    PandLReportTable.E2,
    [R2]-[E2] AS P2,
    PandLReportTable.R3,
    PandLReportTable.E3,
    [R3]-[E3] AS P3,
    PandLReportTable.R4,
    PandLReportTable.E4,
    [R4]-[E4] AS P4,
    PandLReportTable.R5,
    PandLReportTable.E5,
    [R5]-[E5] AS P5,
    PandLReportTable.R6,
    PandLReportTable.E6,
    [R6]-[E6] AS P6,
    PandLReportTable.R7,
    PandLReportTable.E7,
    [R7]-[E7] AS P7,
    PandLReportTable.R8,
    PandLReportTable.E8,
    [R8]-[E8] AS P8,
    PandLReportTable.R9,
    PandLReportTable.E9,
    [R9]-[E9] AS P9,
    PandLReportTable.R10,
    PandLReportTable.E10,
    [R10]-[E10] AS P10,
    PandLReportTable.R11,
    PandLReportTable.E11,
    [R11]-[E11] AS P11,
    PandLReportTable.R12,
    PandLReportTable.E12,
    [R12]-[E12] AS P12
   FROM PandLReportTable;
   

    

5. Design a Report using PandLReportQ as Source File, like the sample design image given below.

    

   The sample image shows Columns of January and February only. But, you may design the Report for all twelve months in similar way. The Value from Year field is used for creating Headings so that it automatically changes when the Report is printed next year without modification to the Report.

    

   
   

   Click to Enlarge

    

   The above Report in Print Preview is given below.

    

   
   

   Click to Enlarge

    

   We will automate the P&L; Report preparation procedure to get updated data on the Report when new data of Revenue and Expenses are added to the Source Table. As part of the automation procedure we need a Delete Query to remove the earlier data from the PandLReportTable before adding revised data into it.

    

6. Create a new Query with the following SQL String and name the Query as PandLReportTable_Init.

 

DELETE PandLReportTable.*
FROM PandLReportTable;

Isn't it easy enough to prepare the P&L; Report with the above simple Queries and with a supporting Report Table for any number of Locations that you add to your main Source Table Transactions2. As you can see now you don’t need any complicated programs to prepare this Report.

If you look at the Queries we have created we can see that there are only two Action Queries among them (Delete and Append Queries). We can put these two Queries into a Macro to automate the P&L; Report preparation easily. But first, let us examine the logical arrangement of this Report preparation procedure with a Process Flow Chart.

Click to Enlarge

In Step-1 the PandLReportTable_Init Query removes earlier Report Data from the PandLReportTable.

In Step-3 the Append Query (Method3_2) takes the Cross-Tab Query output from Step-2 and adds them to the Report Table PandLReportTable.

We have already written expressions in PandLReportQ SELECT Query to calculate Profit/Loss Values. The Report will automatically get all available data from this Query and other Columns on the Report will remain empty till fresh data Records are added in the Source Table Transactions2.

If we can add both the Action Queries into a Macro (or VBA Subroutine) then the User can click on a Command Button to run it every month to create the Report with added data within seconds.

The sample image of the Macro with the Action Queries in sequence is given below for reference:

Click to Enlarge

If you can further simplify this procedure please share that idea with me too?