Ms-Access Advanced VBA Programming Techniques, Tips and Tricks.

MS-Access And Data Processing-2

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:

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,
FROM Transactions2
GROUP BY Transactions2.Location,
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,
     [Revenue1]-[Expenses1] AS [Profit/Loss1],
     [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,
    FROM Transactions2
    GROUP BY Transactions2.Location,
    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,
     [R1]-[E1] AS P1,
     [R2]-[E2] AS P2,
     [R3]-[E3] AS P3,
     [R4]-[E4] AS P4,
     [R5]-[E5] AS P5,
     [R6]-[E6] AS P6,
     [R7]-[E7] AS P7,
     [R8]-[E8] AS P8,
     [R9]-[E9] AS P9,
     [R10]-[E10] AS P10,
     [R11]-[E11] AS P11,
     [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.

    The above Report in Print Preview is given below.

    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.

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:

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



  1. What happened to only needing two simple queries? Now I understand what you're trying to achieve I have a much simpler way...

    Use 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.

  2. 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.

    The 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.


  3. 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.

    Looking 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.


  4. 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.

    I 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.


  5. [...] Null Values when data is [...]



Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


Forms How Tos MS-Access Security Functions Reports Animations msaccess animation msaccess forms Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards msaccess graphs msaccess reporttricks ms-access functions msaccessprocess security advanced Access Security DOS Commands Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Array Auto-Number Command Button Join Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess functions msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Copy Custom Functions Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Rich Text Sequence SetFocus Summary Tab-Page Time Difference Union Query User Users Variables Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Important Notice

Dear Readers, Very sorry to inform you that I am finding it difficult to renew my contract with the Hostgator Hosting Plans to continue prov...


Blog Archive

Recent Posts