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 sample data processing methods and attempted to approach the same problem from different angles to arrive at the same result. Reports are the primary output component that is delivered to the User with critical information for analyzing business activities and making informed business decisions. Transforming raw data into a meaningful form as a shareable Report is a real challenge for 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 examine such an issue here so that you understand what I mean by the hurdles involved in creating the final report. Complex data processing steps like these can be automated by sequencing each step in a macro and running that macro from a command button or a VBA subroutine.
Process Flowcharts.
It is essential to create and maintain flowcharts for processes that involve multiple queries and tables, clearly showing the input and output at each step, leading to the final report data. Over time, you may build hundreds of queries in a database for different reports and forget how a particular one was structured. If a user later points out an error in the output, a well-documented flowchart helps you to easily trace each step and identify where the problem occurred.
Last week, I posed a question: how can we display Revenue, Expenses, and Profit/Loss on a month-by-month basis if the sample data includes separate Year and Month fields? The image below shows the sample source table (Transactions2).
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 a 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 is 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 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 Method 2_2 Query and view the output.
Even though we can get the desired results using the above two queries, the second query must be modified each time new monthly data records are added to include the new Profit/Loss column. Since the Profit & Loss Report is based on this query, it also needs to be updated to include the corresponding Revenue, Expenses, and Profit columns for the new period.
This approach is not ideal, especially when the goal is to automate all database processes so that users can produce reports with a single click.
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 months' 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, and 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 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 the source table Transactions2.
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 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 above to simplify 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 the Source File, similar to the sample design image given below.
The sample report currently displays columns for January and February only. However, you can easily extend the same design to include all twelve months. The value from the Year field is used to generate the report headings dynamically, ensuring that the headings automatically update each year when the report is printed—without requiring any manual modifications to the report design.
The Report in Print Preview.We will now automate the Profit and Loss (P&L) Report preparation process so that the report automatically reflects updated data whenever new Revenue and Expense entries are added to the source table.
As part of this automation, we’ll create a Delete Query to clear the existing data from the PandLReportTable before inserting the latest, revised records.
- 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.
Actions Queries in Macro.
If you look at the Queries we have created, you 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 procedure. But first, let us examine the logical arrangement of this Report preparation procedure with a Process Flow Chart.
In Step 1, the PandLReportTable_Init Delete Query clears all previously generated report data from the PandLReportTable.
In Step 3, the Append Query (Method3_2) takes the output from the CrossTab Query in Step 2 and appends it to the PandLReportTable.
We have already defined expressions in the PandLReportQ (SELECT Query) to calculate the Profit/Loss values. The report automatically retrieves all available data from this query, while other columns will remain blank until new records are added to the Transactions2 source table.
To streamline the process, both Action Queries can be combined into a Macro (or a VBA Subroutine) so the user can generate an updated P&L report each month simply by clicking a Command Button — producing results 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