Programming & Security

Posted: 22 Nov 2009 09:21 AM PST

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:

Source Table image with Year and Month added

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

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];

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

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

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;

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

Query

Report

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:

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

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

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.

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.

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

R12

E12

Revenue

Expenses

January

December

NB:

Default Value

Number

Property Sheet

Null

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];

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;

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;

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

January

February

Year

Report

Revenue

Expenses

Delete Query

PandLReportTable

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.

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: