Continued from Last Week's Post.
This is the continuation of an earlier article published on this subject last week. Click here to visit that page.
Last week we 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 a 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 the 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, sequencing each step in a Macro, and running that Macro from a Command Button Click or from VBA Sub-Routines.
Process Flowcharts.
It is absolutely necessary to create and maintain Flow Charts, of processes that involve several Queries and Tables, clearly indicating the Input and Output in each step, arriving at the final Report Data. 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 backtrack the steps using the Flow Chart and debug the problem.
Last week I raised a question as to 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:
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 represent the Month Value. For example, Revenue1 is January Revenue and Profit/Loss2 is in 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.
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;
- 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.
- 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 a Profit/Loss Column when new data records are added for subsequent months. The P & L Report is 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 the 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 the source.
Once you implement this method you don't have to make any changes to the Queries or Reports when new data records are added to 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.
Designing a Report Table
- 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 for the period from January to December respectively.
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 adding data fields with Null Values when data are 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 above for simplifying the data field names.
- 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.
The sample image shows the Columns in January and February only. But, you may design the Report for all twelve months in a similar way. The Value of Year field is used for creating headings so that it automatically changes when the Report is printed next year without modification to the Report.
The Report in Print Preview.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.
- 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 how you don't need any complicated programs to prepare this Report.
Actions Queries in Macro.
If you look at the Queries we have created we can see that there are only two Actions 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 the sequence is given below for reference:
If you can further simplify this procedure, please share that idea with me too?
What happened to only needing two simple queries? Now I understand what you're trying to achieve I have a much simpler way...
ReplyDeleteUse a select query but use the pivot table view.
First we need to get the Expenses and Revenue transposed using a crosstab query, then add in the calculation for the P+L. In my previous comments I used the query "qry_test_part1". This query transposed the E and R fields.
In the final query we add the P+L fields by subtracting the Expenses from the Revenue fields. Let's call this qry_test_part2. The SQL for qry_test_part2 is as follows:
SELECT qry_test_part1.Location, qry_test_part1.Year, qry_test_part1.Month, qry_test_part1.E, qry_test_part1.R, [R]-[E] AS [P+L]
FROM qry_test_part1
GROUP BY qry_test_part1.Location, qry_test_part1.Year, qry_test_part1.Month, qry_test_part1.E, qry_test_part1.R, [R]-[E];
Now, in this query, change the view type to pivot table and drag the fields in as follows:
Location as a Row field
Month as a column field
Year as a Filter field
Expenses, Revenue and P+L fields as a total field
You will now find the details laid out exactly as you require. Simply add this query to a command button on click event in vba as follows:
DoCmd.OpenQuery "qry_test_part2", acViewPivotTable
That's it, you will find each time a month is added to the table that it's added automatically. Once the year changes, the user needs to simply change the filter option for year.
Can you please present your solution in complete form with SQL Strings of both Queries exactly the same way as you have explained above. The result should match the sample image (2nd image from top on this page) given above.
ReplyDeleteThe example image that I have presented in the earlier Article as a Question (2nd image from top on this page) needs only two Queries to arrive at that result and I have presented the complete SQL strings of both Queries (Method2_1 and Method2_2) on this page too.
What is explained and presented further on is how we can refine the procedure to automate and prepare the result in Report form without modifying the Queries every time.
Regards,
In my earlier comments, you criticized my method for combining two queries to represent the months being displayed as columns. This was to show how I arrived at your sample image. Your response was that it would be necessary to create new queries for each month and you had a solution that only required two steps.
ReplyDeleteLooking at your two steps above, they are essentially exactly the same as the solution that I provided? The extra steps you describe above are needed in your solution to provide the end result which is to have all the months in the year as columns. In my last comment I showed the solution to use just two queries to achieve the desired result, with the help of pivottable view. Here are the SQL strings in full...
The table that holds the data is called tbl_test and I called this query "qry_test_part1"
TRANSFORM Sum(tbl_test.Amount) AS SumOfAmount
SELECT tbl_test.Location, tbl_test.Year, tbl_test.Month
FROM tbl_test
GROUP BY tbl_test.Location, tbl_test.Year, tbl_test.Month
PIVOT tbl_test.Type;
The second query uses qry_test_part1 as it's source table.
SELECT qry_test_part1.Location, qry_test_part1.Year, qry_test_part1.Month, qry_test_part1.E, qry_test_part1.R, [R]-[E] AS [P+L]
FROM qry_test_part1
GROUP BY qry_test_part1.Location, qry_test_part1.Year, qry_test_part1.Month, qry_test_part1.E, qry_test_part1.R, [R]-[E];
It is this second query that you need to use pivottable view and choose the fields as I explained in my last comments.
You will find this query provides everything you needed, where each new month and location added will automatically show in the query without any further queries needing to be created, along with the year.
Regards
I am sorry if you felt offended by my remarks. I accept your example as a simplified version of the solution with PIVOT Table that needs only fewer steps.
ReplyDeleteI also accept my ignorance in the usage of PIVOT Table except a few trial runs I did few years back and discarded it from my data processing solutions as it has only limited flexibility in presentation and distribution of information like Reports.
Regards,
[...] Null Values when data is msaccesstips.com [...]
ReplyDelete